Failing to create database in Postgres 9.3 with Norwegian (Bokmål) locale

Question

I am trying to create a database using this command:

CREATE DATABASE workgroup WITH TEMPLATE = template0 
ENCODING = 'UTF8' 
LC_COLLATE = 'Norwegian (Bokmål)_Norway.1252' 
LC_CTYPE = 'Norwegian (Bokmål)_Norway.1252';

But it fails with this error:

"ERROR:  invalid locale name: "Norwegian (Bokmål)_Norway.1252"

********** Error **********

ERROR: invalid locale name: "Norwegian (Bokmål)_Norway.1252" SQL state: 42809"

I added Norwegian (Bokmål) keyboard on Windows 7, and also failed with standard 'a' (Bokmal) and without the space.

Creating the DB with this locale:

LC_COLLATE='Estonian_Estonia.1257' 
LC_CTYPE='Estonian_Estonia.1257'

works fine.

I've installed Windows 9.3 Postgres with the Norwegian Bokmal locale, but then queried the database for the locales using the SQL commands:

show LC_COLLATE;
show LC_CTYPE;

SELECT *
FROM   pg_settings
WHERE  name ~~ 'lc%';

It returns empty data for LC_COLLATE and LC_CTYPE.

What should be the LC* values for the Norwegian (Bokmål) locale?

Answer 1

I had a similar problem trying a query that worked on windows and didn't work on linux:

CREATE DATABASE testingDB
WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'Portuguese_Brazil.1252'
   LC_CTYPE = 'Portuguese_Brazil.1252'
   CONNECTION LIMIT = -1;

When I changed

'Portuguese_Brazil.1252'

to

'pt_BR.UTF8'

I then got another problem:

new collation (pt_BR.UTF8) is incompatible with the collation of the template database (en_US.UTF-8)

which is self explained, my system and database uses another locale, so I fixed it.

you can find more about your postgres version collation here

I believe this should fix your issue:

CREATE DATABASE workgroup WITH TEMPLATE = template0 
ENCODING = 'UTF8' 
LC_COLLATE = 'nb_NO.UTF8' 
LC_CTYPE = 'nb_NO.UTF8';

Answer 2

Try issuing \dOs+ in psql, it will show a list of available locales.