How to save PostgreSQL output to text file

Question

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

\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. — user330315
– user330315, Commented Feb 3, 2016 at 23:47
You can use WbExport for that: sql-workbench.net/manual/command-export.html — user330315
– user330315, Commented Feb 4, 2016 at 6:58
Possible duplicate of Save PL/pgSQL output from PostgreSQL to a CSV file — geekQ
– geekQ, Commented Aug 16, 2017 at 13:03

Dzmitry Savinkou · Accepted Answer · 2016-02-04 00:37:04Z

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

That creates the file on the server, ZJAY wants the file locally.
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.

Collectives™ on Stack Overflow

How to save PostgreSQL output to text file

1 Answer 1

UPDATED

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATED

3 Comments

Linked

Related