PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15710
PG Version9.6.10
OSDebian stretch 64bit
Opened2019-03-21 08:41:53+00
Reported byTom Palan
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15710
Logged by:          Tom Palan
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-03-21 08:41:53+00PG Bug reporting formBUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
2019-03-21 14:18:10+00Tom LaneRe: BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
2019-03-22 08:36:14+00Christoph BergRe: BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways