=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I have a patching script that is supposed to add column if not existing :
> ALTER TABLE myschem.table1
> ADD COLUMN IF NOT EXISTS col1 VARCHAR(254) REFERENCES
> myschem.table2(col2)
> When col1 already exists, I expected that nothing would happen.
> [ but actually, it creates an FK constraint anyway ]
As I've said many times before, I hate CREATE IF NOT EXISTS with a
passion, because its semantics are so squishy. This is a perfect example
of that: it's impossible to make a principled decision whether this is a
bug or not, or what the correct behavior is if you think it's a bug.
Should the command do nothing at all if col1 exists, regardless of
whether there's an FK constraint or not? Should it avoid creating
a duplicate constraint, and if so how picky are we to be about what
"duplicate" means? What happens if myschem.table2(col2) doesn't exist?
Not to mention whether we should change the behavior for other secondary
objects that might be shown in the command, such as UNIQUE or CHECK
constraints. Right now all of those get added, possibly redundantly,
just like FK constraints.
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.
Even if we were now to conclude that this is a bug and agree on what'd be
better semantics, there would be a pretty strong backwards-compatibility
argument against changing it; some people's scripts might expect the
constraint(s) to get added.
The short answer is that IF NOT EXISTS gives you no guarantees whatsoever
about the subsequent properties of the object, only that something by
that name will exist. If you don't like that, don't use IF NOT EXISTS.
regards, tom lane