variable filename for psql \copy

From: Jiří Fejfar <jurafejfar(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: variable filename for psql \copy
Date: 2022-04-25 08:24:49
Message-ID: CA+8wVNX6WgxgUxPFka=Ob-OQhOZVg0XM-NYqwHa5KTFr38JEJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message houzj.fnst@fujitsu.com 2022-04-25 08:35:05 RE: Perform streaming logical transactions by background workers and parallel apply
Previous Message Richard Guo 2022-04-25 07:21:06 Re: A problem about partitionwise join