"\pset numericlocale" is not picking the set lc_numeric in postgres

user_9238822 0
Viewed 113

the lc_numeric set by default is

postgres=# show lc_numeric;
         lc_numeric
----------------------------
 English_United States.1252
(1 row)

When we do \pset numericlocale. the output is coming as

postgres=# select 10000000.00;
   ?column?
---------------
 10,000,000.00
(1 row)

However if we manually change the lc_numeric to any other which should result is '.' as group separator and ',' as decimal separator. It is still returning as below,

postgres=# show lc_numeric;
 lc_numeric
------------
 de_DE
(1 row)


postgres=# \pset numericlocale
Locale-adjusted numeric output is on.
postgres=# select 10000000.00
postgres-# ;
   ?column?
---------------
 10,000,000.00
(1 row)

where as if I use to_char function the output is as below,

postgres=# select to_char(1000000.00,'999G999G999D99');
     to_char
-----------------
    1.000.000,00
(1 row)

We cannot use to_char function as there are many numeric columns which could be having any number of digits. So we want a common setting that would work for all numeric columns at once.

Why is the \pset numericlocale not picking the set lc_numeric to consider '.' as group separator and ',' as decimal separator??

postgresql
1 Answers

\pset numericlocale specifies how the client psql formats numbers as strings, while lc_numeric determines how the server does it.

So lc_numeric influences the result of

SELECT to_char(10000000, '999G999G999D99');

     to_char     
═════════════════
   10.000.000,00
(1 row)

(where the server does the conversion), while numericlocale influences the display of

SELECT 10000000;
user_64643080
Related