Postgres Find All Cascade Deletes

In a database management system like PostgreSQL, using ON DELETE CASCADE is useful to keep your data in sync, but can be a footgun if you use it instead of a the safer ON DELETE SET NULL. So, just to audit you schema, you can use this query to find all the deleting cascades.

SELECT
	n.nspname AS schema_name,
	cl.relname AS table_name,
	att.attname AS column_name,
	referenced_cl.relname AS referenced_table,
	referenced_att.attname AS referenced_column
FROM
	pg_constraint con
	JOIN pg_class cl ON cl.oid = con.conrelid
	JOIN pg_class referenced_cl ON referenced_cl.oid = con.confrelid
	JOIN pg_namespace n ON n.oid = cl.relnamespace
	JOIN pg_attribute att ON att.attrelid = con.conrelid
	AND att.attnum = ANY (con.conkey)
	JOIN pg_attribute referenced_att ON referenced_att.attrelid = con.confrelid
	AND referenced_att.attnum = ANY (con.confkey)
WHERE
	con.contype = 'f'
	AND con.confdeltype = 'c';  -- 'c' stands for CASCADE

Will return something like this…

Don’t lose data