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
