PostgreSQL中“Money”字段的意外行为

Question

I started developing a web sistem using Linux Ubuntu and at some point I had to do the following with the data type "money":

explode(" ", "R$ 3,000.00"); // [0] => "R$" and [1] => "3,000.00"

However when I installed the software in Windows I realized that the data is saved without space, that is, "R$3,000.00". Soon, the code snippet fails to function properly.

Note: 1 could "fix" this using:

preg_replace("/[R$]+/", "$0 $1", "R$3,000.00"); // "R$ 3,000.00"

But certainly not a better way.

Note 2: The version of PostgreSQL used is 9.5

Would anyone have any suggestions for resolving this?

Thank you very much.

Answer 1

The issue you are having is that the lc_monetary locale does not have the same value on both computers. This is what you have an "Unexpected behavior" on two different operating systems.

You can change the lc_monetary locale with:

set lc_monetary to 'SOME_LOCALE';

Then test it with:

test=# SELECT 34.888::money;
 money  
--------
 $34.89
(1 row)

Read more at https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-LC-MONETARY

If your application is using different OS, it is wise to set the locale correctly at the beginning of the connection or in the configuration.

On Mac/Linux you can see available locales with locale -a. I an not sure for Windows.

If you don't generally use the currency symbol you should definitively consider to store the number as a decimal instead.