2

Hallo everyone once again,
I did various searches but couldn't gind a suitable/applicable answer to the simple problem below:
On pgAdminIII (Windows 7 64-bit) I am running the following command using SQL editor: 

COPY public.Raw20120113 FROM 'D:\my\path\to\Raw CSV Data\13_01_2012.csv';

I tried many different variations for the path name and verified the path, but I keep getting:

ERROR: could not open file "D:\my\path\to\Raw CSV Data\13_01_2012.csv" for reading: No such file or directory

Any suggestions why this happens?
Thank you all in advance
Petros

UPDATE!!

After some tests I came to the following conclusion: The reason I am getting this error is that the path includes some Greek characters. So, while Windows uses codepage 1253, the console is using 727 and this whole thing is causing the confusion. So, some questions arise, you may answer them if you like or prompt me to other questions?
1) How can I permanently change the codepageof the console?
2) How can I define the codepage is SQL editor? Thank you again, and sorry if the place to post the question was inappropriate!

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Petros Apotsos
  • 615
  • 2
  • 6
  • 13
  • 2
    Does that file path exist *on the Postgres server*? Note that it is not pgAdmin III that is executing this command, it is the Postgres database itself. – IMSoP Nov 08 '13 at 12:22
  • 2
    You should realize that pgAdmin runs on the ***client*** but the `COPY` command is executed on the ***server***. This means that the path you specify in the copy command should refer to a location on the **server**, not on the machine running pgAdmin. – fvu Nov 08 '13 at 12:22
  • I fully understand the above implications! However, in my case everything runs locally and that's why it seems do strange to me!! – Petros Apotsos Nov 08 '13 at 15:14
  • In that case read http://stackoverflow.com/questions/10079682/copy-function-in-postgresql (the answer discussing `standard_conforming_strings = on`). – fvu Nov 08 '13 at 17:38

2 Answers2

2

Try DIR "D:\my\path\to\Raw CSV Data\13_01_2012.csv" from command line and see if it works - just to ensure that you got the directory, file name, extension etc correct.

Jayadevan
  • 1,306
  • 2
  • 12
  • 33
  • After some tests, I have come to the following conclusion: The path name exists, but it contains non-ASCII (Greek) characters. Could this be the problem? Any workarounds? – Petros Apotsos Nov 11 '13 at 07:20
  • Please have a look at [psql](http://www.postgresql.org/docs/9.3/static/app-psql.html) Scroll down to "Notes for Windows Users" "Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.)" – Jayadevan Nov 12 '13 at 08:44
  • It is sad that you underestimate my understanding and research skills, but I will overpass it....I have set up everything right and after some further tests I have come to the following conclusion: When I enter the path client-side (ie in the psql console) it is interpreted correctly. When I try to execute a SQL command (like COPY over psql or Query Tool using pgScript) server-side, it fails! Is there any setting for the codepage the server side uses? Thank you! – Petros Apotsos Nov 12 '13 at 10:22
  • Apologies - in the update, you asked "How can I permanently change the codepageof the console?", that was why I provided the link. You also mentioned some Greek characters in the path name. Can you try moving the file to a path without Greek characters? That way we can know for sure if it is a non-ASCII character issue. You have mentioned "When I enter the path client-side (ie in the psql console) it is interpreted correctly.". Still...worth a try? – Jayadevan Nov 13 '13 at 11:41
  • No problem and thank you once again - I have already copied the file to a path with non-Greek characters and the command works as normal! So it is clearly an issue of encoding. As a last resort I will try to programmatically translate the path from WIN1253 to UTF-8, but that will take some time, as I am not yet familiar with pgScript. Until then I will be working from a temp directory. Thank you once again! – Petros Apotsos Nov 13 '13 at 14:02
0

The problem is that COPY command runs on server so it takes the path to the file from the server's scope.

To use local file to import you need to use \COPY command. This takes local path to the file into account and loads it correctly.

michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63