Menu
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?
540
Postgresql add primary key if not exists We will alter the table items by adding a new column name which is customer_name. Let's check the syntax. ALTER TABLE table_name ADD COLUMN column_name VARCHAR NOT NULL; Let's check the query again by adding the column customer_name.
Here is the generic syntax to add a constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition; For example, to add a UNIQUE constraint to the table customers on the column customer_id: ALTER TABLE customers ADD CONSTRAINT uniquectm_const UNIQUE (customer_id);
In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement. You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.
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.
127
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
