Retrieving all PK and FK
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
, ...).
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 |
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
.