Recreate all foreign keys in all tables as deferrable (batch)

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

You can update the catalog tables directly. See http://www.postgresql.org/message-id/[email protected]