Re: BUG #15361: Add column if not exists create duplicate constraint

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> I wrote:
> > I believe this exact issue was debated when ADD COLUMN IF NOT EXISTS
> > was added, and the camp that wanted it thought this behavior was fine.
>
> After a bit of digging in the archives, I failed to find any evidence
> that the point was considered when the ADD COLUMN IF NOT EXISTS patch
> went in, though there have certainly been related threads in the dim
> past, which basically led to the conclusion that you don't get to have
> much certainty about what state IF NOT EXISTS leaves behind, and those
> who think it's a good feature think that's OK. (I am not among them.)
>
> The thing I was half-remembering was probably this bug report about
> the unique-constraint aspect of the same problem:
>
> https://www.postgresql.org/message-id/flat/152509815280.19803.16118194452213577808%40wrigleys.postgresql.org
>
> wherein it was argued that if the IF NOT EXISTS fires, it should prevent
> all side-effects of the ADD COLUMN clause it's attached to, whether or not
> any of the subsidiary objects exist (in some form) already. If you buy
> that theory then there's a sketch for a fix there. Nobody's done anything
> about it AFAIK.

For my 2c, at least, I continue to be of the opinion (as it seems the OP
is also..) that IF NOT EXISTS means "ONLY DO THIS IF THE OBJECT
REFERENCED DOESN'T EXIST". There certainly seemed to be a lack of
dissenting opinion by the end of that thread, so maybe it's time to
actually make that change. I still consider the current behavior to be
a bug, but I could see an argument for not back-patching it out of
concern about breaking scripts in a back-patch and because the code
looks like it'd need to be whacked around some.

Thanks!

Stephen