Postgres: Add constraint if it doesn't already exist

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.

This might help, although it may be a bit of a dirty hack:

create or replace function create_constraint_if_not_exists (
    t_name text, c_name text, constraint_sql text
) 
returns void AS
$$
begin
    -- Look for our constraint
    if not exists (select constraint_name 
                   from information_schema.constraint_column_usage 
                   where table_name = t_name  and constraint_name = c_name) then
        execute constraint_sql;
    end if;
end;
$$ language 'plpgsql'

Then call with:

SELECT create_constraint_if_not_exists(
        'foo',
        'bar',
        'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')

Updated:

As per Webmut's answer below suggesting:

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

That's probably fine in your development database, or where you know you can shut out the apps that depend on this database for a maintenance window.

But if this is a lively mission critical 24x7 production environment you don't really want to be dropping constraints willy nilly like this. Even for a few milliseconds there's a short window where you're no longer enforcing your constraint which may allow errant values to slip through. That may have unintended consequences leading to considerable business costs at some point down the road.

Postgres: Add constraint if it doesn't already exist

Related

Recent Posts