How to import a large csv file(~4GB) to PostreSQL database?

Question

Currently, I am using PostgreSQL 11.2 and I have a ~4GB .csv file. Firstly I am trying to create a temporary table and select the needed columns to fill my current table.

create temporary table t (identification varchar, a1 text, a2 text, a3 text, a4 text, a15 text, a6 text, a7 text, a8 text)                       

copy t 
from 'C:\PostgreSqlData\mydata.csv'
delimiter ',' csv

If I get some smaller portion of data(~10MB), it does not give any errors. But, when I try to import the whole file, it gives:

could not stat file "mydata.csv”: unknown error

Thanks, but I am a beginner for PostgreSql, so I don't know how to run commands from commandline and which directory I will use to open the cmd. — eaytan, Mar 30 '19 at 21:24
Try running `select version();` to see if you're running a 32-bit or 64-bit version. 4 GB is right at the 32 bit size limit. Same for your Postgres client, which is I guess pgAdmin4. — Schwern, Mar 30 '19 at 21:38
Another possibility is [your anti-virus software is interfering by trying to scan the enormous file before you're allowed to copy it](https://www.postgresql.org/message-id/4B65A6E8.1080407%40kaltenbrunner.cc). — Schwern, Mar 30 '19 at 21:43
Finally, [this may be a bug in PostgreSQL 11.0](https://github.com/MIT-LCP/mimic-code/issues/493#issuecomment-435589201). Try upgrading to 11.2 or downgrading to 10.7. — Schwern, Mar 30 '19 at 21:46
Result for select version command: PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit — eaytan, Mar 31 '19 at 13:29

score 3 · Answer 1 · edited Mar 30 '19 at 21:41

3

First check that psql is already installed.
Open your terminal
Run the psql command :

Try this way

psql -c "COPY tablename FROM 'C:\PostgreSqlData\mydata.csv' delimiter ',' csv;"

edited Mar 30 '19 at 21:41

Schwern

153,029
25
195
336

answered Mar 30 '19 at 21:28

Yassine CHABLI

3,459
2
23
43

which psql version you have ? – Yassine CHABLI Apr 01 '19 at 08:34
psql (PostgreSQL) 11.2 – eaytan Apr 01 '19 at 19:12
i found that is a bug in the PostgreSQL 11 , can you retry using version 10 .5 – Yassine CHABLI Apr 02 '19 at 08:50
it just works but not data is imported into database – Muhammad Waheed Oct 05 '20 at 08:02

How to import a large csv file(~4GB) to PostreSQL database?

1 Answers1