On Mon, Apr 30, 2018 at 02:22:32PM +0000, PG Bug reporting form wrote:
> 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.
I am sure you mean that:
https://www.postgresql.org/message-id/12400.1506455842@sss.pgh.pa.us
Similarly, the index is created before deciding to make use of it in the
new column... I don't recall all the details in tablecmds.c, but I am
pretty sure that it would be quite messy to create the column before
creating the index itself.
--
Michael