Postgres: Add constraint if it doesn't already exist

Does Postgres have a开发者_如何转开发ny 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?

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.

You can use an exception handler inside an anonymous DO block to catch the duplicate object error.

DO $$
BEGIN

  BEGIN
    ALTER TABLE foo ADD CONSTRAINT bar ... ;
  EXCEPTION
    WHEN duplicate_table THEN  -- postgres raises duplicate_table at surprising times. Ex.: for UNIQUE constraints.
    WHEN duplicate_object THEN
      RAISE NOTICE 'Table constraint foo.bar already exists';
  END;

END $$;

http://www.postgresql.org/docs/9.4/static/sql-do.html http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html

you can run query over pg_constraint table to find constraint exists or not.like:

SELECT 1 FROM pg_constraint WHERE conname = 'constraint_name'"

Creating constraints can be an expensive operation on a table containing lots of data so I recommend not dropping constraints only to immediately create them again immediately after - you only want to create that thing once.

I chose to solve this using an anonymous code block, very similar to Mike Stankavich, however unlike Mike (who catches an error) I first check to see if the constraint exists:

DO $$
BEGIN
    IF NOT EXISTS ( SELECT  constraint_schema
                ,       constraint_name 
                FROM    information_schema.check_constraints 
                WHERE   constraint_schema = 'myschema'
                  AND   constraint_name = 'myconstraintname'
              )
    THEN
        ALTER TABLE myschema.mytable ADD CONSTRAINT myconstraintname CHECK (column <= 100);
    END IF;
END$$; 

Using information_schema.constraint_column_usage to check for the constraint doesn't work for foreign keys. I use pg_constraint to check for primary keys, foreign keys or unique constraints:

CREATE OR REPLACE FUNCTION add_constraint(t_name text, c_name text, constraint_sql text)
RETURNS void
AS $$
BEGIN
    IF NOT EXISTS(
            SELECT c.conname
            FROM pg_constraint AS c
            INNER JOIN pg_class AS t ON c.conrelid = t."oid"
            WHERE t.relname = t_name AND c.conname = c_name
    ) THEN

        EXECUTE 'ALTER TABLE ' || t_name || ' ADD CONSTRAINT ' || c_name || ' ' || constraint_sql;

    END IF;
END;
$$
LANGUAGE plpgsql;

Examples:

SELECT add_constraint('client_grant_system_scopes', 'client_grant_system_scopes_pk', 'PRIMARY KEY (client_grants_id, tenant, "scope");');

SELECT add_constraint('client_grant_system_scopes', 'client_grant_system_scopes_fk', 'FOREIGN KEY (tenant,"scope") REFERENCES system_scope(tenant,"scope") ON DELETE CASCADE;');

SELECT add_constraint('jwt_assertion_issuers', 'jwt_assertion_issuers_issuer_key', 'UNIQUE (issuer);');

Take advantage of regclass to reduce verbosity, increase performance, and avoid errors related to table naming clashes between schemas:

DO $$ BEGIN
    IF NOT EXISTS (SELECT FROM pg_constraint 
                   WHERE conrelid = 'foo'::regclass AND conname = 'bar') THEN 
        ALTER TABLE foo ADD CONSTRAINT bar...;
    END IF;
END $$;

This will also work for tables in other schemas, e.g.:

DO $$ BEGIN
    IF NOT EXISTS (SELECT FROM pg_constraint 
                   WHERE conrelid = 's.foo'::regclass AND conname = 'bar') THEN 
        ALTER TABLE s.foo ADD CONSTRAINT bar...;
    END IF;
END $$;

In psql You can use metacommand \gexec for run generated query.

SELECT 'ALTER TABLE xx ADD CONSTRAINT abc' WHERE not EXISTS (SELECT True FROM pg_constraint WHERE conname = 'abc') \gexec

For me those solutions didn't work because the constraint was a primary key. This one worked for me:

ALTER TABLE <table.name> DROP CONSTRAINT  IF EXISTS <constraint.name> CASCADE;

Considering all the above mentioned answers , the below approach help if you just want to check if a constraint exist in the table in which you are trying to insert and raise a notice if there happens to be one

DO 
$$ BEGIN
IF NOT EXISTS (select constraint_name 
               from information_schema.table_constraints 
               where table_schema='schame_name' and upper(table_name) = 
upper('table_name')  and upper(constraint_name) = upper('constraint_name'))

THEN

   ALTER TABLE TABLE_NAME ADD CONSTRAINT CONTRAINT_NAME..... ;

ELSE raise NOTICE 'Constraint CONTRAINT_NAME already exists in Table TABLE_NAME';   

END IF;
END
$$;

Don't know why so many lines of code ?

-- SELECT "Column1", "Column2", "Column3" , count(star) FROM dbo."MyTable" GROUP BY "Column1" , "Column2" , "Column3" HAVING count(*) > 1;

alter table dbo."MyTable" drop constraint if exists "MyConstraint_Name" ;

ALTER TABLE dbo."MyTable" ADD CONSTRAINT "MyConstraint_Name" UNIQUE("Column1", "Column3", "Column2");