Change collate postgresql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Change collate postgresql

marcelo mendoza
Hello everyone, I have the following list of databases, but in record 3 you can see that the COLLATE has a space in the definition "en_US.UTF-
                  | 8" How can I fix this? Can I leave like this without problem?

Regards

List of databases

-[ RECORD 1 ]-----+----------------------

Name              | xxxxxxxxxx

Owner             | xxxxxxx

Encoding          | UTF8

Collate           | en_US.UTF-8

Ctype             | en_US.UTF-8

Access privileges | 

-[ RECORD 2 ]-----+----------------------

Name              | xxxxxxxxxx

Owner             | xxxxxxx

Encoding          | UTF8

Collate           | en_US.UTF-8

Ctype             | en_US.UTF-8

Access privileges | 

-[ RECORD 3 ]-----+----------------------

Name              | xxxxxxxx

Owner             | xxxxxxx

Encoding          | UTF8

Collate           | en_US.UTF-

                  | 8

Ctype             | en_US.UTF-8

Access privileges | 



Reply | Threaded
Open this post in threaded view
|

Re: Change collate postgresql

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: Change collate postgresql

marcelo mendoza
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? 

Regards

El mié., 19 ago. 2020 a las 1:59, Laurenz Albe (<[hidden email]>) escribió:
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
(0983) 383-752
Reply | Threaded
Open this post in threaded view
|

Re: Change collate postgresql

Tom Lane-2
marcelo mendoza <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Change collate postgresql

marcelo mendoza
In any case, could it be left like this or is the recommendation to perform the update?

Regards

El mié., 19 ago. 2020 a las 9:56, Tom Lane (<[hidden email]>) escribió:
marcelo mendoza <[hidden email]> 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


--
Marcelo Mendoza
(0983) 383-752