BUG #15180: Alter table add column if not exists with uniqueconstraint will add extra duplicate - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15180
Logged by:          Olav Gjerde
Email address:      olav@backupbay.com
PostgreSQL version: 10.1
Operating system:   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0
Description:

Alter table add column if not exists with unique constraint will add extra
duplicate of the unique constraint when the column exists.

Example:
ALTER TABLE api_values ADD COLUMN IF NOT EXISTS master_key bigint NULL
UNIQUE;

If you run this several times you will get more unique constraints:
"api_values_master_key_key" UNIQUE CONSTRAINT, btree (master_key)
"api_values_master_key_key1" UNIQUE CONSTRAINT, btree (master_key)
"api_values_master_key_key2" UNIQUE CONSTRAINT, btree (master_key)

Workaround is just dropping the constraint if exits before the alter table
add column statement. But I am afraid a lot of developers will enter this
trap as it is kinda unexpected behavior. I read there is a similar problem
with serial/sequences.