Is it possible to insert un-formatted data for money data type in PostgreSQL By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $.


How can I insert locale specific values?
How can I insert values with my own digit grouping and decimal grouping formats?
How can I read unformatted data?
Why is PostgreSQL's money data type different than MS SQL's money data type?
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:


PostgreSQL: Which Datatype should be used for Currency?