How to Change the Locale When Formatting Numbers in PostgreSQL

Posted on by Ian

If you’re formatting a number in PostgreSQL, and you want to change the locale so that the correct group and decimal separators are used, here’s how you can do that.

There are two system variables of interest:

lc_monetarySets the locale to use for formatting monetary amounts.
lc_numericSets the locale to use for formatting numbers.

These can be set with the SET statement.

For example:

SET lc_numeric = 'en_US';

Result:

SET

Having set that, functions like TO_CHAR() will format numbers according to the en_US format.

Examples

Here are some examples that demonstrate how these variable can affect the output:

The lc_monetary Variable

Here’s an example of setting the lc_monetary variable:

SET lc_monetary = 'en_US';
SELECT CAST(123456.789 AS money);

Result:

$123,456.79

And here it is using a different locale:

SET lc_monetary = 'de_DE';
SELECT CAST(123456.789 AS money);

Result:

Eu123.456,79

The lc_numeric Variable

Here’s an example of setting the lc_numeric variable, then formatting a number with the TO_CHAR() function:

SET lc_numeric = 'en_US';
SELECT TO_CHAR(123456.789, 'fm999G999D99');

Result:

123,456.79

And here it is using a different locale:

SET lc_numeric = 'de_DE';
SELECT TO_CHAR(123456.789, 'fm999G999D99');

Result:

123.456,79

Check the Current Settings

You can check your current settings with the SHOW command.

Example:

SHOW lc_monetary;

Result:

en_US

And:

SHOW lc_numeric;

Result:

de_DE

Reset Back to the Default Values

You can use the following to set each variable back to its default setting:

SET lc_monetary TO DEFAULT;
SHOW lc_monetary;

Result:

en_US.UTF-8

And for lc_numeric:

SET lc_numeric TO DEFAULT;
SHOW lc_numeric;

Result:

en_US.UTF-8

The locales available on your system and their names depend on what was provided by the operating system vendor and what was installed. See PostgreSQL’s documentation about locale support for more information.