83

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

3
91

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.

2
  • 4
    Notice that pg_catalog.pg_constraint does not contain NOT NULL constraints. Jul 24 '19 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 at 10:47
30

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 '19 at 8:35
2

Here is POSTGRES specific answer ..... it will retrive all columns and their relationship as well

SELECT * FROM (

SELECT
    pgc.contype as constraint_type,
    pgc.conname as constraint_name,
    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 , null as constraint_name , 'public' as "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 1=1
    AND table_schema = 'public'
    and column_name not in(
        SELECT CASE WHEN (pgc.contype = 'f') THEN kcu.COLUMN_NAME ELSE kcu.COLUMN_NAME END 
        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 desc
    

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.