Can I use a sql query to get a list of all primary and foreign keys in a PostgreSQL database

Question

I have a client using a PostgreSQL database. I'm new to PostgreSQL and need a little help. I need to get a list of primary and foreign keys from all the tables in the public schema. There seems to be inconsistent data in the database. I tried queries from other posts with no success. When I look at the constraints on the table I can see the primary and foreign keys. How can this same information using SQL?

table primary key properties foreign key properties

Look at the `information_schema` schema. It is self-explanatory. — Mike Organek, Aug 17 '20 at 22:09
check this answer: https://stackoverflow.com/a/1152321/5651972 — Khalil Meg, Aug 17 '20 at 22:39

score 1 · Answer 1 · answered Aug 17 '20 at 22:33

Using information_schema and restricting to public schema. NOTE: this will only display tables you have privileges on. So if you want to see everything you will need to do this as sufficiently privileged user.

SELECT
    constraint_schema,
    table_name,
    constraint_type,
    constraint_name
FROM
    information_schema.table_constraints
WHERE
    constraint_schema = 'public'
    AND constraint_type IN ('FOREIGN KEY', 'PRIMARY KEY')
ORDER BY
    table_name,
    constraint_type;

Thank you. I'll give it a try. – Terry Clark Aug 19 '20 at 15:21 — Terry Clark, Aug 19 '20 at 15:21

Can I use a sql query to get a list of all primary and foreign keys in a PostgreSQL database

1 Answers1