Postgresql – Is LC_COLLATE different from COLLATE

collationpostgresql

Postgres 12 on Windows 7

This works:

CREATE DATABASE "foo"
    ENCODING 'UTF8'
    LC_COLLATE = 'en-US'
    LC_CTYPE = 'en-US'
    TEMPLATE template0

Afterwards, running SHOW LC_COLLATE with database foo selected reports:

But when running

SELECT UPPER('kedi' COLLATE "en-US")

it reports:

ERROR: collation "en-US" for encoding "UTF8" does not exist

Shouldn't both accept the same collation names?

Best Answer

LC_COLLATE refers to a name of locale from the operating system, whereas COLLATE refers to a collation that should exist in pg_catalog.pg_collation.

pg_catalog.pg_collation is originally populated in the template databases when the PostgreSQL instance is created (by initdb).

Specifically it's the SQL function pg_import_system_collations() that should do that. From the doc (emphasis mine)

pg_import_system_collations ( schema regnamespace ) → integer

Adds collations to the system catalog pg_collation based on all the locales it finds in the operating system. This is what initdb uses; see Section 23.2.2 for more details. If additional locales are installed into the operating system later on, this function can be run again to add collations for the new locales. Locales that match existing entries in pg_collation will be skipped. (But collation objects based on locales that are no longer present in the operating system are not removed by this function.) The schema parameter would typically be pg_catalog, but that is not a requirement; the collations could be installed into some other schema as well. The function returns the number of new collation objects it created.

For some reason you don't have en-US in pg_collation while it's valid from your operating system, so you seem to be in the case where the advice is to run pg_import_system_collations() manually in the databases that need it.

Related Solutions

Postgresql – Is anyone else experiencing consistency issues with postgres-xl

I have only seen the above message when I set it up and there is a firewall or incorrectly specified the GTM port.

I always recommend to first set up a simple local configuration, with 1 GTM, 1 Coordinator, and 2 Datanodes running on the same server. Once you get that working, try more complex architectures, it will be easier moving forward.

Also, please use the pgxc_ctl utility to configure, it will save you a lot of trouble and will check for port conflicts and such.

Good luck!

Postgresql – Restore PostgreSQL database with same locale settings as source database

These are the exact steps I take to import a copy of a PostgreSQL 9.5 database (exported on Linux using en_US.UTF-8 encoding) into PostgreSQL 9.3 on Windows 7 or PostgreSQL 9.5 on Windows 8.1. You need to create the database with the appropriate encoding prior to loading the SQL file, otherwise the encoding from Linux (en_US.UTF-8) will prompt Windows to use a default encoding.

REM WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details.
chcp 1252

C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "CREATE DATABASE my_db WITH TEMPLATE=template0 ENCODING='UTF-8' LC_COLLATE='american_usa' LC_CTYPE='american_usa'"

REM Grant privileges to the appropriate database user.
C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "GRANT ALL PRIVILEGES ON DATABASE my_db TO myuser"

REM Import my_db.sql. Ignore the following error (it comes from a line in the SQL file, but we have already created the database with the correct locale, so we are ok):
REM psql:my_db.sql:22: ERROR:  invalid locale name: "en_US.UTF-8"

C:\PostgreSQL\9.5\bin\psql.exe -o nul --quiet -U postgres -d postgres_password -f my_db.sql

As discussed here, the locale on Windows should be american_usa (on Windows 7). On Windows 8.1, either american_usa or en-US (not en_US as with Unix) will work.

Best Answer

Related Solutions

Postgresql – Is anyone else experiencing consistency issues with postgres-xl

Postgresql – Restore PostgreSQL database with same locale settings as source database

Related Question