ERROR: could not stat file "XX.csv": Unknown error

Question

I run this command:

COPY XXX FROM 'D:/XXX.csv'  WITH (FORMAT CSV, HEADER TRUE, NULL 'NULL')

In Windows 7, it successfully imports CSV files of less than 1GB.

If the file is more then 1GB big, I get an “unknown error”.

[Code: 0, SQL State: XX000]  ERROR: could not stat file "'D:/XXX.csv'  Unknown error

How can I fix this issue?

Solution 1

You can work around this by piping the file through a program. For example I just used this to copy from a 24GB file on Windows 10 and PostgreSQL 11.

copy t(c,d) from program 'cmd /c "type x:\path\to\file.txt"' with (format text);

This copies the text file file.txt into the table t, columns c and d.

The trick here is to run cmd in a single command mode, with /c and telling it to type out the file in question.

Solution 2

https://github.com/MIT-LCP/mimic-code/issues/493 alistairewj commented Nov 3, 2018 • ► edited

Okay, the could not stat file "CHARTEVENTS.csv": Unknown error is actually a bug in PostgreSQL 11. Under the hood it makes a call to fstat() to make sure the file is not a directory, and unfortunately fstat() is a 32-bit program which can't handle large files like chartevents. I tested the build on Windows with PostgreSQL 10.5 and I didn't get this error so I think it's fairly new.

The best workaround is to keep the files compressed (i.e. keep them as .csv.gz files) and use 7zip to load in the data directly from compressed files. In testing this seemed to still work. There is a pretty detailed tutorial on how to do this here: https://mimic.physionet.org/tutorials/install-mimic-locally-windows/

The brief version of above is that you keep the .csv.gz files, you add the 7zip binary to your windows environment path, and then you call the postgres_load_data_7zip.sql file to load in the data. You can use the postgres_checks.sql file after everything to make sure you loaded in all the data correctly.

edit: For your later error, where you are using this 7zip approach, I'm not sure why it's not loading. Try redownloading just the ADMISSIONS.csv.gz file and seeing if it still throws you that same error. Maybe there is a new version of 7zip which requires me to update the script or something!

Solution 3

For anyone else who googled this Postgres error message after attempting to work with a >1gb file in Postgres 11, I can confirm that @亚军吴's answer above is spot-on. It is indeed a size issue.

I tried a different approach, though, than @亚军吴's and @Loren's: I simply uninstalled Postgres 11 and installed the stable version of Postgres 10.7. (I'm on Windows 10, by the way, in case that matters.)

I re-ran the original code that had prompted the error and voila, a few minutes later I'd filled in a new table with data from a medium-ish-size csv file (~3gb). I initially tried to use CSVSplitter, per @Loren, which was working fine until I got close to running out of storage space on my machine. (Thanks, Battlefield 5.)

In my case, there isn't anything in PGSQL 11 that I was relying on that wasn't in version 10.7, so I think this could be a good solution for anyone else who runs into this problem. Thanks everyone above for contributing, especially to the OP for posting this in the first place. I cured a huge, huge headache!

Solution 4

This has been fixed in commit bed90759f in PostgreSQL v14.

The file limit for the error is actually 4 GB.

The fix was too invasive to be backported, so you can only upgrade to avoid the problem. Once the fix has had some field testing, you could lobby the pgsql-hackers mailing list to get it backported.

Solution 5

With pgAdmin and AWS, I used CSVSplitter to split into files less than 1GB. Lame, but worked. pgAdmin import appends to the existing table. (Changed escape character from ' to " in order to avoid error due to unquoted text in the source file. Typically I apply quotes in LibreOffice, but these files were too big to open.)

View more solutions

Comments

[Aren Cambre]

I recommend you switch the accepted answer to @johann-oskarsson 's answer. It's straightforward and works with the current release of Postgres.

[russellhoff]

I didn't extract the compressed GZ file and it didn't throw the error.

[Nicholas Humphrey]

Thanks. Very astonished to find that this bug is still present in 4.5. I mean surely nowadays most people want to load GB data.

[Aren Cambre]

I deleted my comment. I ran into a different problem, where for long-running queries, either Postgres or Pgamin were causing a success message to display when in fact the query ended in error.

[Aren Cambre]

@亚军吴 I recommend this be the accepted answer. It's straightforward, simple, and doesn't require downgrading Postgres.

[allenlin1992]

COPY clarifidataexport_usa_wide FROM PROGRAM 'cmd /c "type C:\temp\clarifi_usa.csv"' DELIMITER ',' CSV HEADER; What is wrong with this query? It returns COPY 0

[surabhi gupta]

I tried this method for a 4 GB file; however, it takes forever to complete.

[Luffydude]

so my file with 16gb doesnt work on the latest version.. great

[gcj]

@surabhigupta but does the query finish at some point i have been running for few hours and is still processing? \copy table from program 'cmd \c type "c:\data\data_to_load_in_postgres.csv"' with DELIMITER ',' CSV HEADER;

[Connor Willoughby]

does anyone here know the syntax for path with a space?

[Luffydude]

is there a way to do this with csvs that have a header?