Domain candidates

Copy SQL

WITH cols AS (SELECT c.table_schema, c.table_name, c.column_name, c.column_default, c.collation_name, c.is_nullable,
CASE WHEN c.data_type LIKE '%char%' THEN
c.data_type || '(' || c.character_maximum_length || ')'
ELSE c.data_type
END AS data_type
FROM INFORMATION_SCHEMA.columns AS c
WHERE c.domain_name IS NULL AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables AS t WHERE table_type='BASE TABLE') AND 
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata AS s
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

fk_cols AS (SELECT A.table_schema, A.table_name , A.column_name 
FROM information_schema.key_column_usage A
INNER JOIN information_schema.table_constraints B
ON A.table_schema=B.table_schema AND A.table_name=B.table_name AND A.constraint_name=B.constraint_name
INNER JOIN information_schema.columns C
ON A.table_schema=C.table_schema AND A.table_name=C.table_name AND A.column_name=C.column_name
INNER JOIN information_schema.schemata D
ON A.table_schema=D.schema_name
WHERE B.constraint_type='FOREIGN KEY'
AND (A.table_schema = 'public'
OR D.schema_owner<>'postgres')),

checks AS (
SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name, 
a.attname AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 AND o.contype = 'c')

SELECT string_agg(c.table_schema ||'.'|| c.table_name ||'.'|| c.column_name,';<br>' ORDER BY c.table_schema, c.table_name, c.column_name) AS columns, data_type, is_nullable, column_default, collation_name,  count(*) AS number_of_columns 
FROM cols AS c 
WHERE NOT EXISTS (SELECT 1 
FROM fk_cols AS f 
WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name AND c.column_name=f.column_name)
AND EXISTS (SELECT 1
FROM checks AS cs
WHERE c.table_schema=cs.table_schema AND c.table_name=cs.table_name AND c.column_name=cs.column_name)
GROUP BY data_type, is_nullable, column_default, collation_name
HAVING count(*)>1
ORDER BY Count(*) DESC, data_type, is_nullable DESC, column_default, collation_name;