Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
check collation for all public text columns postgres
SELECT
n.nspname as "Schema",
c.relname as "Name",
a.attname,
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