How to format number with “ ” as thousand separator, and “,” as decimal separator?

Question

I have a decimal number like 1234567.02

I would like to make my numbers have space as thousand separator and comma as decimal separator in PostgreSQL.

The expect result is 1 234 567,020

How can I write such a query in PostgreSQL?

Answer 1

You can use to_char to format a number like that.

Set lc_numeric appropriately for the decimal separator:

SET lc_numeric='de_DE';

Since the group separator for that locale is ., we use spaces explicitly:

SELECT to_char(1234567.02, '9 999 999D999');

    to_char    
---------------
 1 234 567.020
(1 row)