CODEWITHSUNDEEP
postgresql
Mano
Mano

Reputation: 711

Postgres Check Constraint definition

I want to write a query to list the check constraints in a database along with its constraint definition. I am not able to find a column to find the check constraint definition just like the search condition column in oracle all_constraints table. Is there a PG alternate for this?

Upvotes: 0

Views: 1836

Answers (1)

Nikhil B
Nikhil B

Reputation: 393

The catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables and contype column saves data about the type of the constraint i,e

c = check constraint
f = foreign key constraint
p = primary key constraint
u = unique constraint
t = constraint trigger
x = exclusion constraint

select pgc.conname as constraint_name,
       ccu.table_schema as table_schema,
       ccu.table_name,
       ccu.column_name,
       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
where contype ='c'
order by pgc.conname;

Upvotes: 3

Related Questions