Postgres: Add constraint if it doesn't already exist

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);');

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

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 $$;

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
$$;