1

I have successfully installed Postgres 10 in a Windows Server 2008 R2 standard, 64 bit.

I am trying to create a new database that has LC_COLLATE = 'und-x-icu' and LC_CTYPE = 'und-x-icu' with the following SQL 

CREATE DATABASE hey
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'und-x-icu'
    LC_CTYPE = 'und-x-icu'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    TEMPLATE = template0
    ;

I get ERROR: invalid locale name: "und-x-icu" SQL state: 42809.

But the SELECT * FROM pg_collation; clearly shows und-x-icu.

The same SQL works on my laptop (windows 10).

I did select locale : C while installing on the server, I did not remember what I selected as a locale while installing on the laptop.

How can I make this work on win server 2008 and get und-x-icu?

codebot
  • 517
  • 8
  • 29
  • 55

1 Answers1

1

The documentation does not seem to mention that restriction, but you cannot use ICU collations in CREATE DATABASE.

This may be improved in the future, but for now there is no way to have an ICU collation as the default collation.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • On my laptop, I can set `LC_COLLATE` or `LC_CTYPE` as `'und-x-icu'` in the `CREATE DATABASE` , if I also set `TEMPLATE=template0`. But I can also see `'und-x-icu'` as a option in the Create > Database UI menu of pgAdmin4. Those options are not available in the win server 2008. So maybe its a win server OS thing? Should I check its available collations? Or somehow install ICU in server's Postgres ? But I think its already there. Thanks – codebot Jan 29 '19 at 10:58
  • That is scary. You definitely should get the same error message on your laptop. Something must be wrong there. – Laurenz Albe Jan 29 '19 at 11:30
  • "scary" is a ...scary word. So, what could be wrong in my laptop setup? I dont remember doing anything special for enabling ICU. I just played around with `CREATE DATABASE` .If you see my comments in the answer [here](https://stackoverflow.com/questions/48044953/install-utf8-collation-in-postgresql) the SQL with `'und-x-icu'` just worked and the DB I am working on for months, works fine. Anyhow, what to chose instead of `und-x-icu` as collate and c type? Just C will be fine? – codebot Jan 29 '19 at 12:39
  • With "scary" I mean that this is maybe a PostgreSQL bug. What is the precise version of PostgreSQL on your laptop? If you create a new cluster with `initdb` on your laptop and try again to create a database with an ICU collation, does it work? – Laurenz Albe Jan 29 '19 at 12:48
  • I am sorry ,but I dont know how or where to run the `initdb` . cmd? psql? I was experimenting without success. But I think its not a bug, since win 10 support ICU and in my `C:\Program Files\PostgreSQL\10\bin` I have the ICU .`dll` files. Win server has the same .`dll` files. Still nothing. I re-installed the whole Postgres on the server, chosing `locale : [Default locale]` this time and I still have C, POSIX and English 1252 as collate options – codebot Jan 29 '19 at 15:17
  • `initdb` is run in `cmd.exe`. If you never have created a cluster, you'd have to read some documentation first. I am not certain that it is a bug (I have no Windows machine at hand, so I cannot test), but to the best of my knowledge the error message is the correct behavior. – Laurenz Albe Jan 29 '19 at 15:43