Skip to content

Instantly share code, notes, and snippets.

@timwis

timwis/foreign_keys.sql

Last active November 15, 2023 20:05
Show Gist options
  • Save timwis/9f08632f368c7e532b549b70dd850bc7 to your computer and use it in GitHub Desktop.
Save timwis/9f08632f368c7e532b549b70dd850bc7 to your computer and use it in GitHub Desktop.
Download ZIP
Get foreign keys
Raw
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
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment