Обсуждение: BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy

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)

On Mon, Sep 28, 2020 at 01:25:08PM +0000, PG Bug reporting form wrote:
> 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

\copy is psql command, and has to be in single line.

You can see it by entering:

\copy (

and then pressing enter in psql.

Docs mention:

>> Another way to obtain the same result as \copy ... to is to use the SQL COPY
>> ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike
>> \copy, this method allows the command to span multiple lines; also, variable
>> interpolation and backquote expansion can be used.

Best regards,

depesz