Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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;
like image 981
Andrus Avatar asked Dec 06 '12 21:12

Andrus


People also ask

How do you create a sequence?

To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege. Specify the schema to contain the sequence. If you omit schema , then Oracle Database creates the sequence in your own schema. Specify the name of the sequence to be created.

How do you create a sequence in SQL?

The syntax to create a sequence in SQL Server (Transact-SQL) is: CREATE SEQUENCE [schema.] sequence_name [ AS datatype ] [ START WITH value ] [ INCREMENT BY value ] [ MINVALUE value | NO MINVALUE ] [ MAXVALUE value | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE value | NO CACHE ];

How do you create a sequence for an existing table?

3 Answers. Show activity on this post. Set the default value when you add the new column: create sequence rid_seq; alter table test add column rid integer default nextval('rid_seq');


3 Answers

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:

  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:

  • How to check if a table exists in a given schema
like image 150
Erwin Brandstetter Avatar answered Sep 29 '22 16:09

Erwin Brandstetter


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.

like image 24
Joe Shaw Avatar answered Sep 29 '22 16:09

Joe Shaw


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;
like image 45
Evan Siroky Avatar answered Sep 29 '22 18:09

Evan Siroky