PostgreSQL Export COMPOUND Query Output to CSV

Question

I have a fully functioning compound PostgreSQL query...

SELECT * FROM 
(SELECT date_trunc('day', date) date, (array_agg(open ORDER BY time 
ASC))[1] o, MAX(high) h, MIN(low) l, (array_agg(close ORDER BY time 
DESC))[1] c
FROM 
(SELECT * FROM ES_test WHERE time BETWEEN 
(SELECT pit_open FROM "contracts" WHERE root_symbol = '@ES' LIMIT 1) 
AND 
(SELECT pit_close FROM "contracts" WHERE root_symbol = '@ES' LIMIT 1)) 
AS pit
GROUP BY date_trunc('day', date) ORDER BY date) AS pit_daily INNER 
JOIN trading_dates ON pit_daily.date = trading_dates.date;

I want to export the output of this query to a csv. I have tried the COPY method...

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

and the \copy method...

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

without any success. When I try these I get multiple errors including parse errors or gets tangled up after one of the "AS" within the query or right after the "TO" that quotes the location to create the csv file. I get similar errors when I try to create a temp table or temp view. Any suggestions? This is the version...
'psql (PostgreSQL) 12.2 (Ubuntu 12.2-2.pgdg18.04+1)'

I have also tried multiple ways to give the entire query an alias. Maybe there is a maximum number of alias allowed? — Ludlow_Luddite
– Ludlow_Luddite, Commented Apr 20, 2020 at 21:21
Works for me. Please show us one thing you actually tried and what error you got. Please format it correctly (for example, indent it 4 spaces) so we don't have to try to guess which quote marks are part of the command which are part of the formatting. — jjanes
– jjanes, Commented Apr 20, 2020 at 23:13
Thanks for your reply jjane. Could you show what code you used to make this work? — Ludlow_Luddite
– Ludlow_Luddite, Commented Apr 21, 2020 at 14:29
COPY (SELECT * FROM ([ CODE FROM ABOVE ]) TO ‘/home/alpha/Desktop/ES_System_data.csv’ CSV header; ERROR: syntax error at or near "‘" LINE 7: ..._dates ON pit_daily.date = trading_dates.date) TO ‘/home/alp... — Ludlow_Luddite
– Ludlow_Luddite, Commented Apr 21, 2020 at 14:33

jjanes · Accepted Answer · 2020-04-21 19:07:08Z

1

Based on the ERROR message and comment, it looks like you have a so-called "smart" quote mark, or maybe a backtick/grave, before your file name. You need it to be a straight ASCII apostrophe/single quote mark.

Whatever you are using to edit your queries seems to be too clever by half. But it does seem like it would be screwing up the rest of your quote marks as well, not just these ones.

edited Apr 21, 2020 at 19:07

answered Apr 21, 2020 at 15:42

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Comment

Ludlow_Luddite Over a year ago

Thanks jjanes. Indeed the text editing created an unreadable quote mark. I edited the statement on a different editor and got the result I was looking for.

Collectives™ on Stack Overflow

PostgreSQL Export COMPOUND Query Output to CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related