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

Answer 1

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 ...

Answer 2

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;