How to create a database with UTF-8 collation in PostgreSQL on Windows?

Question

I'm configuring PostgreSQL db for the Bitbucket Server on Windows. In the official guide it says that:

The database must be configured to use the UTF-8 character set.

It doesn't strictly say that you have to set collation to UTF-8, but for other atlassian procucts it's recommended so I assume that's the same case for Bitbucket Server. Exmaple from Confluence documentation:

Character encoding must be set to utf8 encoding.

Collation must also be set to utf8. Other collations, such as "C", are known to cause issues with Confluence.

This is what I have now, the problem is that it sets the collation to English_United States.1252:

CREATE DATABASE test
WITH OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'american_usa'
LC_CTYPE = 'american_usa'
TEMPLATE template0;

Is setting collation to UTF-8 actually necessary and if yes, how can I do it?

benvc · Accepted Answer · 2022-03-01 17:26:47Z

27

Assuming that you are trying to create a PostgreSQL database with US locale sort order and character classification with UTF-8 encoding on Windows, following is a modification to the code example posted in the original question that may be used to achieve that result.

CREATE DATABASE "example_db"
WITH OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en-US'
LC_CTYPE = 'en-US'
TEMPLATE template0;

One liner format for terminal copy / paste:

CREATE DATABASE "example_db" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en-US' LC_CTYPE = 'en-US' TEMPLATE template0;

For anyone trying to create a similar database in a Linux environment such as Ubuntu on Windows Subsystem for Linux, you can do the following (depending on the specific environment, you may need to use 'en_US.UTF8' as the locale instead):

CREATE DATABASE "example_db"
WITH OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

One liner format for terminal copy / paste:

CREATE DATABASE "example_db" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;

edited Mar 1, 2022 at 17:26

answered Aug 29, 2018 at 0:06

benvc

15.3k4 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

spankmaster79 Over a year ago

en_US.UTF-8 does not exist on Windows as locale and then results in an error.

benvc Over a year ago

You are right @spankmaster79, see edit for creating the db on Windows without Windows Subsystem for Linux.

Zafer Over a year ago

For Turkish locale: CREATE DATABASE "example_db" WITH OWNER=postgres ENCODING 'UTF-8' LC_COLLATE 'tr_TR.UTF-8' LC_CTYPE 'tr_TR.UTF-8' TEMPLATE template0;

Laurenz Albe · Accepted Answer · 2018-07-16 05:58:10Z

12

There is no UTF8 collation. UTF8 is a way to encode characters as numbers, a so-called encoding. Collations define how characters (and composites) are ordered.

While you have to pick a collation that matches the database encoding with PostgreSQL on UNIX, that is not required on Windows. Maybe the documentation you are reading is targeted at UNIX.

You should ask the people who wrote the software to tell you what collation to use.

answered Jul 16, 2018 at 5:58

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Collectives™ on Stack Overflow

How to create a database with UTF-8 collation in PostgreSQL on Windows?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related