Postgres: CSV file imports, PG's money type, and lc_monetary
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.
Please sign in to leave a comment.
Hi! Thank you for the detailed explanation. I've created an issue in our tracker: https://youtrack.jetbrains.com/issue/DBE-6141