redundant constraint_schema

From: Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>
To: <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: redundant constraint_schema
Date: 2018-08-29 13:48:36
Message-ID: 5b86a437.1c69fb81.c64a2.1852@mx.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

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 exists, I expected that nothing would happen. But, when applying
this query

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)

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

Thanks,

Olivier

Browse pgsql-sql by date

  From Date Subject
Next Message Dave Bolt 2018-08-29 21:46:02 Stored Procedure to Delete Rows and Return Count
Previous Message Olivier Leprêtre 2018-08-28 15:37:04 pgadmin not displaying data from postgresql_fdw