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.

SupaDupa
  • 91
  • 2
  • 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. – Alex Nov 01 '18 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. – SupaDupa Nov 02 '18 at 14:13
  • Look for the Postgresql service and see (don't change) the account it's running as. – Alex Nov 02 '18 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. – Alex Nov 02 '18 at 14:25
  • @AlexanderMP I looked at the services.msc box that comes up. I see that postgresql is logged on as local system. I checked the properties of the prgram files/Postgresql folder too. Nothing seemed out of the ordinary. Before I dive too deeply into this aspect, I should probably note that created a test .txt file and uploaded it successfully the same way I'm trying to do with the larger file in question. So if I'm able to upload a similar file type from the same folder, using the same process, could we still say it is a permission issue? Thank you again for trying to help me with this BTW. – SupaDupa Nov 02 '18 at 16:19
  • That is interesting. There's no reason why a larger file would be a problem. If it has the exact same permissions as the smaller file in the exact same directory, and doesn't have a weird name (non ASCII symbols or something) then it goes into really weird territory. Try to copy from program, not from file. Write a simple python script that reads the file and just outputs the contents. If there's anything wrong with the file - python will scream. – Alex Nov 02 '18 at 17:12
  • @AlexanderMP Thank you again. Will will try the python suggestion, I just have to learn python first...(d'oh!). I will try your other suggestion as well. – SupaDupa Nov 02 '18 at 18:56
  • Python3 is easy. `with open ("path to file", 'r') as fp: print (fp.read())`. Then you can set up another side effect of the script running (make it create another file, same way you read one, just the flash isn't 'r' but 'w'). If the script runs but Postgresql still throws the same error, then the problem is in the file contents, which I highly doubt, from the error you got. If the script doesn't run, then the problem is likely permission. Obscure problems like this are almost always permissions related. You'll need to learn to expect to encounter permissions issues every single day. – Alex Nov 02 '18 at 19:06
  • 4
    There is a bug in postgres 11 with files over 1G - see the solutions offered in https://stackoverflow.com/questions/53523051/error-could-not-stat-file-xx-csv-unknown-error – dibs487 May 28 '19 at 08:30
  • @dibs487 Thank you. I knew there was something wrong. I no longer need to do this, but will definitely keep this in mind for next time. – SupaDupa May 29 '19 at 14:58

0 Answers0