Non-foreign key columns that have no associated CHECK constraints

Copy SQL

WITH constrained_col AS (SELECT ccu.table_schema, ccu.table_name, ccu.column_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_schema, constraint_name)
WHERE cc.check_clause NOT LIKE '%IS NOT NULL' AND 
ccu.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)
UNION SELECT cdu.table_schema, cdu.table_name, cdu.column_name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND catalog_name IS NOT NULL AND schema_name IS NOT NULL)
UNION SELECT A.table_schema, A.table_name , A.column_name 
FROM information_schema.key_column_usage A INNER JOIN information_schema.table_constraints B USING (table_schema, table_name, constraint_name)
INNER JOIN information_schema.columns C USING (table_schema, table_name, 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'))
SELECT C.table_schema, C.table_name, C.column_name, 
CASE WHEN data_type ILIKE 'character%' AND character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length::text || ')'
WHEN data_type ILIKE 'timestamp%' AND datetime_precision IS NOT NULL THEN data_type || '(' || datetime_precision || ')'
WHEN data_type ILIKE 'numeric%' AND numeric_precision IS NOT NULL THEN data_type || '(' || numeric_precision::text || ',' ||coalesce(numeric_scale,0)::text || ')'
WHEN data_type ILIKE 'interval%' AND interval_type IS NOT NULL THEN data_type || '(' || interval_type::text || ')'
WHEN data_type='USER-DEFINED' THEN udt_schema || '.' || udt_name 
ELSE data_type END AS data_type
FROM information_schema.columns C INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON T.table_schema = S.schema_name
WHERE T.table_type = 'BASE TABLE' 
AND (T.table_schema = 'public' OR S.schema_owner<>'postgres')
AND NOT EXISTS (SELECT 1
FROM constrained_col AS cc
WHERE c.table_schema=cc.table_schema AND c.table_name=cc.table_name AND c.column_name = cc.column_name)
ORDER BY table_schema, table_name, ordinal_position;