timwis/foreign_keys.sql

## foreign_keys.sql
SELECT
	con.oid as "constraint_oid",
	conname as "constraint_name",
  pg_catalog.pg_get_constraintdef(con.oid, true) as "constraint_definition",
  conrelid as "constrained_table_oid",
	col.attname as "constrained_column_name",
  confrelid as "referenced_table_oid",
  ref.attname as "referenced_column_name"
FROM pg_catalog.pg_constraint as con
CROSS JOIN UNNEST(conkey) WITH ORDINALITY as constrained_cols(col_num, col_index)
JOIN pg_catalog.pg_attribute as col
	ON col.attrelid = con.conrelid
	AND col.attnum = constrained_cols.col_num
CROSS JOIN UNNEST(confkey) WITH ORDINALITY as referenced_cols(col_num, col_index)
JOIN pg_catalog.pg_attribute as ref
	ON ref.attrelid = con.conrelid
	AND ref.attnum = referenced_cols.col_num
WHERE con.contype = 'f'
     AND conparentid = 0
ORDER BY conname

## results.tsv

          
            oid
            table_oid
            name
            definition
            column_name
            ref_table_oid
            ref_column_name

            
              27200
              27145
              bank_accounts_bank_connection_id_fkey
              FOREIGN KEY (bank_connection_id) REFERENCES bank_connections(id) ON DELETE CASCADE
              bank_connection_id
              27151
              id

            
              27205
              27151
              bank_connections_institution_id_fkey
              FOREIGN KEY (institution_id) REFERENCES institutions(id)
              institution_id
              27157
              id

            
              27210
              27151
              bank_connections_user_id_fkey
              FOREIGN KEY (user_id) REFERENCES users(id)
              user_id
              27165
              id

            
              27328
              27309
              blog_posts_category_id_fkey
              FOREIGN KEY (category_id) REFERENCES categories(id)
              category_id
              27318
              id

            
              27348
              27334
              post_reactions_blog_post_id_fkey
              FOREIGN KEY (blog_post_id) REFERENCES blog_posts(id)
              blog_post_id
              27309
              id

            
              27343
              27334
              post_reactions_user_id_fkey
              FOREIGN KEY (user_id) REFERENCES users(id)
              user_id
              27165
              id

            
              27215
              27171
              users_tokens_user_id_fkey
              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
              user_id
              27165
              id
	SELECT
	con.oid as "constraint_oid",
	conname as "constraint_name",
	pg_catalog.pg_get_constraintdef(con.oid, true) as "constraint_definition",
	conrelid as "constrained_table_oid",
	col.attname as "constrained_column_name",
	confrelid as "referenced_table_oid",
	ref.attname as "referenced_column_name"
	FROM pg_catalog.pg_constraint as con
	CROSS JOIN UNNEST(conkey) WITH ORDINALITY as constrained_cols(col_num, col_index)
	JOIN pg_catalog.pg_attribute as col
	ON col.attrelid = con.conrelid
	AND col.attnum = constrained_cols.col_num
	CROSS JOIN UNNEST(confkey) WITH ORDINALITY as referenced_cols(col_num, col_index)
	JOIN pg_catalog.pg_attribute as ref
	ON ref.attrelid = con.conrelid
	AND ref.attnum = referenced_cols.col_num
	WHERE con.contype = 'f'
	AND conparentid = 0
	ORDER BY conname
oid	table_oid	name	definition	column_name	ref_table_oid	ref_column_name
27200	27145	bank_accounts_bank_connection_id_fkey	FOREIGN KEY (bank_connection_id) REFERENCES bank_connections(id) ON DELETE CASCADE	bank_connection_id	27151	id
27205	27151	bank_connections_institution_id_fkey	FOREIGN KEY (institution_id) REFERENCES institutions(id)	institution_id	27157	id
27210	27151	bank_connections_user_id_fkey	FOREIGN KEY (user_id) REFERENCES users(id)	user_id	27165	id
27328	27309	blog_posts_category_id_fkey	FOREIGN KEY (category_id) REFERENCES categories(id)	category_id	27318	id
27348	27334	post_reactions_blog_post_id_fkey	FOREIGN KEY (blog_post_id) REFERENCES blog_posts(id)	blog_post_id	27309	id
27343	27334	post_reactions_user_id_fkey	FOREIGN KEY (user_id) REFERENCES users(id)	user_id	27165	id
27215	27171	users_tokens_user_id_fkey	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE	user_id	27165	id