How to list all constraints of a table in PostgreSQL?

Question

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

Answer 1

Constraints can be retrieved via pg_catalog.pg_constraint.

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>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.

Answer 2

In the psql command line this information is in the table sheet, obtained with the \d+ command. d+ also informs on the NOT NULL constraints, something that is not present in the pg_catalog.pg_constraint table. An example:

# \d+ observations.stream   
                                                  Table "observations.stream"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target |                 Description                 
--------+-------------------+-----------+----------+---------+----------+--------------+---------------------------------------------
 id     | integer           |           | not null |         | plain    |              | 
 name   | character varying |           | not null |         | extended |              | This should be a table in the import schema
 min_id | integer           |           | not null |         | plain    |              | 
 about  | character varying |           | not null |         | extended |              | 
Indexes:
    "stream_pkey" PRIMARY KEY, btree (id)
    "stream_name_key" UNIQUE CONSTRAINT, btree (name)
Check constraints:
    "stream_id_check" CHECK (id > 0)
Referenced by:
    TABLE "profile" CONSTRAINT "profile_id_stream_fkey" FOREIGN KEY (id_stream) REFERENCES stream(id)

The caveat here is that you do not get the names of all the constraints this way.

Answer 3

Here is PostgreSQL specific answer. It will retrieve all columns and their relationship as well:

select *FROM (
from (
    select
        pgc.contype as constraint_type,
        ccu.table_schema as table_schema,
        kcu.table_name as table_name,
        case when (pgc.contype = 'f') then kcu.column_name else ccu.column_name end as column_name, 
        case when (pgc.contype = 'f') then ccu.table_name else (null) end as reference_table,
        case when (pgc.contype = 'f') then ccu.column_name else (null) end as reference_col,
        case when (pgc.contype = 'p') then 'yes' else 'no' end as auto_inc,
        case when (pgc.contype = 'p') then 'no' else 'yes' end as is_nullable,
        'integer' as data_type,
        '0' as numeric_scale,
        '32' as numeric_precision
    from
        pg_constraint as pgc
        join pg_namespace nsp on nsp.oid = pgc.connamespace
        join pg_class cls on pgc.conrelid = cls.oid
        join information_schema.key_column_usage kcu on kcu.constraint_name = pgc.conname
        left join information_schema.constraint_column_usage ccu on pgc.conname = ccu.constraint_name 
        and nsp.nspname = ccu.constraint_schema
     union
        select 
            null as constraint_type ,
            table_schema,
            table_name,
            column_name, 
            null as refrence_table, 
            null as refrence_col, 
            'no' as auto_inc,
            is_nullable,
            data_type,
            numeric_scale,
            numeric_precision
        from information_schema.columns cols 
        where 
            table_schema = 'public'
            and concat(table_name, column_name) not in(
                select concat(kcu.table_name, kcu.column_name)
                from
                pg_constraint as pgc
                join pg_namespace nsp on nsp.oid = pgc.connamespace
                join pg_class cls on pgc.conrelid = cls.oid
                join information_schema.key_column_usage kcu on kcu.constraint_name = pgc.conname
                left join information_schema.constraint_column_usage ccu on pgc.conname = ccu.constraint_name 
                and nsp.nspname = ccu.constraint_schema
            )
    ) as foo
order by table_name asc, column_name

Answer 4

The previous answer showed unreadable checks column that was compiled or something

This query results are readable in all directions

select tc.table_schema,
  tc.table_name,
  string_agg(col.column_name, ', ') as columns,
  tc.constraint_name,
  cc.check_clause
from information_schema.table_constraints tc
join information_schema.check_constraints cc
  on tc.constraint_schema = cc.constraint_schema
  and tc.constraint_name = cc.constraint_name
join pg_namespace nsp on nsp.nspname = cc.constraint_schema
join pg_constraint pgc on pgc.conname = cc.constraint_name
  and pgc.connamespace = nsp.oid
  and pgc.contype = 'c'
join information_schema.columns col
  on col.table_schema = tc.table_schema
  and col.table_name = tc.table_name
  and col.ordinal_position = ANY(pgc.conkey)
where tc.constraint_schema not in('pg_catalog', 'information_schema')
group by tc.table_schema,
  tc.table_name,
  tc.constraint_name,
  cc.check_clause
order by tc.table_schema,
  tc.table_name;