CODEWITHSUNDEEP
sqlpostgresqlpsqlpostgresql-copy
ZJAY
ZJAY

Reputation: 2837

How to save PostgreSQL output to text file

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

Upvotes: 2

Views: 9031

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

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

Upvotes: 1

Related Questions