How can I get a list of locales in PostgreSQL?

Question

I want to change number and currency format using something like:

SET lc_numeric='en_AU.UTF-8'
SET lc_money='en_AU.UTF-8'

I tried a number of formats for, say, Dutch, but nothing works. It appears that I don’t have that locale on my operating system.

Ho can I get a list of available locales in PostgreSQL? There’s nothing I can find online.

Erwin Brandstetter · Accepted Answer · 2023-06-01T02:02:37.100

Postgres uses locales provided by the underlying OS. The manual:

PostgreSQL uses the standard ISO C and POSIX locale facilities provided by the server operating system.

To see all available locales, fire up a shell and run:

locale -a

Of course, you can install more ...

The manual again:

What locales are available on your system under what names depends on what was provided by the operating system vendor and what was installed. On most Unix systems, the command locale -a will provide a list of available locales. Windows uses more verbose locale names, such as German_Germany or Swedish_Sweden.1252, but the principles are the same.

Traditionally, libc has been the "locale provider". Since version 10 Postgres also supports ICU locales. For limited purposes at first, and Postgres needed to be built with the --with-icu flag to support it.
Since Postgres 15 you can use ICU collations per database.
And Postgres 16 (currently beta) has ICU support per default. There is a chapter on ICU Locales in the manual since Postgres 16, accordingly.

Related blog posts by Peter Eisentraut (core developer responsible for much of this):

score 4 · Answer 2 · answered Jun 01 '23 at 06:36

PostgreSQL uses locales from either the C library or the ICU library. If you want to use C library locales, as your question suggests, you can look at

SELECT collname FROM pg_collation WHERE collprovider = 'c';

for the available collations. If the one you are looking for is missing, you'll have to create it on the operating system:

for Redhat-based Linux distributions, install the glibc-all-langpacks package
for Debian-based Linux distributions, generate the locale with
```
locale-gen en_AU.utf8
```

Once you have the locale on the operating system, restart the PostgreSQL server and import the new locales into the system catalog with

SELECT pg_import_system_collations('pg_catalog');

I recommend using ICU collations, particularly from v15 on, where you can use ICU as default locale provider.

How can I get a list of locales in PostgreSQL?

2 Answers2