Copy SQL

WITH dom AS (SELECT domain_schema, 
domain_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,
domain_default,
collation_schema,
collation_name
FROM INFORMATION_SCHEMA.domains
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,
string_agg('Name: ' || dc.constraint_name || 
'
check: ' || cc.check_clause ||
'
is deferrable: ' || dc.is_deferrable ||
'
initially deferred: ' || dc.initially_deferred, ',
' ORDER BY dc.constraint_name) AS constraints,
collation_schema, collation_name
FROM dom LEFT JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
LEFT JOIN INFORMATION_SCHEMA.check_constraints AS cc ON dc.domain_schema=cc.constraint_schema AND dc.constraint_name=cc.constraint_name AND check_clause LIKE '%VALUE%'
GROUP BY domain_schema, domain_name, data_type, domain_default, collation_schema, collation_name
ORDER BY domain_schema, domain_name;