2

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?

8

You can do this by generating the necessary script.

select 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' drop constraint '||quote_ident(conname)||';'||chr(10)||
       'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' add constraint '||quote_ident(conname)||' '||
       pg_get_constraintdef(c.oid, true)||' deferrable initially immediate;' as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname in ('public')   --<<< adjust the schema name(s) here
 and c.contype = 'f';

Spool the output of the above statement into a file and then run that generated script.

  • This deserves far more points. +10 – beldaz May 28 '17 at 10:49
  • Great answer. Since PostgreSQL 9.4, this can also be done by altering the constraints directly: SELECT 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)|| ' alter constraint '||quote_ident(conname)||' deferrable initially immediate;' FROM pg_constraint c JOIN pg_class tb ON tb.oid = c.conrelid JOIN pg_namespace ns ON ns.oid = tb.relnamespace WHERE ns.nspname IN ('public') AND c.contype = 'f'; – Austin Drenski Aug 28 '18 at 13:42
1

You can update the catalog tables directly. See http://www.postgresql.org/message-id/066E39B5-0E4F-11D9-965C-000A9578CFCC@kcilink.com

  • Please edit this answer to include a summary of the actions needed, and how/why they solve the problem. We typically delete answers that do not stand on their own (for example, if the link stops working). – Paul White Jan 8 '16 at 6:56
1

As a follow-up to the answer from @a-horse-with-no-name, since PostgreSQL 9.4 constraints can be modified directly:

SELECT 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       '  alter constraint '||quote_ident(conname)||' deferrable initially immediate;'
FROM pg_constraint c
       JOIN pg_class tb ON tb.oid = c.conrelid
       JOIN pg_namespace ns ON ns.oid = tb.relnamespace
WHERE ns.nspname IN ('public') AND c.contype = 'f';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.