Postgres: Add constraint if it doesn't already exist

sql postgresql constraints
Paul A Jungwirth picture Paul A Jungwirth · Jul 23, 2011 · Viewed 61.3k times · Source

Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ... which will just ignore the command if the constraint already exists, so that it doesn't raise an error?

Answer

Webmut picture Webmut · Aug 20, 2014

A possible solution is to simply use DROP IF EXISTS before creating the new constraint.

ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;

Seems easier than trying to query information_schema or catalogs, but might be slow on huge tables since it always recreates the constraint.

Edit 2015-07-13: Kev pointed out in his answer that my solution creates a short window when the constraint doesn't exist and is not being enforced. While this is true, you can avoid such a window quite easily by wrapping both statements in a transaction.

Related questions

How to drop all NOT NULL constraints from a PostgreSQL table in one go

Is it possible to drop all NOT NULL constraints from a table in one go? I have a big table with a lot of NOT NULL constraints and I'm searching for a solution that is faster than dropping them separately.

Read More
Adding named foreign key constraints in a SQL Create statement

I currently have: CREATE TABLE galleries_gallery ( id INT NOT NULL PRIMARY KEY IDENTITY, title NVARCHAR(50) UNIQUE NOT NULL, description VARCHAR(256), templateID INT NOT NULL REFERENCES galleries_templates(id), jsAltImgID INT NOT NULL REFERENCES libraryImage(id) jsAltText NVARCHAR(500), dateCreated SMALLDATETIME …

Read More
How to get the name of a unique constraint in postgresql?

I need to drop a unique constraint from a postgresql table, but I didn't give it a name in the schema. Does anybody know, how to get the name of such a constraint, or how to drop it?

Read More