Postgres: CSV file imports, PG's money type, and lc_monetary

Answered

I'm running Datagrip and Postgres 10.1. 

When I import certain CSV files, the importer will choke on some lines.  It's related to PG's money type.  Values >= $10 million don't come through:

68:1: ERROR: invalid input syntax for type money: "2.030745908E7"

This is a PG thing, and not a DG thing.  I know this because I got the same error when I tried to import the file outside of Datagrip, using psql, with this command:

\copy mytable from 'myfile.csv' HEADER CSV;

Googling revealed that you can solve the problem by changing the value of PG's lc_monetary variable from 'en_US.UTF-8' to 'C'.  Once I did this, it worked in psql:

set lc_monetary to 'C';

Next, I changed the value of lc_monetary in postgresql.conf, and restarted the server.  After I did that, the CSV imports work in psql without my having to change lc_monetary explicitly.  So all is good in psql.

The weird thing is that the CSV file importer still doesn't work in Datagrip.  In a console, I ran "show lc_monetary;" to check what Datagrip was seeing, and it came back with C.  But the CSV imports are still broken.  Weirdly, you can run a query in Datagrip, export the file, and have Datagrip choke on re-importing it.

I'm not very deep with DG, but I tried nuking the connection in DG and recreating it, resyncing the connection, etc.  None of those things worked. I have an easy workaround (import the file in psql), but if there's a way to do this in Datagrip, I'd love to know what it is.

 

0
1 comment

Hi! Thank you for the detailed explanation. I've created an issue in our tracker: https://youtrack.jetbrains.com/issue/DBE-6141

0

Please sign in to leave a comment.