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'