php - How does the locale setting LC_NUMERIC work in PostgreSQL?

Question

Could anybody give an insight on the locale and numeric types behaviour in PostgreSQL? We work with Italian locale. That is comma separation for decimal part. Setting in postgresql.conf

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'it_IT.UTF-8'                     # locale for system error message
                                                # strings
lc_monetary = 'it_IT.UTF-8'                     # locale for monetary formatting
lc_numeric = 'it_IT.UTF-8'                      # locale for number formatting
lc_time = 'it_IT.UTF-8'                         # locale for time formatting

.. does nothing! It behaves in a quite appropriate way with dates and so, BUT the numeric type remains DOT separated for decimal part.

root@server:~# uname -a
Linux server 2.6.32-36-generic-pae #79-Ubuntu SMP Tue Nov 8 23:25:26 UTC 2011 i686 GNU/Linux

root@server:~# dpkg -l | grep postgresql
ii  postgresql-8.4      8.4.9-0ubuntu0.10.04   object-relational SQL database, version 8.4 
ii  postgresql-client   8.4.9-0ubuntu0.10.04   front-end programs for PostgreSQL (supported)

EDIT

Having problem with implementation of locale in different scopes: db, server script, os and client side. Decided to avoid any locale formatting and use en_EN locale. The locale formatting will be applied only at the moment of output and so.

Answer 1

I quote the manual:

lc_numeric (string)

Sets the locale to use for formatting numbers, for example with the to_char family of functions.

Concerns these type formatting functions. You should be able to reproduce the following demo:

SHOW lc_numeric;

de_AT.UTF-8

SELECT to_number('13,4','999D99')

13.4

SELECT to_char(13.4,'FM999D99')

13,4

SET lc_numeric = 'C';
SELECT to_number('13,4','999D99')

134

SELECT to_char(13.4,'FM999D99')

13.4

RESET lc_numeric;

Template patterns in the manual.

The format of numbers in SQL expressions does not change with locale settings. That would be madness.

---

On a different note: you are aware that you have to (at least) reload the server after changing postgresql.conf.

pg_ctl reload