How to copy with statement result to local in postgresql

Question

I have following with statement and copy command

with output01 as
(select * from (
select name,
case
    when column1 is not null and lower(column1) in ('point1','point2','point3','point4') then 3456
    else null end column1Desc,
case
    when column2 is not null and lower(column2) in ('point1','point2','point3','point4') then 2456
    else null end column2Desc,
column3, column4),
output02 as
(select * from (
select name,
case
    when column1 is not null and lower(column1) in ('point1','point2','point3','point4') then 3456
    else null end column1Desc,
case
    when column2 is not null and lower(column2) in ('point1','point2','point3','point4') then 2456
    else null end column2Desc,
column3, column4),
output3 as (SELECT * FROM output01 UNION ALL SELECT * FROM output02)

\copy (select * from output3) to '/usr/share/output.csv' with CSV ENCODING 'UTF-8' DELIMITER ',' HEADER;

I am getting following ERROR

ERROR: relation "tab3" does not exist

Your query is wrong.

Akhilesh Mishra
– Akhilesh Mishra

2020-10-20 06:31:19 +00:00
Commented Oct 20, 2020 at 6:31 — Akhilesh Mishra
– Akhilesh Mishra, Commented Oct 20, 2020 at 6:31

score 2 · Accepted Answer · 2020-10-20 09:38:27Z

2

All psql backslash commands need to be written on a single line, so you can't have a multi-line query together with \copy. The only workaround is to create a (temporary) view with that query, then use that in the \copy command.

Something along the lines:

create temporary view data_to_export
as
with cte as (..)
select * 
from cte
;

\copy (select * data_to_export) to ...

edited Oct 20, 2020 at 9:38

answered Oct 20, 2020 at 7:20

user330315

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

2 Comments

Kevin Jonaitis Over a year ago

You need a "()" around your full "with cte...select * from cte;" statement

user330315 Over a year ago

@KevinJonaitis: parentheses around the query of a view are unnecessary and completely useless: dbfiddle.uk/5vGI9wdh

Akhilesh Mishra · Accepted Answer · 2020-10-20 05:25:51Z

0

You are getting this error because you are running your CTE query and copy command in different statements. Considering your with query is working fine, you should write your copy statement like below:

\copy (WITH tab1 as (Your SQL statement),
tab2 as ( SELECT ... FROM tab1 WHERE your filter),
tab3 as ( SELECT ... FROM tab2 WHERE your filter)
SELECT * FROM tab3) to '/usr/share/results.csv' with CSV ENCODING 'UTF-8' DELIMITER ',' HEADER;

answered Oct 20, 2020 at 5:25

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

7 Comments

sawan Over a year ago

tried this but getting ERROR: syntax error at or near ","

sawan Over a year ago

updated my actual query format it contains many cases so with your solution, query fails on first with clause

Akhilesh Mishra Over a year ago

@swan The query you have mentioned in all 3 with is incomplete. Please have a look at it. Please first run your query directly and check whether you are getting the output or not. Once it will run correctly then append it in the () of copy. It will work definitely.

sawan Over a year ago

after executing whole query it works perfectly fine on pgAdmin Querytool but on command prompt it did not work

user330315 Over a year ago

All psql backslash commands need to be written on a single line, so you can't have a multi-line query together with \copy

|

Collectives™ on Stack Overflow

How to copy with statement result to local in postgresql

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related