Thread: Change collate postgresql

On Tue, 2020-08-18 at 16:00 -0400, marcelo mendoza wrote:
> I have the following list of databases, but in record 3 you can see that the COLLATE has a space in the definition

I cannot see a space in the image, but it looks certainly weird.

What do you get for

SELECT d.datname,
       c.collname,
       c.oid AS coll_oid
FROM pg_database AS d
   LEFT JOIN pg_collation AS c ON d.datcollate = c.collname;

Did you directly manipulate "pg_database" at some point?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

marcelo mendoza <jmarcelo.mendoza@gmail.com> writes:
> Hi Laurenz, this is the space en_US.UTF- 8 (the space is before the 8)
> Collate           | en_US.UTF-
>                   | 8
> The database was created in this way, without error, how can I fix this?

I'm guessing that you specified LC_COLLATE that way when you created
the database.  Postgres won't complain if setlocale() doesn't, which
it very possibly wouldn't.

I don't believe we offer any DDL command to change that, so a direct
UPDATE on pg_database is the only way.  Should be pretty harmless
as long as you don't try to change the setting to a functionally
different value.

            regards, tom lane