PostgreSQL – describe constraints with schema and table

Here is a query which will look at the pg_catalog views to extract a list of constraints along with their respective schema, table, name, and definition.

SELECT 
  pg_namespace.nspname as schema_name,
  pg_class.relname as table_name,
  pg_constraint.conname as constraint_name,
  pg_get_constraintdef(pg_constraint.oid, true) AS constraint_def, *
FROM 
  pg_catalog.pg_constraint 
  INNER JOIN pg_catalog.pg_namespace ON pg_constraint.connamespace = pg_namespace.oid 
  INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE True
  AND pg_namespace.nspname = 'public'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s