30

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?

0

4 Answers 4

52

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

3
  • 1
    This modified where condition returns any unique constraints as well: WHERE contype IN ('f', 'p', 'u') Jun 17, 2018 at 0:34
  • is it possible to get a definition of an index in case WITH (FILLFACTOR=80) was provided as well during the definition of the index?
    – arthur
    Jul 20, 2021 at 13:27
  • @arthur: Sure, possible. Those details of a supporting index are not reproduced by pg_get_constraintdef() - though they can be provided during the constraint definition. I suggest you create a new question presenting your case. You can always link to this one for context and add a comment here to get my attention. Jul 25, 2021 at 23:10
14

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.

2
  • 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, 2020 at 19:28
  • 1
    @Anse updated the answer. Thanks for the idea. Feb 8, 2020 at 15:50
12

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 |
2
  • 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, 2020 at 12:03
  • The best and most concise answer here.
    – mjs
    Mar 10, 2023 at 12:36
1

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

SELECT

    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",

    (
        string_to_array(
            (
                string_to_array(
                    pg_get_constraintdef("pg_constraint".oid),
                    '('
                )
            )[2],
            ')'
        )
    )[1] AS "foreign_column_name",

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

    TRIM((
        string_to_array(
            pg_get_constraintdef("pg_constraint".oid),
            '('
        )
    )[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

WHERE

    "pg_constraint".contype IN ( 'f', 'p' )
    AND
    "pg_namespace".nspname = current_schema()
    AND
    "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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