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='numeric' THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
WHEN character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length || ')'
WHEN datetime_precision IS NOT NULL AND data_type<>'date' THEN data_type || '(' || datetime_precision || ')'
ELSE data_type END AS data_type,
domain_default,
is_not_null
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)),
columns AS (SELECT table_schema,
table_name,
column_name,
CASE WHEN data_type='numeric' THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
WHEN character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length || ')'
WHEN datetime_precision IS NOT NULL AND data_type<>'date' THEN data_type || '(' || datetime_precision || ')'
ELSE data_type END AS data_type,
column_default,
CASE WHEN is_nullable='YES' THEN FALSE ELSE TRUE END AS is_not_null
FROM Information_schema.columns AS c
WHERE domain_name IS NULL AND
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE')),
fk as (select
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f'),
fk_names as (
select fk.target_schema, fk.target_table, a_target.attname as target_col
from fk inner join pg_attribute a_target on fk.target_col = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false)
SELECT table_schema, table_name, column_name, columns.data_type, column_default, columns.is_not_null, domain_schema, domain_name
FROM columns, domains
WHERE columns.data_type=domains.data_type AND coalesce(columns.column_default,'')=coalesce(domains.domain_default,'') AND columns.is_not_null=domains.is_not_null
AND NOT EXISTS (SELECT 1 FROM fk_names WHERE columns.table_schema=fk_names.target_schema AND columns.table_name=fk_names.target_table AND columns.column_name=fk_names.target_col)
ORDER BY table_schema, table_name, column_name, domain_schema, domain_name;