- How can I insert locale specific values?
Set LC_MONETARY
accordingly. (Only affects the money
type and some monetary details in formatting output!) You can do this as permanent default for your whole DB cluster in postgresql.conf
, just for the current session with SET
or for the transaction only with SET LOCAL
. (Or in several other ways.)
Demo on a Windows machine:
BEGIN;
CREATE TEMP TABLE mdemo(id serial, m money);
SET LOCAL lc_monetary = 'German_Austria.1252';
INSERT INTO mdemo(m) VALUES ('123.45');
SET LOCAL lc_monetary = 'English_US.1252';
INSERT INTO mdemo(m) VALUES ('123.45');
TABLE mdemo;
id | m
----+------------
1 | $12,345.00
2 | $123.45
ROLLBACK; -- to clean up
As you can see the string literal '123.45' was interpreted according to the current setting of LC_MONETARY
. In Austria, the dot (.
) serves as group separator and ,
is the decimal point. While in the USA it's the other way round.
It's important to find correct locale names. The manual:
What locales are available on your system under what names depends on
what was provided by the operating system vendor and what was
installed. On most Unix systems, the command locale -a
will provide a
list of available locales. Windows uses more verbose locale names,
such as German_Germany
or Swedish_Sweden.1252
, but the principles are the same.
- How can I insert values with my own digit grouping and decimal grouping formats?
Use to_number()
to produce a numeric
value from the string literal, which can in turn be cast to money
.
Say, you have this literal: '12,454.8-'
:
2 digits, ,
as group separator, 3 digits, .
as decimal point, 1 digit, pending sign. This does not work:
SELECT money '12,454.8-';
Works:
SELECT to_number('12,454.8-', '99,999.9S')::money
Since we are reading in a general number the more general setting for LC_NUMERIC
applies for the import while the setting for LC_MONETARY
still dictates how the result is displayed! The cast from numeric
to money
is locale-agnostic (immutable). Don't get confused.
And the pattern in the 2nd parameter is tricky, too. Symbols like ,
and .
are taken literally and interpreted according to C locale (no locale), while patterns (you could use '99G999D9S'
, too) are interpreted according to the current locale.
I suggested clarifications to the manual on pgsql-docs.
- How can I read unformatted data?
You can read it, but you can hardly cast this to any numeric type without a minimum of information about its format.
I still would rather not use the data type money
. Narrow, quirky specification. Use numeric
to cover all bases. Or just integer
signifying cents, performs better. See: