1

I’m trying to upload a large, comma delimited, text file using the psql tool. The .txt file has about 20 column names already. The file is about 2GB in size and has approximately 14 million rows. A handful of the columns are mostly empty.

The .txt file looks like this (table): 

boyID, girlID, dateID, actionID

“1234”,”4321”,”Jan 12 2008”,”abc123”

I created a new table in psql, and here we’ll call it “tablename”. I gave the table slightly different column names but still in the same order as the existing column names.

I then executed the following command to try and copy the data from my text file to the new table I just created: 

COPY tablename (boyid, girlid, dateid, actionid)
FROM 'C:\filename\textfile.txt'
WITH CSV HEADER DELIMITER AS ',';

I get a message that says “cannot stat file” with “unknown error”.

I’ve tried using pgAdmin 4 as well and get the same message. I should also note I got that last part of my code from here.

P.S. I’m very new to this. (But I really enjoy it!)

Thank you all in advance.

Improve this question
10
  • Probably a permission issue. I don't have Windows, so I can't test this hypothesis. But your postgres instance is probably running under a different user than you, and doesn't have access to that file. And Postgresql can't check file size or other stats, which might trigger this error. Run services.msc, find Postgresql service and see the user it's running as, and then edit the permissions of that directory / file, to make sure that the user has permissions. Commented Nov 1, 2018 at 17:22
  • @AlexanderMP I just ran services.msc and didn't see anything that would allow me to change permissions. But I did click an option to "Allow service to interact with desktop." Didn't seem to make anything change. Also, I am running Postgres as a superuser. As far as I can tell, all privileges are enabled. Commented Nov 2, 2018 at 14:13
  • Look for the Postgresql service and see (don't change) the account it's running as. Commented Nov 2, 2018 at 14:23
  • You're only running the client as a superuser probably, which has no effect, as the client only sends requests to the server, that runs as its separate user. Commented Nov 2, 2018 at 14:25
  • 4
    There is a bug in postgres 11 with files over 1G - see the solutions offered in stackoverflow.com/questions/53523051/… Commented May 28, 2019 at 8:30
Related questions
2
Inserting large text data into postgres with python
10
How to import a huge file into PostgreSQL?
2
File size limit for importing text file into postgreSQL using phpPGAdmin
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.