List all foreign keys in PostgreSQL Database
Knowledge-base/Databases-queries/PostgreSQL queries
Sep 19th, 2021
In this article, you will learn several ways to list all foreign keys for all tables in a PostgreSQL database.
1. Using SQL Query
To show all foreign keys in the PostgreSQL database, you can use the following SQL query:
SELECT conrelid::regclass AS table_name,
conname AS foreign_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f'
AND connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;Sample result
The output of the SQL query:
In addition, to retrieve all foreign keys for a specific table in a PostgreSQL database you may use the following SQL query:
SELECT conrelid::regclass AS table_name, conname AS foreign_key, pg_get_constraintdef(oid) FROM pg_constraint WHERE contype = 'f' and conrelid::regclass::text = 'your_table_name' AND connamespace = 'public'::regnamespace ORDER BY conrelid::regclass::text, contype DESC;
2. Using psql command-line
To get a list of all foreign keys of the table using psql you can use the \d your_table_name command line.
3. List all foreign keys with ERBuilder Data Modeler
Using ERBuilder Data Modeler you can visuals all foreign keys for each table in your PostgreSQL database. With a double-click on the table then columns you will get all the foreign keys of that table as shown in the screen down bellow
ERBuilder Data Modeler allows you to get all the foreign keys in your PostgreSQL database as you can see from the screen which follows:
For more details just double-click on the name of the relationship and you will get the parent table, the child table, and the parent key used which is the foreign key for the child table.
Start modeling your database with us today
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
