Create a type if not exist Postgresql

Question

I'm trying to run this sql which create a new type if is is not existed:

IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'ai') CREATE TYPE ai AS (a text, i int);

I got an error at or near "if", I have trying to find out the answer but its no help. I tried this also but it still not work:

CASE (SELECT 1 FROM pg_type WHERE typname = 'ai') WHEN IS NULL THEN CREATE TYPE ai AS (a text, i int);

error near or at "CASE"

Answer 1

Your IF syntax is wrong, it misses END IF. Depending on the context, i.e. when you're not already in an PL/pgSQL block (like a function etc.), you need to also use an anonymous DO block. And don't forget to check for the schema too, otherwise you might get false positives if the type already exists in another schema.

DO
$$
BEGIN
  IF NOT EXISTS (SELECT *
                        FROM pg_type typ
                             INNER JOIN pg_namespace nsp
                                        ON nsp.oid = typ.typnamespace
                        WHERE nsp.nspname = current_schema()
                              AND typ.typname = 'ai') THEN
    CREATE TYPE ai
                AS (a text,
                    i integer);
  END IF;
END;
$$
LANGUAGE plpgsql;

(Or, if you're already in an PL/pgSQL block, just everything between IF and END IF; including IF and END IF;.)