variable filename for psql \copy
Hi all,
I have found maybe buggy behaviour (of psql parser?) when using psql \copy
with psql variable used for filename.
SQL copy is working fine:
contrib_regression=# \set afile '/writable_dir/out.csv'
contrib_regression=# select :'afile' as filename;
filename
-----------------------
/writable_dir/out.csv
(1 row)
contrib_regression=# copy (select 1) to :'afile';
COPY 1
but psql \copy is returning error:
contrib_regression=# \copy (select 1) to :'afile';
ERROR: syntax error at or near "'afile'"
LINE 1: COPY ( select 1 ) TO STDOUT 'afile';
^
when used without quotes it works, but it will create file in actual
directory and name ':afile'
contrib_regression=# \copy (select 1) to :afile;
COPY 1
vagrant@nfiesta_dev_pg:~/npg$ cat :afile
1
workaround (suggested by Pavel Stěhule) is here:
contrib_regression=# \set afile '/writable_dir/out2.csv'
contrib_regression=# \set cmd '\\copy (SELECT 1) to ':afile
contrib_regression=# :cmd
COPY 1
My PG versin:
contrib_regression=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.10 (Debian 12.10-1.pgdg110+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
Best regards, Jiří Fejfar.
Jiří Fejfar wrote:
I have found maybe buggy behaviour (of psql parser?) when using psql \copy
with psql variable used for filename.
While it's annoying that it doesn't work as you tried it, this behavior is
documented, so in that sense it's not a bug.
The doc also suggests a workaround in a tip section:
From psql manpage:
The syntax of this command is similar to that of the SQL COPY
command. All options other than the data source/destination are as
specified for COPY. Because of this, special parsing rules apply to
the \copy meta-command. Unlike most other meta-commands, the entire
remainder of the line is always taken to be the arguments of \copy,
and neither variable interpolation nor backquote expansion are
performed in the arguments.
Tip
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,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite
On Mon, Apr 25, 2022 at 1:24 AM Jiří Fejfar <jurafejfar@gmail.com> wrote:
contrib_regression=# copy (select 1) to :'afile';
Hopefully you realize that COPY is going to place that file on the server,
not send it to the psql client to be placed on the local machine.
The best way to do copy in psql is:
\set afile '...'
\o :'afile'
copy ... to stdout; --or the variant where you one-shot the \o ( \g with
arguments )
Not only do you get variable expansion but you can write the COPY command
on multiple lines just like any other SQL command.
Additionally, we have a list, and even an online form, for submitting bug
reports. That would have been the more appropriate place to direct this
email.
David J.
Dear Daniel, David
On Mon, 25 Apr 2022 at 18:07, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Mon, Apr 25, 2022 at 1:24 AM Jiří Fejfar <jurafejfar@gmail.com> wrote:
contrib_regression=# copy (select 1) to :'afile';
Hopefully you realize that COPY is going to place that file on the server,
not send it to the psql client to be placed on the local machine.The best way to do copy in psql is:
\set afile '...'
\o :'afile'
copy ... to stdout; --or the variant where you one-shot the \o ( \g with
arguments )Not only do you get variable expansion but you can write the COPY command
on multiple lines just like any other SQL command.
thank you for your advice, \g works pretty well in my case
Additionally, we have a list, and even an online form, for submitting bug
reports. That would have been the more appropriate place to direct this
email.
sorry, I didn't realize that, next time I will send report there
J.
Show quoted text
David J.