[SOLVED] How to copy with statement result to local in postgresql

How to copy with statement result to local in postgresql

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

Solution

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