Restrict name restriction in Postgresql

Question

How can I remove a restriction name in Postgresql just by knowing the name? I have a list of restrictions that are autogenerated by a third party script. I need to delete them without knowing the table name only the name of the constraint.

Answer 1

You need to get the table names by running the following query:

SELECT * FROM information_schema.constraint_table_usage WHERE table_name = 'your_table' 

Alternatively, you can use pg_constraint to get this information.

 select n.nspname as schema_name, t.relname as table_name, c.conname as constraint_name from pg_constraint c join pg_class t on c.conrelid = t.oid join pg_namespace n on t.relnamespace = n.oid where t.relname = 'your_table_name'; 

Then you can run the required ALTER TABLE statement:

 ALTER TABLE your_table DROP CONSTRAINT constraint_name; 

Of course, you can make the request return the full alter statement:

 SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';' FROM information_schema.constraint_table_usage WHERE table_name in ('your_table', 'other_table') 

Remember to include table_schema in the WHERE clause (and ALTER statement) if there are multiple schemas with the same tables.

Answer 2

If your on 9.x from PG you can use the DO statement to run it. Just do what a_horse_with_no_name did, but apply it to the DO statement.

 DO $$DECLARE r record; BEGIN FOR r IN SELECT table_name,constraint_name FROM information_schema.constraint_table_usage WHERE table_name IN ('your_table', 'other_table') LOOP EXECUTE 'ALTER TABLE ' || quote_ident(r.table_name)|| ' DROP CONSTRAINT '|| quote_ident(r.constraint_name) || ';'; END LOOP; END$$;