Postgres list all constraints

list-constraints.sql

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'

very helpful! thanks man

hi,
thnx for sharing the code,

In postgreSQL we can have same constraint name in different schema with the same table name or different. In that case we are getting some incorrect records.
by adding constraint schema name as qualifier we can avoid duplicate
tc.constraint_schema = kcu.constraint_schema ,
ccu.constraint_schema = tc.constraint_schema

Thank you very much for sharing.

nice :)

nice one.

Thanks a lot ^^

Holy crap! Was this gist so popular? I didn't realize 😆

nice