Andreas Joseph Krogh <andreas@xxxxxxxxxx> writes: > Are you saying that I don't have to re-initdb and can just change collation > somehow? If so, how? Collation is really pretty much a per-index property these days; the DB-level setting only provides a default. You could imagine some process along the lines of: 1. For each collation-sensitive index, use CREATE INDEX CONCURRENTLY to build a new index with same properties except for collation. 2. Change the DB-level setting so that ORDER BY acquires the new default interpretation (I assume you don't want to attach an explicit COLLATE to every ORDER BY for the rest of time, else you wouldn't need to do this). I think you'd have to poke pg_database.datcollate and datctype directly as a superuser for this to happen, but AFAIR there's not any underlying magic that would prevent it from working. 3. Drop all the now-useless indexes with the old collation. One fly in the ointment is that step 1 would result in indexes marked with indcollate equal to the explicitly chosen collation. There was just some discussion the other day about how the planner wouldn't recognize that this is equivalent to COLLATE "default", so after 2 you might also need a step that runs through pg_index and updates the collation OIDs to match the "default" collation. Obviously, this is untested and you'd be foolish not to try it out on a test installation before believing that it works. But I think it might, and if you are looking at a seriously painful dump+reload it'd be worth the trouble to debug a process for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
