Thread: BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy
BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy
From
PG Bug reporting form
Date:
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)
Re: BUG #16639: Syntax error in pgsql when executing local \copy instead of SQL copy
From
hubert depesz lubaczewski
Date:
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