TR: redundant constraint_schema - Mailing list pgsql-general

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

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