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