Unexpected behavior of the “Money” field in PostgreSQL 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] =&gt; "R$" and [1] =&gt; "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.
Quoting the documentation on monetary types on PostgreSQL:


  Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the database that was dumped.


(emphasis mine)

The interpretation of the local for lc_monetary is system-dependent, and doesn't work the same way in Ubuntu as in Windows. 

Check Best data type to store money values in MySQL, a good part of the answers are not MySQL specific, and they will give you a good understanding.

The natural tendency is to not deal with money types within databases, but use numeric types instead (and normally, with 4 decimals during calculations, not just 2), and handle the formatting at the app level. I should add also that, having a money type that doesn't store a currency doesn't make much sense to me.



With regard to locale information:

In Windows, you can actually set with very much detail how currency should be shown. The following picture shows the Windows 10 settings of my box:



They're quite different that those of my Ubuntu 16 box:



And slightly different from the Mac that hosts them all: