Michael Paquier <michael@paquier.xyz> writes:
> 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;
> ... 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.
After thinking about it for awhile, I'm not exactly convinced that this
is a bug at all. "UNIQUE" underspecifies the desired index, so that it's
impossible to say that a given existing index does or does not match the
command. The code errs in favor of deciding that it doesn't, but the
opposite decision could also be "wrong" in some use-cases.
I'll spare you my usual rant about how CREATE IF NOT EXISTS sucks because
the subsequent state of the object isn't well-defined ... oops, too late.
But this seems like just another case of that problem.
regards, tom lane