TR: redundant constraint_schema

Hi,

Please find a question that didn't get an answer in the pgsql-sql &
pgsql-hackers lists. I hope I'll get an answer here.

Thanks,

Olivier

De : Olivier Leprêtre [ <mailto:o(dot)lepretre(at)gmail(dot)com>
mailto:o(dot)lepretre(at)gmail(dot)com]
Envoyé : mercredi 29 août 2018 15:49
À : 'pgsql-sql(at)lists(dot)postgresql(dot)org'
Objet : redundant constraint_schema

Hi,

Can someone explain why, when a column is not created (add column if not
exists), a redundant constraint is still created from the REFERENCES part ?

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, when
applying the previous query and then querying :

select constraint_name from information_schema.key_column_usage where
constraint_schema='myschem'

I notice that a new constraint "table1_col2_fkeyxxx" is created each time
the previous ALTER TABLE ADD COLUMN is called (with xxx being a new number
each time)

It seems strange to have second part of statement executed (references) when
first part (add column) was not. Would it be possible that this sort of
query executes "references" first ?

Thanks,

Olivier