How do I use a different locale in PostgreSQL with to_char?

Question

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?

Adrian Klaver · Accepted Answer · 2022-08-19 00:09:30Z

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

That looks like the solution. It’s a bit tricky if you want multiple versions in a single query, but I suppose that’s more a job for the client software anyway. Thanks.

Collectives™ on Stack Overflow

How do I use a different locale in PostgreSQL with to_char?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related