CODEWITHSUNDEEP
sqlpostgresqldatabase-designplpgsqlpostgresql-9.2
Andrus
Andrus

Reputation: 27955

How to create sequence if not exists

I tried to use code from Check if sequence exists in 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;

Upvotes: 43

Views: 83992

Answers (6)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657587

Postgres 9.5 or later

IF NOT EXISTS was added to CREATE SEQUENCE in Postgres 9.5. That's the simple solution now:

CREATE SEQUENCE IF NOT EXISTS myschema.myseq;

But consider details of the outdated answer anyway ...
And you know about serial or IDENTITY columns, right?

Postgres 9.4 or older

Sequences share the namespace with several other table-like objects. The manual:

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. So there are 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?

Specify what to do in either case. A DO statement could look like this:

DO
$do$
DECLARE
   _kind "char";
BEGIN
   SELECT relkind
   FROM   pg_class
   WHERE  oid = 'myschema.myseq'::regclass  -- sequence name, optionally schema-qualified
   INTO  _kind;

   IF NOT FOUND THEN       -- name is free
      CREATE SEQUENCE myschema.myseq;
   ELSIF _kind = 'S' THEN  -- sequence exists
      -- do nothing?
   ELSE                    -- object name exists for different kind
      -- do something!
   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

Related:

Upvotes: 71

Osify
Osify

Reputation: 2295

Postgres doesn't have CREATE SEQUENCE IF NOT EXISTS and if the table has default value using the sequence if you just drop the sequence, you might get error:

ERROR: cannot drop sequence (sequence_name) because other objects depend on it SQL state: 2BP01

For me, this one can help:

ALTER TABLE <tablename> ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE IF EXISTS <sequence_name>;
CREATE sequence <sequence_name>;

Upvotes: 3

Evan Siroky
Evan Siroky

Reputation: 9418

If you don't need to preserve the potentially existing sequence, you could just drop it and then recreate it:

DROP SEQUENCE IF EXISTS id_seq;
CREATE SEQUENCE id_seq;

Upvotes: 10

Joe Shaw
Joe Shaw

Reputation: 22592

I went a different route: just catch the exception:

DO
$$
BEGIN
        CREATE SEQUENCE myseq;
EXCEPTION WHEN duplicate_table THEN
        -- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

One nice benefit to this is that you don't need to worry about what your current schema is.

Upvotes: 14

Loek Bergman
Loek Bergman

Reputation: 2195

I have a function to clean all tables in my database application at any time. It is build dynamically, but the essence is that it deletes all data from each table and resets the sequence. This is the code to reset the sequence of one of the tables:

perform relname from pg_statio_all_sequences where relname = 'privileges_id_seq';
if found then
  select setval ('privileges_id_seq',1, false) into i_result;
end if;

Hope this helps,

Loek

I am using postgres 8.4, I see that you use 9.2. Could make a difference where the information is stored.

Upvotes: 0

sierrasdetandil
sierrasdetandil

Reputation: 461

The information about sequences can be retrieved from information_schema.sequences (reference)

Try something like this (untested):

...
IF not EXISTS (SELECT * FROM information_schema.sequences
    WHERE sequence_schema = 'firma1' AND sequence_name = 'myseq') THEN
...

Upvotes: 0

Related Questions