1

I tried to import several .csv Files (8GB, 56GB and 11GB) and all of them failed with unknown error. 

CREATE TABLE name(
    COMMIT_ID int,
    PARENT_ID int
);

COPY  FROM 'C:/Users/Public/name.csv' DELIMITER ',' NULL AS '\N' ESCAPE AS '\' CSV;

I tried over pgAdmin4 and cmd.

I use Windows 10 and PostgreSQL12

All other files that are from the same database and they are all <1GB. This files I can import without any issue

Shorty20
  • 11
  • 5
  • Can the server read that file in that directory? Have you tried using \COPY in psql instead? Why is the error unknown? What do the logs say? – Jeremy Jun 03 '20 at 14:13
  • Does this answer your question? [ERROR: could not stat file "XX.csv": Unknown error](https://stackoverflow.com/questions/53523051/error-could-not-stat-file-xx-csv-unknown-error) – JGH Jun 03 '20 at 15:39
  • I tried it like this `COPY project_commits(project_id,commit_id) FROM program 'cmd /c "type c:/Users/Public/project_commits.csv"' with (DELIMITER ',' NULL AS '\N' ESCAPE AS '\' format CSV);` and I also tried to use this command `COPY project_commits(project_id,commit_id) FROM program 'cmd /c "type c:/Users/Public/project_commits.csv"' DELIMITER ',' NULL AS '\N' ESCAPE AS '\' with (format CSV);` both are not working – Shorty20 Jun 04 '20 at 08:46
  • Just for the record I also tried it like this `COPY project_commits(project_id,commit_id) FROM program 'cmd /c "type c:/Users/Public/project_commits.csv"' with (format CSV);` – Shorty20 Jun 04 '20 at 08:51
  • You are mixing old and new syntax for the [`with`](https://www.postgresql.org/docs/current/sql-copy.html) options. Try `COPY project_commits(project_id,commit_id) FROM program 'cmd /c "type c:/Users/Public/project_commits.csv"' DELIMITER ',' NULL AS '\N' CSV ESCAPE AS '\';` It is using the old syntax as in your question. Note the `with` is optional and its options are **not** in parentheses. Moreover, there is no comma between options (while the new syntax requires it) – JGH Jun 04 '20 at 11:35
  • Thanks for the answer that might fix the problem. I cannot check it anymore since I downgraded my postgresql to 9.6.18. – Shorty20 Jun 04 '20 at 18:50

2 Answers2

0

This might not answer the question directly but what I did is to downgrade my postgresql.

I use 9.6.18 now and just used the normal command

COPY  FROM 'C:/Users/Public/name.csv' DELIMITER ',' NULL AS '\N' ESCAPE AS '\' CSV;

I know this is not the perfect solution. It just seems like it is an issue from postgresql and not an user issue

Shorty20
  • 11
  • 5
0

I had a similar problem.

  • PostgreSQL 9.4.24 (Greenplum Database 6.8.1
  • psql (PostgreSQL) 9.4.24

I just split the csv file smaller to avoid the problem.

## for pg_dump output.sql
cat copy_inline_csv.sql | awk '
 s {x+=1; print; if(x%1000==0)print "\\.\n\n" s} 
 !s && /^COPY / {s=$0; print s;}
 $0=="\\." {s=""}' | psql


// I'll try another version and feedback.

yurenchen
  • 1,897
  • 19
  • 17