如何在postgresql中将语句结果复制到local

Question

我有下面的语句和复制命令

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;

我收到以下错误

错误：关系"表3"；不存在

Answer 1

所有psql反斜杠命令都需要写在一行上，因此不能将多行查询与copy一起使用。唯一的解决方法是使用该查询创建一个(临时(视图，然后在copy命令中使用该视图。

大致如下：

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

Answer 2

出现此错误是因为您在不同的语句中运行CTE查询和copy命令。考虑到您的with查询运行良好，您应该编写如下copy语句：

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;