Postgres 12 case-insensitive compare
You got it right. ICU collations cannot be used as database default collation (yet), but have to be used in column definitions.
This limitation is annoying and not in the nature of things. It will probably be lifted in some future version.
You can use a DO statement to change the collation of all string columns:
DO$$DECLARE v_table regclass; v_column name; v_type oid; v_typmod integer;BEGIN FOR v_table, v_column, v_type, v_typmod IN SELECT a.attrelid::regclass, a.attname, a.atttypid, a.atttypmod FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid WHERE a.atttypid IN (25, 1042, 1043) AND c.relnamespace::regnamespace::name NOT IN ('pg_catalog', 'information_schema', 'pg_toast') LOOP EXECUTE format('ALTER TABLE %s ALTER %I SET DATA TYPE %s COLLATE ci', v_table, v_column, format_type(v_type, v_typmod) ); END LOOP;END;$$;