ERROR: invalid input syntax for type money

Question

While importing I get this error:

pg_restore: [archiver (db)] COPY failed for table "transaction_details":
ERROR:  invalid input syntax for type money: "$0.00"

restore completed, but the transaction_details table is empty. This is Heroku's PostgreSQL Dump database.

PostgreSQL version on Heroku is 9.3.15 and the same on my system

The command I used:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U root -d database 577b86e3-8d96-4410-9b36-dd0f78cfe32f

Could anyone suggest a solution?

Answer 1

The manual about the money type has advice for your case exactly:

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.

To check:

SHOW lc_monetary;

The manual on lc_monetary.

The example value in the error message ('$0.00') would work with the default setting (among others):

lc_monetary = 'C';

You can set it in postgresql.conf, reload and try again ...

This peculiar behavior is one of the reasons why the money type is unpopular.