1

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:

screenshot

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?

Improve this question
5
  • I don't have a Windows system - do you have that name in pg_collation? – Laurenz Albe Feb 19 at 12:02
  • @LaurenzAlbe Oh, that's where I find available collations? It's different from finding available locales. 💡 I have (in pg_collation.collname) default, C, POSIX and then a whole bunch of language specific ones, for example en-US-u-va-posix-x-icu and en-US-x-icu. – AndreKR Feb 19 at 12:29
  • Indeed for example UPPER('kedi' COLLATE "tr-TR-x-icu") works and yields the expected result (uppercasing using Turkish rules). So, how does Postgres choose a collation (en-US-u-va-posix-x-icu or en-US-x-icu) if I set LC_COLLATE to e.g. en-US? – AndreKR Feb 19 at 12:40
  • It will use neither of these. By default, it always uses a collation from the C library. There are probably some collations named English or similar in pg_collation. – Laurenz Albe Feb 19 at 13:34
  • @LaurenzAlbe SELECT array_agg(collname) FROM pg_collation WHERE collname NOT LIKE '%-x-icu' -> {default,C,POSIX,ucs_basic} – AndreKR Feb 19 at 14:07
2

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.

Improve this answer
3
  • If the thing in LC_COLLATE is not actually a collation, then which collation is used by default when I for example runLOWER() ? – AndreKR Feb 19 at 19:12
  • I ran pg_import_system_collations() and it returned 0. The contents of pg_collation is unchanged. – AndreKR Feb 19 at 19:12
  • @AndreKR: it implies that when PG asks Windows 7 the list of its locales, it doesn't list en-US, but when PG ask to use en-US as the locale, it seems to say "no problem". So a large part of the explanation is in Windows 7 (which has been EOL'ed last year BTW). – Daniel Vérité Feb 22 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.