WITH domain_not_null AS (
SELECT nspname AS domain_schema,
typname AS domain_name,
typnotnull AS is_not_null
FROM pg_catalog.pg_type AS t INNER JOIN pg_catalog.pg_namespace AS n ON t.typnamespace=n.oid
WHERE typtype='d'),
domains AS (
SELECT domain_schema,
domain_name,
CASE WHEN data_type IN ('character varying', 'character') THEN data_type || '(' || character_maximum_length || ')'
WHEN data_type IN ('numeric', 'decimal') THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
ELSE data_type END AS data_type,
domain_default,
is_not_null,
EXISTS (SELECT 1 FROM Information_schema.domain_constraints AS dc WHERE dc.domain_schema=d.domain_schema AND dc.domain_name=d.domain_name) AS has_check,
(SELECT Count(*) AS cnt FROM Information_schema.columns c WHERE c.domain_schema=d.domain_schema AND c.domain_name=d.domain_name AND (c.table_schema, c.table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE')) AS number_of_columns
FROM Information_schema.domains AS d INNER JOIN domain_not_null USING (domain_schema, domain_name)
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT domain_schema, domain_name, data_type, domain_default, is_not_null, has_check, number_of_columns
FROM Domains
WHERE number_of_columns<=1 OR (number_of_columns>1 AND has_check=FALSE AND domain_default IS NULL)
ORDER BY has_check DESC, domain_default NULLS FIRST, number_of_columns, domain_schema, domain_name;