119

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

3

3 Answers 3

139

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.

6
  • 8
    Notice that pg_catalog.pg_constraint does not contain NOT NULL constraints. Jul 24, 2019 at 8:30
  • @sticky bit, is there anyway to drop all the constraints identified in a table? using single query? How can I pass this list of constraints as input to `ALTER TABLE <table name> DROP CONSTRAINT <conname> CASCADE;?
    – The Great
    Jul 16, 2021 at 10:47
  • What is schema name?
    – nacholibre
    Dec 22, 2021 at 7:41
  • @nacholibre: The name of the schema of the table.
    – sticky bit
    Dec 22, 2021 at 14:34
  • @TheGreat Old comment, I know, but this is a hack for lack of a better one: select concat('alter table ', table_schema, '.', table_name, ' drop constraint ', constraint_name, ';') from information_schema.table_constraints where constraint_name [pattern match]; Just set the query part and copy the result into psql.
    – Rob Skelly
    Feb 1 at 0:13
41

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.

1
  • 1
    I see constraint names in your posted output. Jul 24, 2019 at 8:35
6

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
    
1
  • The where clause table_name not in (...) does not work correctly because different tables can have same fields, this leads to less fields. Also 'integer' as data_type is not always true, an example is an unique email field that is varchar. Jan 3 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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