2

I want to export the data in a table of some PostgreSQL database to a csv file. Since the standard copy command does not work, I tried the following:

\copy (SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv

just as in http://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/ . The path seems to be correct; however, I get the error message 

FEHLER:  Syntaxfehler bei »\«
LINE 1: \copy [...]

which means that there is a syntax error at the "\" sign before the copy statement. Any ideas what I missed?

By the way, this is not the real problem I'm currently facing. Actually I was trying to import a csv file but unfortunately I do not seem to have sufficient privileges and when just using "COPY", the permission will be denied. so I tried to import the file using "\copy" but still get the same error message I get when trying to export using "\copy".

Improve this question
6
  • you have to run it in psql - it is this specific client metacommand, not an sql one Commented May 17, 2017 at 11:02
  • Do I have to? I'm using pgAdmin 4. If I nevertheless have to run it in psql, what exactly would I have to change? Commented May 17, 2017 at 11:07
  • `psql -c "\copy (SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv" should perfectly work, and pgadmin has its own save to csv functionality somewhere Commented May 17, 2017 at 11:09
  • The thing is that for future purposes, I will have to automatically import/export a ton of files which would be infeasible to be done manually^^ Commented May 17, 2017 at 11:12
  • another argument to use psql, not pgadmin for you Commented May 17, 2017 at 11:15

3 Answers 3

Reset to default
0

\copy (SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv would not work in pgAdmin, because \copy is an pslq metacommand:

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Image
Anything I can do to achieve my goal using pgAdmin?
Image
question-defense.com/2010/10/15/… and please ask one question at a time. please don't use comments for on line help line
Image
well, this doesn't exactly help me. I already figured out how to export tables to csv manually. but I want to do more than one at a time...
Image
just another reason to move to psql from pgadmin. you can sit on two cheers - pgadmin friendly to query by hand, psql easily cronned
0

Issue this command on PgAdmin or psql:

COPY (SELECT * FROM persons) to 'C:\\tmp\\persons_client.csv' with csv;

Don't forget to escape Windows file separator.

Improve this answer

4 Comments

Image
"error, couldn't open file 'persons_client.csv' for writing" ... :-(
Image
You must ensure that your path exists (tmp folder in your case) and is writable.
Image
@frau_tana Please see this answer.
Image
Another important point: Your server is remote? If yes, you'll cannot do COPY this way because it will attempt to access C:\\tmp\\persons_client.csv on server. In this case you must use stdout to write to local file.
0 
\COPY (SELECT * FROM persons) to 'persons_client.csv' with csv;
Improve this answer

1 Comment

Image
Please briefly explain your answer

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.