PostgreSQL 10 on Linux - LC_COLLATE locale en_US.utf-8 not valid

Question

ERROR: invalid locale name: "en_US.utf-8"

Running Ubuntu server 18.04 Beta 2 with PostgreSQL 10.

In running a database creation script that worked on 9.5, I am now seeing an issue with 'en_US.UTF-8' as a locale:

CREATE DATABASE db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

I know this may be redundant as I understand the default to be 'en_US.etf-8'. Removing the LC_COLLATE and LC_CTYPE parameters let me run my script.

So did the locale definitions change somehow for V 10? Or is there something else now happening? I couldn't find anything on this in the Postgres 10 manual.

Answer 1

check for available locales with locale -a if not found, try manually adding it with:

locale-gen en_US.UTF-8 

after this

CREATE DATABASE db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

should work

Answer 2

I had the same error, I generated the new locale:

locale-gen en_US.UTF-8 

I verified with locale -a that it was in fact present, then I logged out just to make sure, but I was still getting the same error on the database creation.

Finally, I solved the error by simply restarting the postgresql server with:

sudo systemctl restart postgresql

After that, the database creation command was working!