Re: BUG #15180: Alter table add column if not exists with unique constraint will add extra duplicate

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >> 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.
>
> > My understanding, and, it seems, that of the original poster and likely
> > anyone who works with PG, is that an IF NOT EXISTS means "only perform
> > this operation if the thing doesn't already exist.
>
> Not clear. If the column exists but there's no unique index on it,
> should this command cause the index to spring into existence?

No, it shouldn't, and I view that as quite clear.

The operation is "ADD COLUMN IF NOT EXISTS ... column definition."

If the column exists then the operation should be a noop.

ALTER TABLE has a very explicit way to segregate independent operations
using the ','. In this case, there's only one operation being requested
and it's the ADD COLUMN, and that's what the IF NOT EXISTS applies to.

> C.I.N.E. is by its nature too fuzzy to allow any principled answer to
> that. The facts on the ground are that right now, this does result in
> creation of an index. If we take that away, I guarantee you somebody
> else will file a bug report complaining that it used to work and we
> broke their code.

I disagree entirely. If someone was depending on that misbehavior then
that's their bug and issue to deal with.

Thanks!

Stephen