Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table.

I have pgAdmin III.

Is there a way to do this automatically and not go over each table manually?

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following:

SELECT conrelid::regclass AS table_from
     , conname
     , pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  contype IN ('f', 'p ')
AND    connamespace = 'public'::regnamespace  -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

Result:

table_from | conname    | pg_get_constraintdef
------------+------------+----------------------
 tbl        | tbl_pkey   | PRIMARY KEY (tbl_id)
 tbl        | tbl_col_fk | FOREIGN KEY (col) REFERENCES tbl2(col) ON UPDATE CASCADE
...

Returns all primary and foreign keys for all tables in the given schema, ordered by tablename, PKs first.

The manual about pg_constraint.

The manual about object identifier types (regclass,regnamespace, ...).

StackExchange Database Administrators Q#36979, answer score: 50