Re: Default collation changes leave indexes in invalid state

On 2019-Sep-11, Vincent Van Driessche wrote:

> If I understand it correctly you are saying: “If you run queries like this, you’d better know what you are doing”?
> Is there a more supported way to change the default collation on PostgreSQL?

I don't think there's a supported way to change the default collation on
an existing database.  You would have to dump and restore in order to
achieve that cleanly.  That would be my advice.

Part of the reason that there isn't a way to do this is that it isn't a
trivial change such as a single catalog update.  If you want to use this
hack, fine -- it's your database.  However, as you noticed, you will
also have to reindex many indexes (which? it's up to you to find out),
and there likely are other changes that you need to do.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services