Skip to content

Instantly share code, notes, and snippets.

@PickledDragon
Created October 25, 2015 11:30
Show Gist options
  • Save PickledDragon/dd41f4e72b428175354d to your computer and use it in GitHub Desktop.
Save PickledDragon/dd41f4e72b428175354d to your computer and use it in GitHub Desktop.
Postgres list all constraints
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'
@WalleEve
Copy link

WalleEve commented Aug 6, 2018

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

@hecklek
Copy link

hecklek commented May 15, 2019

Thank you very much for sharing.

@kalistons
Copy link

nice :)

@nwbkhan
Copy link

nwbkhan commented Jan 14, 2020

nice one.

@mYakut
Copy link

mYakut commented Sep 28, 2020

Thanks a lot ^^

@PickledDragon
Copy link
Author

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

@fabian-campos-imprivata

nice

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment