0

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)'

Improve this question
4
  • I have also tried multiple ways to give the entire query an alias. Maybe there is a maximum number of alias allowed?
    – 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
    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
    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
    Commented Apr 21, 2020 at 14:33

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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.
    – Ludlow_Luddite
    Commented Apr 21, 2020 at 18:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.