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