How to create sequence if not exists

Question

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;

If sequence does not exist .. if sequence exists. Please clarify what you want to do in each case. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 6, 2012 at 22:37
Postgres 9.5 will have create sequence if not exists postgresql.org/docs/9.5/static/sql-createsequence.html — user330315
– user330315, Commented Sep 15, 2015 at 20:21

Erwin Brandstetter · Accepted Answer · 2020-06-06 23:12:26Z

73

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?

Auto increment table column

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:

Name does not exist. -> Create sequence.
Sequence with the same name exists. -> Do nothing? Any output? Any logging?
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

How to check if a table exists in a given schema

edited Jun 6, 2020 at 23:12

answered Dec 6, 2012 at 22:42

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

wildplasser Over a year ago

Excellent answer. IIRC sequences used to have problems with not being fully schema-qualified in older versions (before 9.1 ?), causing them to "leak" into the "global namespace". I' not sure though, could have been a glitch on my side ;-[

Erwin Brandstetter Over a year ago

@wildplasser: Doesn't ring a bell here, maybe it slipped my attention.

wildplasser Over a year ago

Could have been a glitch. Too broad search_path settings (plus a lack of explicit schema qualifying) could have been the cause, for instance...

Joe Shaw · Accepted Answer · 2014-06-05 02:21:57Z

14

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.

answered Jun 5, 2014 at 2:21

Joe Shaw

22.7k16 gold badges74 silver badges92 bronze badges

1 Comment

Mathieu J. Over a year ago

only annoying piece, is the extra dependency. I am getting this error.

PG::InternalError: ERROR:  could not load library "/usr/lib/postgresql/plpgsql.so": /usr/lib/postgresql/plpgsql.so: undefined symbol: MakeExpandedObjectReadOnlyInternal

Evan Siroky · Accepted Answer · 2015-09-15 19:59:40Z

10

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;

answered Sep 15, 2015 at 19:59

Evan Siroky

9,4366 gold badges58 silver badges74 bronze badges

1 Comment

Mathieu J. Over a year ago

except that if it already exist, it is most likely in use.

PG::DependentObjectsStillExist: ERROR:  cannot drop sequence activities_id_seq because other objects depend on it DETAIL:  default for table activities column id depends on sequence activities_id_seq HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Osify · Accepted Answer · 2016-04-19 03:44:48Z

3

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

answered Apr 19, 2016 at 3:44

Osify

2,30526 silver badges42 bronze badges

Comments

sierrasdetandil · Accepted Answer · 2012-12-06 23:55:28Z

0

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

edited Dec 6, 2012 at 23:55

answered Dec 6, 2012 at 21:36

sierrasdetandil

4611 gold badge9 silver badges17 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

No information is "stored" in objects of the information_schema. That's just a collection of (rather convoluted) views on the system catalog to supply the information according to the SQL standard. If you need guaranteed portability across different RDBMS or across versions, use information_schema. Using the objects in the system catalog - schema pg_catalog, where actual information is stored - is generally faster by an order of magnitude.

sierrasdetandil Over a year ago

@ErwinBrandstetter You're right, it was a bad choice of word. I've edited my answer.

Loek Bergman · Accepted Answer · 2012-12-07 14:53:45Z

0

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.

answered Dec 7, 2012 at 14:53

Loek Bergman

2,19520 silver badges18 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

The question seems to be about creating a sequence, not resetting it. Also, please consider this [faq] posting on meta about signatures and taglines.

Loek Bergman Over a year ago

I read the question that the main concern is how to avoid the arousal of noise. Andrus knows about creating the sequence as he points to a question about that. Your answer is already a perfect solution for the question as a whole. I just wanted to be friendly and provide him an alternative method. The extra information is there to show that it works. I will read the posting about meta etc. shortly.

Collectives™ on Stack Overflow

How to create sequence if not exists

6 Answers 6

Postgres 9.5 or later

Postgres 9.4 or older

3 Comments

1 Comment

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Postgres 9.5 or later

Postgres 9.4 or older

3 Comments

1 Comment

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related