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;


 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, ...).

| improve this answer | |
  • 1
    This modified where condition returns any unique constraints as well: WHERE contype IN ('f', 'p', 'u') – Daniel Waltrip Jun 17 '18 at 0:34

Based on Erwin solution:

SELECT conrelid::regclass AS "FK_Table"
      ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column"
      ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position(' REFERENCES ' in pg_get_constraintdef(c.oid))+12, position('(' in substring(pg_get_constraintdef(c.oid), 14))-position(' REFERENCES ' in pg_get_constraintdef(c.oid))+1) END AS "PK_Table"
      ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14, position(')' in substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14))-1) END AS "PK_Column"
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %'
ORDER  BY pg_get_constraintdef(c.oid), conrelid::regclass::text, contype DESC;

Will return a table of form:

| FK_Table | FK_Column | PK_Table | PK_Column |
| improve this answer | |
  • That is nearly what HeidiSQL would need to retrieve foreign key definitions. Is there a way to do the same more elegant, with less LIKE, SUBSTRING and POSITION calls? I am almost there, but columns are listed twice with my query. – Anse Jan 30 at 12:03

No need to parse pg_get_constraintdef(), just use columns of pg_constraint table to obtain other details (the docs).

Here constraint_type can be:

  • p - primary key,
  • f - foreign key,
  • u - unique,
  • c - check constraint,
  • x - exclusion,
  • ...

Based on Erwin's answer:

SELECT c.conname                                 AS constraint_name,
   c.contype                                     AS constraint_type,
   sch.nspname                                   AS "self_schema",
   tbl.relname                                   AS "self_table",
   ARRAY_AGG(col.attname ORDER BY u.attposition) AS "self_columns",
   f_sch.nspname                                 AS "foreign_schema",
   f_tbl.relname                                 AS "foreign_table",
   ARRAY_AGG(f_col.attname ORDER BY f_u.attposition) AS "foreign_columns",
   pg_get_constraintdef(c.oid)                   AS definition
FROM pg_constraint c
       LEFT JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS u(attnum, attposition) ON TRUE
       LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS f_u(attnum, attposition) ON f_u.attposition = u.attposition
       JOIN pg_class tbl ON tbl.oid = c.conrelid
       JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
       LEFT JOIN pg_attribute col ON (col.attrelid = tbl.oid AND col.attnum = u.attnum)
       LEFT JOIN pg_class f_tbl ON f_tbl.oid = c.confrelid
       LEFT JOIN pg_namespace f_sch ON f_sch.oid = f_tbl.relnamespace
       LEFT JOIN pg_attribute f_col ON (f_col.attrelid = f_tbl.oid AND f_col.attnum = f_u.attnum)
GROUP BY constraint_name, constraint_type, "self_schema", "self_table", definition, "foreign_schema", "foreign_table"
ORDER BY "self_schema", "self_table";

Results are ordered by schema and table.

Technical note: see this question about with ordinality.

| improve this answer | |
  • 1
    I wonder how to retrieve the referenced columns plus their table and schema, without having to parse the result of pg_get_constraintdef()? – Anse Feb 2 at 19:28
  • 1
    @Anse updated the answer. Thanks for the idea. – Evgeny Nozdrev Feb 8 at 15:50

Recently had to implement this for a Data Access Layer that builds CRUD utilities based on info schema, ended up going with this.


    current_schema() AS "schema",
    current_catalog AS "database",
    "pg_constraint".conrelid::regclass::text AS "primary_table_name",
    "pg_constraint".confrelid::regclass::text AS "foreign_table_name",

    )[1] AS "foreign_column_name",

    "pg_constraint".conindid::regclass::text AS "constraint_name",

    )[1]) AS "constraint_type",

    pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"

FROM pg_constraint AS "pg_constraint"

JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace


    "pg_constraint".contype IN ( 'f', 'p' )
    "pg_namespace".nspname = current_schema()
    "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.