Thread: BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways

The following bug has been logged on the website:

Bug reference:      15710
Logged by:          Tom Palan
Email address:      thomas@palan.at
PostgreSQL version: 9.6.10
Operating system:   Debian stretch 64bit
Description:

If I want to add a column to a table which and the column may or may not
already exist, the specified column constraint like UNIQUE is added every
time the command is issued, regardless if the column gets added or not.

Example:
CREATE TABLE test (id SERIAL PRIMARY KEY);
ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;

Result:
\d test
                           Table "public.test"
   Column   |  Type   |                     Modifiers                     
------------+---------+---------------------------------------------------
 id         | integer | not null default nextval('test_id_seq'::regclass)
 new_column | integer | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_new_column_key" UNIQUE CONSTRAINT, btree (new_column)

After issuing the command:
ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;

again, the table structure is like:
\d test
                           Table "public.test"
   Column   |  Type   |                     Modifiers                     
------------+---------+---------------------------------------------------
 id         | integer | not null default nextval('test_id_seq'::regclass)
 new_column | integer | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_new_column_key" UNIQUE CONSTRAINT, btree (new_column)
    "test_new_column_key1" UNIQUE CONSTRAINT, btree (new_column)

Result: a new UNIQUE constraint was added
Expected result: as the column new_column already exists, creating the
column and the specified constraint should not be done

PG Bug reporting form <noreply@postgresql.org> writes:
> If I want to add a column to a table which and the column may or may not
> already exist, the specified column constraint like UNIQUE is added every
> time the command is issued, regardless if the column gets added or not.

Yeah, this is the same thing previously reported at

https://www.postgresql.org/message-id/flat/152509815280.19803.16118194452213577808%40wrigleys.postgresql.org

We haven't got further than sketching a design for a fix :-(

            regards, tom lane

Re: PG Bug reporting form 2019-03-21 <15710-f52bad04543be4cb@postgresql.org>
> CREATE TABLE test (id SERIAL PRIMARY KEY);
> ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;

As a workaround, you can name the constraint:

ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer CONSTRAINT id_unique UNIQUE;

Then it won't get added multiple times. (It will still be executed
once, though.)

Christoph