From: | Michael Paquier <michael(at)paquier(dot)xyz> |
---|---|
To: | olav(at)backupbay(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #15180: Alter table add column if not exists with unique constraint will add extra duplicate |
Date: | 2018-05-01 12:44:36 |
Message-ID: | 20180501124436.GA13450@paquier.xyz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-05-01 14:08:38 | Re: BUG #15180: Alter table add column if not exists with unique constraint will add extra duplicate |
Previous Message | Dang Minh Huong | 2018-05-01 07:41:04 | Usage of pg_waldump |