4

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?

1
  • 3
    Honestly, don't use the money type. It's terrible. Switch to using numeric. – Craig Ringer Dec 30 '16 at 4:58
9

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.

2
  • SHOW lc_monetary; output 'en_IN' – Akash Kumar Dec 30 '16 at 8:06
  • i see, when i tried same dump on cloud server based on USA region it works, lc_monetary is 'en_US.UTF-8' there – Akash Kumar Dec 30 '16 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.