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".

frau_tana
  • 71
  • 1
  • 8
  • you have to run it in `psql` - it is this specific client metacommand, not an sql one – Vao Tsun May 17 '17 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? – frau_tana May 17 '17 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 – Vao Tsun May 17 '17 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^^ – frau_tana May 17 '17 at 11:12
  • another argument to use psql, not pgadmin for you – Vao Tsun May 17 '17 at 11:15
  • I've tried `psql -c "\copy (SELECT * FROM persons) to 'C:\tmp\persons_client.csv' with csv` which yields the error FEHLER: Syntaxfehler bei »psql« (syntax error at psql) – frau_tana May 17 '17 at 11:19

3 Answers3

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.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 1
    Anything I can do to achieve my goal using pgAdmin? – frau_tana May 17 '17 at 11:24
  • https://www.question-defense.com/2010/10/15/how-to-export-from-pgadmin-export-pgadmin-data-to-csv and please ask one question at a time. please don't use comments for on line help line – Vao Tsun May 17 '17 at 11:26
  • 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... – frau_tana May 17 '17 at 11:29
  • just another reason to move to psql from pgadmin. you can sit on two cheers - pgadmin friendly to query by hand, psql easily cronned – Vao Tsun May 17 '17 at 11:34
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.

Michel Milezzi
  • 10,087
  • 3
  • 21
  • 36
0
\COPY (SELECT * FROM persons) to 'persons_client.csv' with csv;
user1859675
  • 201
  • 2
  • 5