How to list all primary keys in PostgreSQL database
Sep 09th, 2021,If you are trying to list all primary keys for all tables in the PostgreSQL database. You can do so with one of the methods in this article.
1. Using SQL Query
The first way would be using an SQL query to show all primary keys for all tables in the PostgreSQL database. The following script can get all primary keys:
SELECT conrelid::regclass AS table_name,
conname AS primary_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'p'
AND connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;
Sample results
Sample result of the query which will list all primary keys for all tables:
To list all primary keys for a specific table in a PostgreSQL database you can use the following SQL query:
SELECT conrelid::regclass AS table_name,
conname AS primary_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'p'
AND connamespace = 'public'::regnamespace
AND conrelid::regclass::text = ‘your_table_name’
ORDER BY conrelid::regclass::text, contype DESC;
2. Using psql command-line
Another method is to use psql \d your_table_name command to list all details of a table in the PostgreSQL database and among those details, we will retrieve the primary key.
3. List all primary keys with ERBuilder Data Modeler
ERBuilder Data Modeler allows you to visualize your database structures which means you will be capable of listing all primary keys for a table in your PostgreSQL database.
With ERBuilder Data Modeler, you can get more details of keys just by double-clicking on it from the list. As you can add a new primary key, edit an exciting one, or delete it.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
