Andrus Andrus - 7 months ago 323
SQL Question

How to create sequence if not exists

I tried to use code from check if sequence exists postgres (plpgsql).

To create sequence if it does not exists. Running this code two times causes an exception:


sequence ... already exists.


How to create sequence only if it does not exist?

If the sequence does not exist, no message should be written and no error should occur so I cannot use the stored procedure in the other answer to this question since it writes message to log file every time if sequence exists.

do $$
begin

SET search_path = '';
IF not EXISTS (SELECT * FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'firma1.' || quote_ident('myseq'))
THEN

SET search_path = firma1,public;

create sequence myseq;

END IF;

SET search_path = firma1,public;

end$$;

select nextval('myseq')::int as nr;

Answer

The name of a sequence conflicts with names of objects of several types - not just sequences. I quote the manual here:

The sequence name must be distinct from the name of any other sequence, table, index, view, or foreign table in the same schema.

Bold emphasis mine.
Therefore you have three cases:

  1. Name does not exist. -> Create sequence.
  2. Sequence with the same name exists. -> Do nothing? Any output? Any logging?
  3. Other conflicting object with the same name exists. -> Do something? Any output? Any logging?

You need to specify what you want to do in either of these cases. The DO statement could look like this:

DO
$do$
DECLARE
   _kind "char";
BEGIN
   SELECT INTO _kind  c.relkind
   FROM   pg_class     c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  c.relname = 'myseq'      -- sequence name here
   AND    n.nspname = 'myschema';  -- schema name here

   IF NOT FOUND THEN       -- name is free
      CREATE SEQUENCE myschema.myseq;
   ELSIF _kind = 'S' THEN  -- sequence exists
      -- do nothing?
   ELSE                    -- conflicting object of different type exists
      -- do somethng!
   END IF;
END
$do$;

Object types (relkind) in pg_class according to the manual:

r = ordinary table
i = index
S = sequence
v = view
m = materialized view
c = composite type
t = TOAST table
f = foreign table