Script - Constraints Information

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article provides a simple script to help the administrator identify constraint level information.

Note: Please verify the script on a test cluster before running it on production.

 

Resolution

List of constraints w.r.t table

select c.nspname||'.'||b.relname "Relation Name", 
    a.conname as "Constraint Name",
    case a.contype when 'p' then 'Primary key'
    	           when 'c' then 'Check'
		   when 'f' then 'Foreign Key'
    end as "Constraint Type"
from pg_constraint a , pg_class b , pg_namespace c 
where a.conrelid=b.oid
and c.oid=b.relnamespace;

Details about Primary Key constraints

select b.table_schema ||'.'|| b.table_name as "Relation name",
     b.constraint_schema ||'.'||b.constraint_name as "Constraint name",
     b.column_name as "Constraint column",
     b.ordinal_position as "Column position"
from pg_constraint a, information_schema.key_column_usage b
where b.constraint_name=a.conname
and a.contype='p'
order by b.constraint_name, b.ordinal_position;

Details about Foreign Key constraints

select b.table_schema ||'.' || b.table_name as "Relation name",
     a.constraint_schema ||'.'||a.constraint_name as "Constraint name",
     b.column_name as "Constraint column",
     c.table_schema ||'.' || c.table_name as "Foreign table name",
     c.column_name as "Foreign table column",
     b.ordinal_position as "Column position"
from information_schema.referential_constraints a , information_schema.key_column_usage b , information_schema.key_column_usage c
where b.constraint_name = a.constraint_name
and c.ordinal_position = b.position_in_unique_constraint
and c.constraint_name = a.unique_constraint_name
order by a.constraint_name, b.ordinal_position;

Details about Check Key constraints

select c.nspname||'.'||b.relname "Relation Name", 
    a.conname as "Constraint Name",
    a.consrc as "Constraint Condition" 
from pg_constraint a , pg_class b , pg_namespace c 
where a.conrelid=b.oid
and c.oid=b.relnamespace
and a.contype='c'
order by 1,2;

Details about "Not Null" columns

select c.nspname||'.'||b.relname "Relation Name", 
       attname "Column Name"
from pg_attribute a , pg_class b , pg_namespace c
where c.oid=b.relnamespace
and b.oid=a.attrelid
and a.attnotnull='t'
and a.attstattarget < 0
and c.nspname not in ('pg_toast','pg_catalog','pg_bitmapindex','pg_aoseg','information_schema','gp_toolkit')
order by 1,2;

 

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions