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"

There's no IF in SQL (at least in postgres).

404
– 404

2019-06-18 10:57:41 +00:00
Commented Jun 18, 2019 at 10:57 — 404
– 404, Commented Jun 18, 2019 at 10:57
i updated question with case when

Nguyen Hoang Vu
– Nguyen Hoang Vu

2019-06-18 11:01:24 +00:00
Commented Jun 18, 2019 at 11:01 — Nguyen Hoang Vu
– Nguyen Hoang Vu, Commented Jun 18, 2019 at 11:01
This is a duplicate question.

cstork
– cstork

2022-08-14 09:10:44 +00:00
Commented Aug 14, 2022 at 9:10 — cstork
– cstork, Commented Aug 14, 2022 at 9:10

sticky bit · Accepted Answer · 2019-06-18 11:18:32Z

14

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;.)

answered Jun 18, 2019 at 11:18

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nguyen Hoang Vu Over a year ago

I include IF and END IF but i still got a syntax error near if

sticky bit Over a year ago

Then you may have something wrong before the IF. What I posted works for me: dbfiddle.uk/…

Collectives™ on Stack Overflow

Create a type if not exist Postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related