SELECT
n.nspname AS table_schema,
c.relname AS table_name,
conname AS constraint_name,
CASE WHEN o.contype='p' THEN 'PRIMARY KEY'
WHEN o.contype='u' THEN 'UNIQUE'
WHEN o.contype='f' THEN 'FOREIGN KEY'
WHEN o.contype='c' THEN 'TABLE CHECK'
WHEN o.contype='x' THEN 'EXCLUDE'
WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS constraint_type
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND length(relname)+2>length(conname)
ORDER BY table_schema, table_name, constraint_name;