List all foreign keys in PostgreSQL Database

Knowledge-base/Databases-queries/PostgreSQL queries

Sep 19th, 2021

[wp_social_sharing social_options='facebook,twitter,linkedin,pinterest,reddit' twitter_username='softbuilder1' facebook_text='Share on Facebook' twitter_text='Share on Twitter' linkedin_text='Share on Linkedin' pinterest_text="Share on Pinterest" reddit_text="Share on Reddit" icon_order='f,t,l,p,x,r' show_icons='2' before_button_text='' text_position='' social_image='']

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:

list all foreign keys in a PostgreSQL database

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.

list all foreign keys using psql

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!

Share on:

[wp_social_sharing social_options='facebook,twitter,linkedin,pinterest,reddit' twitter_username='softbuilder1' facebook_text='Share on Facebook' twitter_text='Share on Twitter' linkedin_text='Share on Linkedin' pinterest_text="Share on Pinterest" reddit_text="Share on Reddit" icon_order='f,t,l,p,x,r' show_icons='2' before_button_text='' text_position='' social_image='']

Choose a product

Choose a product and get 15 Day fully-functional free trial. No credit card required