Postgresql ADD SERIAL COLUMN IF NOT EXISTS still creating sequences

Question

I call the command

ALTER TABLE tableName ADD COLUMN IF NOT EXISTS columnName SERIAL

in postgresql. The first time this is run, a new column is created with a corresponding sequence as desired. When run again, however, another sequence is created in the database, even though the column already exists. Does anyone know of any fixes to stop this new sequence creation?

Check if the column exists before doing the `alter table`. Presumably, the sequence is created before the table is checked for the existence of the column. — Gordon Linoff, May 27 '18 at 15:14
Gordon is right. See ["8.1.4. Serial Types"](https://www.postgresql.org/docs/9.6/static/datatype-numeric.html#DATATYPE-SERIAL). — sticky bit, May 27 '18 at 15:29

Erwin Brandstetter · Accepted Answer · 2021-11-21T00:08:33.923

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.

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?

score 1 · Answer 2 · edited May 27 '18 at 23:04

You can use the following steps, which have the same effect:

ALTER TABLE tablename ADD COLUMN IF NOT EXISTS columnname integer;

Now if the column is not created and you get a notice, you are done. Otherwise proceed:

CREATE SEQUENCE IF NOT EXISTS tablename_columnname_seq
   OWNED BY tablename.columnname;
ALTER TABLE tablename ALTER COLUMN columnname
   SET DEFAULT nextval('tablename_columnname_seq');

Alternatively, you can check if the column exists before you create it:

SELECT count(*) FROM information_schema.columns
   WHERE table_name = 'tablename' AND column_name = 'columnname';

You might want to add the schema name to the conditions.

Postgresql ADD SERIAL COLUMN IF NOT EXISTS still creating sequences

2 Answers2