Created
February 23, 2021 09:55
-
-
Save izmailoff/905332bf4ffb4fb7c27255cfea6ea1a4 to your computer and use it in GitHub Desktop.
Change all Postgres foreign keys to on update cascade - generates sql which you can execute.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
cl2.relname as child_table, | |
att2.attname as "child_column", | |
cl.relname as "parent_table", | |
att.attname as "parent_column", | |
conname, | |
'alter table ' || cl2.relname || ' drop constraint ' || conname || ';' as drop_stmt, | |
'alter table ' || cl2.relname || ' add constraint ' || conname | |
|| ' foreign key (' || att2.attname || ') references ' || cl.relname | |
|| ' on update cascade deferrable initially deferred;' as create_stmt | |
from | |
(select | |
unnest(con1.conkey) as "parent", | |
unnest(con1.confkey) as "child", | |
con1.confrelid, | |
con1.conrelid, | |
con1.conname | |
from | |
pg_class cl | |
join pg_namespace ns on cl.relnamespace = ns.oid | |
join pg_constraint con1 on con1.conrelid = cl.oid | |
where | |
cl.relname not like 'django%' and cl.relname not like 'auth%' | |
and ns.nspname = 'public' | |
and con1.contype = 'f' | |
) con | |
join pg_attribute att on | |
att.attrelid = con.confrelid and att.attnum = con.child | |
join pg_class cl on | |
cl.oid = con.confrelid | |
join pg_class cl2 on cl2.oid = con.conrelid | |
join pg_attribute att2 on | |
att2.attrelid = con.conrelid and att2.attnum = con.parent; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment