Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
check collation for all public text columns postgres
n.nspname as "Schema",
c.relname as "Name",
pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attrelid as table_id,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_attribute a on a.attrelid = c.oid
WHERE a.attnum > 0 AND NOT a.attisdropped
AND pg_catalog.format_type(a.atttypid, a.atttypmod) = 'text'
AND n.nspname = 'public'
AND c.relkind = 'r'
AND c.relname <> 'schema_migrations'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment