Is it possible to insert un-formatted data for money data type in PostgreSQL

Question

By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $.

1. How can I insert locale specific values?
2. How can I insert values with my own digit grouping and decimal grouping formats?
3. How can I read unformatted data?
4. Why is PostgreSQL's money data type different than MS SQL's money data type?

Answer 1

1. 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.

---

2. 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.

---

3. 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:

• PostgreSQL: Which Datatype should be used for Currency?

Answer 2

I would strongly recommend to not use the money data type.

All your problems derive from the limits of the money data type.

Using numeric() is a much better alternative. If you need to store values for different currencies it's the only choice you have anyway (you will need another column that stores the currency of that value).

---

To answer your questions:

How can I insert locale specific values?

You can't. The formatting (at least in psql) is always done based on the locale of the client.

How can I insert values with my own digit grouping and decimal grouping formats?

You can't. Numbers (including numbers in a money column) are always stored without any formatting. The formatting is done based on the client's locale. If you need that use numeric and apply the formatting in your application or us to_char()

why PostgreSQL's money datatype is different with MS-SQL money datatype?

Because there is no standard for a "money" data type.

How can I read un-formatted data?

I don't think this is easily possible, as the formatting is done by the libpq library. There might be low level API calls to do that, but definitely not through SQL. Again using numeric is the better choice if you need that.