TR: redundant constraint_schema

Hi,

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

Thanks,

Olivier

De : Olivier Leprêtre [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 is called (with xxx being a new number each time)

Thanks,

Olivier