How to Create Sequence If Not Exists

How to create sequence if not exists

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

Is there something like if not exist create sequence ... in Oracle SQL?

If you're sure the script will always run under SQL*Plus, you can bracket the CREATE SEQUENCE statements with a directive to continue on error:

WHENEVER SQLERROR CONTINUE
-- create sequences here, ignoring errors
WHENEVER SQLERROR EXIT SQL.SQLCODE

Be aware if there are other errors (permission problems, syntax failures, etc.) in the create sequence statements they will be ignored

Postgres 'if not exists' fails because the sequence exists

Sequences are part of the database schema. If you find yourself modifying the schema dynamically based on the data stored in the database, you are probably doing something wrong. This is especially true for sequences, which have special properties e.g. regarding their behavior with respect to transactions. Specifically, if you increment a sequence (with the help of nextval) in the middle of a transaction and then you rollback that transaction, the value of the sequence will not be rolled back. So most likely, this kind of behavior is something that you don't want with your data. In your example, imagine that a user tries to add word. This results in the corresponding sequence being incremented. Now imagine that the transaction does not complete for reason (e.g. maybe the computer crashes) and it gets rolled back. You would end up with the word not being added to the database but with the sequence being incremented.

For the particular example that you mentioned, there is an easy solution; create an ordinary table to store all the "sequences". Something like that would do it:

CREATE TABLE word_frequency (
    word text NOT NULL UNIQUE,
    frequency integer NOT NULL
);

Now I understand that this is just an example, but if this approach doesn't work for your actual use case, let us know and we can adjust it to your needs.

Edit: Here's how you the above solution works. If a new word is added, run the following query ("UPSERT" syntax in postgres 9.5+ only):

INSERT INTO word_frequency(word,frequency)
VALUES ('foo',1)
ON CONFLICT (word)
DO UPDATE
SET frequency = word_frequency.frequency + excluded.frequency
RETURNING frequency;

This query will insert a new word in word_frequency with frequency 1, or if the word exists already it will increment the existing frequency by 1. Now what happens if two transaction try to do that at the same time? Consider the following scenario:

client 1          client 2
--------          --------
BEGIN
                  BEGIN
UPSERT ('foo',1)
                  UPSERT ('foo',1) <====
COMMIT
                  COMMIT

What will happen is that as soon as client 2 tries increment the frequency for foo (marked with the arrow above), that operation will block because the row was modified by a different transaction. When client 1 commits, client 2 will get unblocked and continue without any errors. This is exactly how we wanted it to work. Also note, that postgresql will use row-level locking to implement this behavior, so other insertions will not be blocked.

Create sequence if not exist in DuckDB

I don't think you are using the if not exists correctly.

It should be:

CREATE SEQUENCE IF NOT EXISTS seq_personid START 1;

Create a Table if it doesn't exists and create a sequence if it doesn't exists in Oracle

You could do an anonymous PL/SQL block:

DECLARE
  t_count INTEGER;
  v_sql VARCHAR2(1000) := 'create table TEST (
            id number NOT NULL PRIMARY KEY,  
            url varchar(1000) NOT NULL,  
            urlHash varchar(255) NOT NULL,  
            contentHash varchar(255),  
            modDate varchar(30),  
            contentLocation varchar(100),  
            status integer,  
            lastCrawlDate varchar(30))';
BEGIN
  SELECT COUNT(*)
    INTO t_count
    FROM user_tables
   WHERE table_name = 'TEST';

  IF t_count = 0 THEN
    EXECUTE IMMEDIATE v_sql;
  END IF;
END;
/

Similarly, you can use USER_SEQUENCES to look for an existing sequence and adapt this to suit.

PGAdmin CREATE script sequence relation does not exist

First off PGAdmin does not create anything. It is an graphical interface to the Postgres as psql is a commend line interface to Postgres. It id Postgres doing the work. You need to always keep in mind what software you are talking.

Secondly, your syntax is incorrect. There are 2 approaches you can take: Let Postgres create the sequence (this seems to be what you want). The other way is to manually create the sequence and then manually specify the sequence as the default (the second part is what you actually did). Postgres generates and names and makes the default when you specify serial/bigserial instead of an actual data type. So (for versins prior to 10)

CREATE TABLE IF NOT EXISTS public.mytable
(
    mykey serial primary key, ...
); 

For versions 10 and after use a Generated Identity

CREATE TABLE IF NOT EXISTS public.mytable
    (
        mykey bigint generated always as identity primary key, ... 
    ); 

With manual method you need to you need to first create the sequence

create sequence mytable_mykey_seq;  --parameters and needed

Postgresql ADD SERIAL COLUMN IF NOT EXISTS still creating sequences

Looks like you found a minor bug. I can recreate it in Postgres 9.6 and 10:

db<>fiddle here

In fact, it has been reported in Sept 2017. An attempt to fix has been rejected so far, since it would have introduced other oddities.

To circumvent, consider the advice in Laurenz' answer. Maybe automate with a DO command like:

DO
$do$
BEGIN
IF NOT EXISTS (SELECT FROM pg_attribute
               WHERE  attrelid = 'public.tblname'::regclass  -- table name here 
               AND    attname = 'colname'                    -- column name here
               AND    NOT attisdropped
              ) THEN
   ALTER TABLE public.tblname ADD COLUMN IF NOT EXISTS colname serial;
ELSE
   RAISE NOTICE 'column "colname" of relation "public.tblname" already exists, skipping';
END IF;
END
$do$;

A race condition between check and creation is possible if more than one transaction can change the same table structure concurrently.

The generated notice is exactly what you'd get with IF NOT EXISTS, too.

Wrap that into a plpgsql function with dynamic SQL taking parameters for repeated use with arbitrary table and column names.

Related:

• Create PostgreSQL ROLE (user) if it doesn't exist

Schema-qualify the table name to avoid ambiguity. Strings are case-insensitive here. You can't use the CaMeL-case spelling you have in your question. See:

• Are PostgreSQL column names case-sensitive?

Create Sequence if it not exists using oracle sql developer

The user who calls this procedure needs to have CREATE_ANY_SEQUENCE granted to them by a user who has this privilege and the ability to grant it.

Effectively for a learning scenario:

• log on as a user with DBA
• execute GRANT CREATE_ANY_SEQUENCE TO YourSchemaOwnername;

How can I determine if a Sequence exist in SQL Server 2012?

The script to determine whether or not a Sequence exists in SQL Server 2012 is very similar to checking for Stored Procedures. Consider the following code that checks to see if a Stored Procedure exists:

SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SProc_Name]') AND type IN (N'P', N'PC')

The values of 'P' and 'PC' for the type specify the type of the sys.object is a SQL Stored Procedure or a Assembly (CLR) stored-procedure. To check for a sequence, you just need to change it to 'SO' which indicates it is a Sequence Object:

SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Sequence_Name]') AND type = 'SO'

For example, if you want to create a Sequence if it doesn't exist, you could use the following code:

IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sequence_Name]') AND type = 'SO')
CREATE SEQUENCE [dbo].[Sequence_Name] 
    AS [bigint]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE  3 
GO

I hope this helps!

---

Related Topics