Postgres Pro
Downloads
Home   >   mailing lists

Thread: variable filename for psql \copy

variable filename for psql \copy

From
Jiří Fejfar
Date:
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.

Re: variable filename for psql \copy

From
"Daniel Verite"
Date:
    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

Re: variable filename for psql \copy

From
"David G. Johnston"
Date:
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.

Re: variable filename for psql \copy

From
Jiří Fejfar
Date:
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.
David J.