BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: buschmann(at)nidsa(dot)net
Subject: BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy
Date: 2020-09-28 13:25:08
Message-ID: 16639-ec6ab8b4776f64b2@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16639
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message hubert depesz lubaczewski 2020-09-28 13:46:10 Re: BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy
Previous Message David Rowley 2020-09-28 07:12:31 Re: BUG #16627: union all with partioned table yields random aggregate results