The following bug has been logged on the website:
Bug reference: 16639
Logged by: Hans Buschmann
Email address: buschmann@nidsa.net
PostgreSQL version: 13.0
Operating system: Linux x64 or Windows 64bit
Description:
Deriving from a much longer query in our application I managed to extract a
short query which shows the error for $subject:
\copy (
with
qcust (ctry_name,cli_nombre) as (
values
('DE','CUS1')
,('DE','CUS2')
,('DE','CUS3')
,('FR','CUS6')
,('FR','CUS8')
)
-- select * from qcust;
,qctry (ctry_name,cli_nombre) as (
values
('DE','zzz SUMM')
,('FR','zzz SUMM')
)
-- select * from qctry;
select * from qcust
union all
select * from qctry
order by ctry_name,cli_nombre
) to stdout with (format csv,delimiter ';', header 1)
;
This gives the error in pgsql:
ERROR: syntax error at or near ")"
LINE 21: ) to stdout with (format csv,delimiter ';', header 1)
^
when executing the SQL copy command (same as above without \ ) the query
succeeds:
db=# copy (
db(# with
db(# qcust (ctry_name,cli_nombre) as (
db(# values
db(# ('DE','CUS1')
db(# ,('DE','CUS2')
db(# ,('DE','CUS3')
db(# ,('FR','CUS6')
db(# ,('FR','CUS8')
db(# )
db(# -- select * from qcust;
db(# ,qctry (ctry_name,cli_nombre) as (
db(# values
db(# ('DE','zzz SUMM')
db(# ,('FR','zzz SUMM')
db(# )
db(# -- select * from qctry;
db(# select * from qcust
db(# union all
db(# select * from qctry
db(# order by ctry_name,cli_nombre
db(# ) to stdout with (format csv,delimiter ';', header 1)
db-# ;
ctry_name;cli_nombre
DE;CUS1
DE;CUS2
DE;CUS3
DE;zzz SUMM
FR;CUS6
FR;CUS8
FR;zzz SUMM
This occured first on psql (v13.0, Win64) connected to server (v13.0,
Linux64).
But it is also present on Linux64 only and also on PG 12.4 Win64 (other
versions not tested).
The error seems to be independant of platform and version.
Environment (official releases of PG13):
db=# select version ();
version
------------------------------------------------------------
PostgreSQL 12.4, compiled by Visual C++ build 1914, 64-bit
(1 row)
db=# \set
...
VERSION = 'PostgreSQL 13.0, compiled by Visual C++ build 1914, 64-bit'
VERSION_NAME = '13.0'
and
db=# select version ();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1
20200723 (Red Hat 10.2.1-1), 64-bit
(1 Zeile)