Postgresql Constraints (display)

Question

I need to display the list of integrity constraints of my solution by indicating the name of the constraint, its type and the detail of the constraint (message that explains what validates the constraint), all sorted by table name and constraint name.

This is what I tried :

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'mytable'

How can I display all of that and also give a message that indicates what validates the constraint ?

Answer 1

Please try below code.

SELECT
    "ns"."nspname" AS "table_schema",
    "t"."relname" AS "table_name",
    "cnst"."conname" AS "constraint_name", pg_get_constraintdef ( "cnst"."oid" ) AS "expression",
CASE
        "cnst"."contype" 
        WHEN 'p' THEN
        'PRIMARY' 
        WHEN 'u' THEN
        'UNIQUE' 
        WHEN 'c' THEN
        'CHECK' 
        WHEN 'x' THEN
        'EXCLUDE' 
    END AS "constraint_type",
    "a"."attname" AS "column_name"
FROM
    "pg_constraint" "cnst" 
    INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid"
    INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace"
    LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid" 
    AND "a"."attnum" = ANY ( "cnst"."conkey" )