2

I've struggled with other StackOverflow responses. I would like to save the output of a query to a local text file - it doesn't really matter where the text file is located as long as it is on my local machine.

Code I am using:

\COPY (
select month,count(*) as distinct_Count_month 
from
(
select UNIQUE_MEM_ID,to_char(transaction_date, 'YYYY-MM') as month
FROM yi_fourmpanel.card_panel WHERE COBRAND_ID = '10006164'
group by UNIQUE_MEM_ID,to_char(transaction_date, 'YYYY-MM')
) a
group by month) TO 'mycsv.csv' WITH CSV HEADER;

Error with this code is:

<!-- language: none -->

An error occurred when executing the SQL command:
\COPY (

ERROR: syntax error at or near "\"
  Position: 1

\COPY (
^

Execution time: 0.08s
(Statement 1 of 2 finished)

An error occurred when executing the SQL command:
select month,count(*) as distinct_Count_month 
from
(
select UNIQUE_MEM_ID,to_char(transaction_date, 'YYYY-MM') as month
FROM yi_fourmpanel.card_panel...

ERROR: syntax error at or near ")"
  Position: 260

group by month) TO 'mycsv.csv' WITH CSV HEADER
              ^

Execution time: 0.08s
(Statement 2 of 2 finished)

2 statements failed.
Script execution finished
Total script execution time: 0.16s
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
ZJAY
  • 2,517
  • 9
  • 32
  • 51
  • 2
    `\copy` (not `\COPY`) is a `psql` command, not a regular SQL statement and it will **only** work with the command line client `psql` The error message you are showing doesn't look like you are using `psql`, so `\copy` won't work there. You need to use whatever "export" feature your SQL client is offering. –  Feb 03 '16 at 23:47
  • I'm using SQL Workbench - any idea? – ZJAY Feb 04 '16 at 00:23
  • You can use WbExport for that: http://sql-workbench.net/manual/command-export.html –  Feb 04 '16 at 06:58
  • Possible duplicate of [Save PL/pgSQL output from PostgreSQL to a CSV file](https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file) – geekQ Aug 16 '17 at 13:03

1 Answers1

1

1.For server COPY remove \ and run in psql following:

COPY (
WITH data(val1, val2) AS ( VALUES
  ('v1', 'v2')
)
SELECT *
FROM data
) TO 'yourServerPath/output.csv' CSV HEADER;

cat yourServerPath/output.csv : 

val1,val2
v1,v2

2.For client COPY: 

psql -h host -U user -d database -c "\copy \
( \
  WITH data(val1, val2) AS ( VALUES \
    (1, 2) \
) \
SELECT * FROM data) TO 'yourClientPath/output.csv' CSV HEADER;"

cat yourClientPath/output.csv:

val1,val2
1,2

UPDATED

As for example provided, on your client machine in terminal you need to run following script with absolute path to your mycsv.csv :

psql -h host -U username -d db -c "\COPY ( \
select month,count(*) as distinct_Count_month \
from \
  ( \
    select UNIQUE_MEM_ID,to_char(transaction_date, 'YYYY-MM') as month \
    FROM yi_fourmpanel.card_panel WHERE COBRAND_ID = '10006164' \
    group by UNIQUE_MEM_ID,to_char(transaction_date, 'YYYY-MM') \
  ) a \
group by month) TO 'path/mycsv.csv' WITH CSV HEADER;"
Dmitry S
  • 4,990
  • 2
  • 24
  • 32
  • That creates the file on the **server**, ZJAY wants the file locally. –  Feb 03 '16 at 23:51
  • changed @a_horse_with_no_name – Dmitry S Feb 04 '16 at 00:18
  • I don't think I follow - would you mind editing my code with your changes or be a bit more explicit what I should change as part of my code. Thanks for the consideration and help. – ZJAY Feb 04 '16 at 00:28