How do I use a different locale in PostgreSQL with to

I know I can use use `to_char() to format currency in my default locale:

SELECT to_char(12345.67,'FML999G999G999D00');

In Australia, I get $12,345.67.

In Microsoft SQL, the format() function has an optional additional parameter for what they call the culture (together with a convenient 'c' format code for currency), so I can change the currency format.

Is there a way of changing the locale for a currency format?

CodePudding user response：

The only way I know is to change lc_monetary:

lc_monetary (string)

Sets the locale to use for formatting monetary amounts, for example with the to_char family of functions. Acceptable values are system-dependent; see Section 24.1 for more information. If this variable is set to the empty string (which is the default) then the value is inherited from the execution environment of the server in a system-dependent way.

show lc_monetary ;
 lc_monetary 
-------------
 en_US.UTF-8

SELECT to_char(12345.67,'FML999G999G999D00');
 to_char   
------------
 $12,345.67

set lc_monetary = 'en_GB.UTF-8';
SELECT to_char(12345.67,'FML999G999G999D00');
 to_char   
------------
 £12,345.67