How to list all constraints of a table in PostgreSQL?

Question

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

Answer 1

To dynamically generate a PostgreSQL script that creates a table with all its constraints, you can query the PostgreSQL system catalog tables (pg_catalog) to extract information about columns, primary keys, foreign keys, and check constraints.

Here’s a complete script to generate the CREATE TABLE statement dynamically:

DO $$
DECLARE
    schema_name TEXT := 'public'; -- Replace with your schema name
    table_name TEXT := 'employees'; -- Replace with your table name
    column_def RECORD;
    pk_constraint RECORD;
    fk_constraint RECORD;
    check_constraint RECORD;
    create_table_query TEXT := '';
BEGIN
    -- Start CREATE TABLE statement
    create_table_query := format('CREATE TABLE IF NOT EXISTS %I.%I (\n', schema_name, table_name);

    -- Add columns
    FOR column_def IN 
        EXECUTE format(
            'SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, is_nullable, column_default 
            FROM information_schema.columns 
            WHERE table_schema = %L AND table_name = %L',
            schema_name, table_name
        )
    LOOP
        create_table_query := create_table_query || 
            format('    %I %s%s%s%s,\n',
                column_def.column_name,
                CASE
                    WHEN column_def.data_type = 'character varying' THEN format('character varying(%s)', column_def.character_maximum_length)
                    WHEN column_def.data_type = 'numeric' THEN format('numeric(%s, %s)', column_def.numeric_precision, column_def.numeric_scale)
                    ELSE column_def.data_type
                END,
                CASE WHEN column_def.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
                CASE WHEN column_def.column_default IS NOT NULL THEN format(' DEFAULT %s', column_def.column_default) ELSE '' END,
                CASE WHEN column_def.data_type = 'character varying' THEN ' COLLATE pg_catalog."default"' ELSE '' END
            );
    END LOOP;

    -- Add primary key constraint
    FOR pk_constraint IN
        EXECUTE format(
            'SELECT conname, pg_get_constraintdef(oid, true) AS definition 
            FROM pg_constraint 
            WHERE conrelid = %L::regclass AND contype = %L',
            format('%I.%I', schema_name, table_name), 'p' -- 'p' = Primary Key
        )
    LOOP
        create_table_query := create_table_query || 
            format('    CONSTRAINT %I %s,\n', pk_constraint.conname, pk_constraint.definition);
    END LOOP;

    -- Add foreign key constraints
    FOR fk_constraint IN
        EXECUTE format(
            'SELECT conname, pg_get_constraintdef(oid, true) AS definition 
            FROM pg_constraint 
            WHERE conrelid = %L::regclass AND contype = %L',
            format('%I.%I', schema_name, table_name), 'f' -- 'f' = Foreign Key
        )
    LOOP
        create_table_query := create_table_query || 
            format('    CONSTRAINT %I %s,\n', fk_constraint.conname, fk_constraint.definition);
    END LOOP;

    -- Add check constraints
    FOR check_constraint IN
        EXECUTE format(
            'SELECT conname, pg_get_constraintdef(oid, true) AS definition 
            FROM pg_constraint 
            WHERE conrelid = %L::regclass AND contype = %L',
            format('%I.%I', schema_name, table_name), 'c' -- 'c' = Check Constraint
        )
    LOOP
        create_table_query := create_table_query || 
            format('    CONSTRAINT %I %s,\n', check_constraint.conname, check_constraint.definition);
    END LOOP;

    -- Remove last comma and close the statement
    create_table_query := regexp_replace(create_table_query, ',\n$', '\n');
    create_table_query := create_table_query || ');';

    -- Print the generated query
    RAISE NOTICE '%', create_table_query;
END $$;

Answer 2

This works for when you want to get column name + constraint definition, filtered by schema + table. A bit of a workaround, but works.

SELECT
    SUBSTRING(
        pg_get_constraintdef(con.oid) FROM 7
    ) as "check_definition",

    (REGEXP_MATCHES(
        SUBSTRING(pg_get_constraintdef(con.oid) FROM 7),
        'length\("([^"]+)"\)'
    ))[1] as "column_name"
    
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 = 'my_schema'
    AND rel.relname = 'my_table'
    AND contype = 'c'; --only "check" constraints

| check_definition                                                                | column_name        |
|---------------------------------------------------------------------------------|--------------------|
| (((length("User_Name") > 0) AND (length("User_Name") < 250)))                   | User_Name          |
| (((length("User_AccountNumber") > 0) AND (length("User_AccountNumber") < 250))) | User_AccountNumber |
| (((length("User_FirstName") > 0) AND (length("User_FirstName") < 250)))         | User_FirstName     |
| (((length("User_LastName") > 0) AND (length("User_LastName") < 250)))           | User_LastName      |
| (((length("User_PersonalEmail") > 0) AND (length("User_PersonalEmail") < 250))) | User_PersonalEmail |
| (((length("User_Phone") > 0) AND (length("User_Phone") < 250)))                 | User_Phone         |
| (((length("User_PlaceOfBirth") > 0) AND (length("User_PlaceOfBirth") < 250)))   | User_PlaceOfBirth  |
| (((length("User_WorkEmail") > 0) AND (length("User_WorkEmail") < 250)))         | User_WorkEmail     |

My regex assumes to be looking for the column name in a "text length constraint", the structure being like (((length("User_Name") > 0) AND (length("User_Name") < 250)))

Answer 3

Here is a simple way of getting all constraints of a table in PostgreSQL

SELECT constraint_name, table_name, column_name, ordinal_position FROM information_schema.key_column_usage WHERE table_name = '<your_table_name>';

Answer 4

I wasn't able to get the above solutions to work. Maybe they're no longer supported or more likely I'm doing something wrong. This is how I got it to work. Note that the "contype" column will be abbreviated with the constraint type (e.g. 'c' for check, 'p' for primary key, etc.). I include that variable in case you want to add a WHERE statement to filter for it after the FROM block.

select pgc.conname as constraint_name,
       ccu.table_schema as table_schema,
       ccu.table_name,
       ccu.column_name,
       contype,
        pg_get_constraintdef(pgc.oid)
from pg_constraint pgc
         join pg_namespace nsp on nsp.oid = pgc.connamespace
         join pg_class  cls on pgc.conrelid = cls.oid
         left join information_schema.constraint_column_usage ccu
                   on pgc.conname = ccu.constraint_name
                       and nsp.nspname = ccu.constraint_schema
order by pgc.conname;

Answer 5

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
    

Answer 6

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}