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.

exact error please. It looks more ubuntu issue, but first - share exact error — Vao Tsun, Apr 12 '18 at 08:20
I have verified I get the same error for LC_COLLATE and LC_CTYPE independantly (by specifying each by itself). I noticed that the 'locale' response in 18.04 indicates the setting of C.UTF-8. So maybe this is a conflict of some type? Using C.UTF-8 in the SQL script triggers the same error 'invalid locale name'. — Nathan Smith, Apr 12 '18 at 14:11
When I do create the database (without the locale spec) and I get to the interactive psql prompt, an \echo `$LC_CTYPE` (with tick quotes) returns a blank. Not sure how sort collation is actually being handled without the spec. — Nathan Smith, Apr 12 '18 at 14:29
check for available locales?.. `locale -a` if not found, try `locale-gen en_US.UTF-8` — Vao Tsun, Apr 12 '18 at 16:03
Thanks Vao. Locale -a only showed the C. responses. The locale-gen en_US.UTF-8 did the trick. I didn't realized this was needed. So separately do I need to somehow set 18.04 to use this as the default instead of C.UTF-8 or would this not matter? I would think it would not effect the database but could this cause problems elsewhere? — Nathan Smith, Apr 12 '18 at 19:37
I'm not good with OS, but I assume yes - anything relying on right locale should fail without it — Vao Tsun, Apr 12 '18 at 19:40

score 22 · Accepted Answer · answered Apr 12 '18 at 19:40

22

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

answered Apr 12 '18 at 19:40

Vao Tsun

47,234
13
100
132

1

I found that it wasn't really working as expected after running through the output of my script. After adding the en_US.UTF-8, locale -A reports this as en_US.UTF8 (without the -). I had to update the SQL script accordingly. – Nathan Smith Apr 12 '18 at 19:57
ERROR: invalid locale name: "en_US.UTF-8" – Luis Alfredo Serrano Díaz Jul 12 '20 at 13:47
2

I found that a restart (`sudo service postgresql restart`) was necessary before it would work. – nograpes Sep 14 '21 at 15:12

score 14 · Answer 2 · answered Jul 10 '19 at 09:53

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!

score 6 · Answer 3 · edited Mar 17 '20 at 05:58

It's been performed on kubuntu and works perfectly, with some additional steps previously mentioned above, the right steps should be:

Validate the existence; if it doesn't exist, proceed to create it (will require sudo privileges)
```
locale -a  |grep -i 'es_ES.utf-8'
sudo locale-gen es_PE.UTF-8
```

Validate if the locale was created.

locale -a|grep -i es
es_ES.utf8
es_PE.utf8

After the creation of the locale proceed to restart the postgres service, otherwise u will be getting the following error :
```
ERROR:  invalid locale name: "es_ES.UTF-8"
```
Restart the postgres service
```
systemctl restart postgresql
```

Finally, database was created.

postgres=# create database db_izipay_prod with template=template0 encoding='utf8' lc_collate='es_ES.UTF-8' lc_ctype='es_ES.UTF-8' owner=postgres;
CREATE DATABASE

score 3 · Answer 4 · answered May 16 '22 at 14:01

3

For me helped the next steps:

1. sudo locale-gen "en_US.UTF-8"

2. sudo dpkg-reconfigure locales

3. sudo systemctl restart postgresql

I don't why, but locale-gen not work.

answered May 16 '22 at 14:01

Andrey Ravkov

1,268
12
21

score 1 · Answer 5 · answered Oct 16 '20 at 13:51

1

Got the same error in a docker container. Here is how I solved it non interactively.

RUN echo "en_US UTF-8 " >> /etc/locale.gen && locale-gen

answered Oct 16 '20 at 13:51

Tobias Ernst

4,214
1
32
30

score 0 · Answer 6 · answered Nov 29 '21 at 19:28

I got this error on Ubuntu server 18.04 with PostgreSQL 14, while trying to run a Django project doing the following:

# create new db with the above created user as its owner
createdb -E UTF8 --lc-collate=en_IN.UTF-8 --lc-ctype=en_IN.UTF-8 -T template0 -O <new_user_name> -U <root_user> <new_db_name>

The error I got was:

createdb: error: database creation failed: ERROR: invalid locale name: "en_IN.UTF-8"

I did following to solve it:

locale -a to see the list of locales which was missing "en_IN.UTF-8"

sudo locale-gen en_IN.UTF-8 to generate the new locale

For me, it did not work without sudo.

Finally, run sudo service postgresql restart

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

6 Answers6

Linked