postgresqlconstraints

How to list all constraints of a table in PostgreSQL?

How to list all constraints (Primary key, Foreign Key, check, unique mutual exclusive, ..) of a table in PostgreSQL?

Solution

  • Constraints of the table can be retrieved from catalog-pg-constraint. using the SELECT query.

    SELECT con.*
    FROM pg_catalog.pg_constraint con
        INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
        INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
        WHERE nsp.nspname = '{schema name}'
             AND rel.relname = '{table name}';

    and the same can be viewed in PSQL using

    \d+ {SCHEMA_NAME.TABLE_NAME}