How to get the list of all constraints, their tables and their columns in postgres?

Question

I want to get the list of all constraints, their tables and their columns, something like

constraint                    | table | columns
------------------------------|-------|---------------
table1_colum1_colum2_key       table1   {colum1, colum2}

How can this be done?

You could retrive that from `INFORMATION_SCHEMA`. Here example for Foreign Keys [Postgres: SQL to list table foreign keys](https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys) — Lukasz Szozda, Dec 30 '18 at 21:05
Which constraints are you referring to? Primary Key? Unique constraints? Check Constraints? Not null constraint? Foreign key constraints? Exclusion constraints? — , Dec 30 '18 at 21:22

sticky bit · Accepted Answer · 2018-12-30T21:28:09.913

The constraints can be queried via pg_constraint. Tables are included in pg_class and columns in pg_attributes. Schemas are in pg_namespace.

Something like the following may work for you.

SELECT con.conname "constraint",
       concat(nsp.nspname, '.', rel.relname) "table",
       (SELECT array_agg(att.attname)
               FROM pg_attribute att
                    INNER JOIN unnest(con.conkey) unnest(conkey)
                               ON unnest.conkey = att.attnum
               WHERE att.attrelid = con.conrelid) "columns"
       FROM pg_constraint con
            INNER JOIN pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace;

Thanks, this is what I was looking for. – mljrg Dec 30 '18 at 23:12 — mljrg, Dec 30 '18 at 23:12

How to get the list of all constraints, their tables and their columns in postgres?

1 Answers1