Trying to create query to export data as csv

Question

I have a Postgresql table I wish to export as CSV on demand using a query, without superuser.

I tried:

COPY myapp_currencyprice to STDOUT WITH (DELIMITER ',', FORMAT CSV, HEADER) \g /tmp/prices.csv

But I get a syntax error at "\g"

So I tried:

\copy myapp_currencyprice to '/tmp/prices.csv' with (DELIMITER ',', FORMAT CSV, HEADER)

But I also get a syntax error at "" from "\copy"

Where did you run those commands? `\g` and `\copy` **only** work in `psql` as they are not [SQL commands](http://www.postgresql.org/docs/current/static/sql-commands.html) — , Aug 11 '22 at 14:17
Try doing COPY myapp_currencyprice to /tmp/prices.csv WITH (DELIMITER ',', FORMAT CSV, HEADER) — Shachar297, Aug 11 '22 at 14:24

score 0 · Answer 1 · answered Feb 21 '23 at 19:58

0

Running the command that way works only when connection is done through psql.

answered Feb 21 '23 at 19:58

user3761308

score -1 · Answer 2 · answered Aug 11 '22 at 14:28

-1

You can do the following in psql.

SELECT 1 as one, 2 as two \g /tmp/1.csv

then in psql

\! cat /tmp/1.csv

or you can

copy (SELECT 1 as one, 2 as two) to '/tmp/1.csv' with (format csv , delimiter '|');

But You can't STDOUT and filename. Because in manual(https://www.postgresql.org/docs/current/sql-copy.html):

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

the Vertical line | means: you must choose one alternative.(source: https://www.postgresql.org/docs/14/notation.html)

answered Aug 11 '22 at 14:28

jian

Thanks for the answer! I tried to do that but it still said I required superuser. – Rick Loterio Aug 11 '22 at 16:14
@RickLoterio Can you paste the exact error issue. – jian Aug 12 '22 at 04:06
You can actually, from docs: "Another way to obtain the same result as \copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike \copy, this method allows the command to span multiple lines; also, variable interpolation and backquote expansion can be used." – user3761308 Feb 21 '23 at 19:59
@user3761308 but that way, the content of copy will not be rendered in STDOUT. it will redirected to the filename. I think the post have some permission issue. ... – jian Feb 22 '23 at 04:12

2 Answers2