☰

Postgres: Add constraint if it doesn't already exist Ask Question

tag-icon • tag-icon sql postgresql constraints
Postgres: Add constraint if it doesn't already exist Ask Question

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?

ベストアンサー1

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.

おすすめ記事