Query below lists all table (and view) constraints - primary keys, unique constraints , foreign keys and default constraints.

Query

select tco.table_schema,
       tco.table_name,
       tco.constraint_type,
       tco.constraint_name,
       rel_tco.table_schema || '.' || rel_tco.table_name as constraint_details
from information_schema.table_constraints tco
left join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
left join information_schema.table_constraints rel_tco
          on rco.unique_constraint_schema = rel_tco.constraint_schema
          and rco.unique_constraint_name = rel_tco.constraint_name
where (tco.constraint_type = 'FOREIGN KEY'
       and rel_tco.constraint_name is not null) 
      or tco.constraint_type != 'FOREIGN KEY'
union all
select table_schema,
       table_name,
       'DEFAULT CONSTRAINT' as constraint_type,
       'CHECK ' || column_name as constraint_name,
       column_default as details
from information_schema.columns
where column_default is not null
order by table_schema,
         table_name;

Columns

• table_schema - name of the table schema
• table_name - name of the table
• constraint_type - type of constraint:
  • Primary key
  • Foregin key
  • Unique
  • Default constraint
• constraint_name - name of constraint or index
• constraint_details - details of this constraint:
  • Primary key - NULL
  • Unique - NULL
  • Foregin key - parent table name
  • Default constraint - column name and default value definition

Rows

• One row represents one constraint: PK,FK, Unique, Default
• Scope of rows: all constraints
• Ordered by schema and table name

Sample results