CODEWITHSUNDEEP
postgresqlconstraints
WhiteShadow
WhiteShadow

Reputation: 103

Finding constraints in PostgreSQL

I need to find all the constraint in only one consult and I don't know how to do it.

I am trying to do it whit the next query:

SELECT UPPER(conname) AS restriccion, UPPER(relname) AS tabla, UPPER(pg_catalog.pg_attribute.attname) AS columna

FROM pg_catalog.pg_constraint, pg_catalog.pg_class, pg_catalog.pg_attribute

WHERE contype = 'u'

AND conrelid = pg_catalog.pg_class.oid

AND conrelid = pg_catalog.pg_attribute.attrelid

AND pg_catalog.pg_attribute.attnum = pg_catalog.pg_constraint.conkey[1]

ORDER BY UPPER(conname), UPPER(relname), UPPER(pg_catalog.pg_attribute.attname);

I am doing it changing the letter 'u' with 'c' to see the check constraints but it is a bit difficult...

Is there another way to see all the constraint in only one query? Thanks!

Upvotes: 0

Views: 1262

Answers (1)

gsiems
gsiems

Reputation: 3780

For just looking for check and unique constraints, does the following get you started?

SELECT nr.nspname AS table_schema,
        r.relname AS table_name,
        c.conname AS constraint_name,
        CASE
            WHEN c.contype = 'c' THEN c.consrc 
            END AS search_condition,
        CASE
            WHEN c.contype = 'u' 
                THEN split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 2 ), ')', 1 )
            END AS column_names,
        d.description AS comments
    FROM pg_class r
    INNER JOIN pg_namespace nr
        ON ( nr.oid = r.relnamespace )
    INNER JOIN pg_constraint c
        ON ( c.conrelid = r.oid )
    INNER JOIN pg_namespace nc
        ON ( nc.oid = c.connamespace )
    LEFT OUTER JOIN pg_description d
        ON ( d.objoid = c.oid )
    WHERE r.relkind = 'r'
        AND c.contype IN ( 'c', 'u' )
    ORDER BY r.relname,
        c.conname ;

Update 2016-04-14: Not null constraints are not to be found in the pg_constraint table. The pg_attribute table has that information:

SELECT nr.nspname AS table_schema,
        r.relname AS table_name,
        null::text AS constraint_name,
        'Not Null'::text AS null_condition,
        a.attname AS column_name,
        pg_catalog.col_description ( a.attrelid, a.attnum ) AS comments
    FROM pg_catalog.pg_class r
    LEFT OUTER JOIN pg_catalog.pg_namespace nr
        ON ( nr.oid = r.relnamespace )
    LEFT OUTER JOIN pg_catalog.pg_attribute a
        ON ( c.oid = a.attrelid
            AND a.attnum > 0
            AND NOT a.attisdropped )
    WHERE r.relkind IN ( 'r', 'f', 'm' ) -- tables(r), foreign tables(f), and materialized views(m)
        AND a.attnotnull
    ORDER BY nr.nspname,
        r.relname,
        a.attname ;

Upvotes: 1

Related Questions