Re: Default collation changes leave indexes in invalid state

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?

Right now (after your response) I’d just document in our private documentation that we’d recreate indexes with fixed collation set and only then change collation defaults if we are required to in the future.

The reason I would document this privately is because I can’t seem to find any information on it on the official documentation, only hits on mailing lists or random forums.

I’m glad to help make this more clear for future users, if at all possible.

Kind Regards
Vincent Van Driessche

On 11 September 2019 at 15:39:32, Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:

Vincent Van Driessche <vincent(at)dabble(dot)be> writes:
> ... By running it before and after changing the `C` collation into `en_US.UTF-8`:
> UPDATE pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

That is not, and I do not think ever will be, a supported operation.
If it breaks your database it's your own fault.

regards, tom lane

Sent via Migadu.com, world's easiest email hosting