Using \set variables in psql cli working in normal queries but not working/expanding in \copy

Question

As described in the title, the issue is that psql variables, set using \set work for me except when used inside the \copy function provided by the psql client

Is there a some special syntax required to reference a psql variable inside a \copy? Or am I out of luck? And if so, is this documented anywhere?

I couldn't find this issue in StackOverflow or documented anywhere. I looked at ~20 posts but found nothing. I also checked the documentation on \copy for PostgreSQL 11 (the version of the CLI) and saw no caveats about this- I searched the page for "variable" and found nothing related to this. I also searched for "expansion" and "expand" and found nothing. So now I'm here asking for help...

The version of PostgreSQL client is 11.10 with whatever downstream patches Debian applied:

psql (PostgreSQL) 11.10 (Debian 11.10-1.pgdg100+1)

I'm pretty sure the server version has little to no relevance, but just to be thorough, the server is version 10.13 as shipped by Ubuntu:

psql (PostgreSQL) 10.13 (Ubuntu 10.13-1.pgdg16.04+1)

Reproducing

I'm aware of the difference between \copy and COPY (one being implemented as a feature in the psql client, the other being a server feature executing in the context of the server process) and for this task, what I need to use is definitely \copy

The standard query that shows I'm setting and referencing the variables correctly:

[local:/tmp]:5432 dbuser@dbdev# \set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# \set var_cname fname
[local:/tmp]:5432 dbuser@dbdev# SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1;
fname|lname|score|nonce
TestVal|C|100|b
(1 row)
Time: 88.786 ms

The failing case(s), which seem to be failing because the variables are referenced inside of \copy- I don't see any other difference between this and the working example:

[local:/tmp]:5432 dbuser@dbdev# \set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# \set var_cname fname
[local:/tmp]:5432 dbuser@dbdev# \copy (SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from :var_tname WHERE :var_cname = 'TestVal...
                              ^
Time: 193.322 ms

Obviously, based on the error, the expansion is not happening and the query is trying to reference a table with a literal name of :var_tname

I wasn't expecting quoting to help with this, but tried it just in case- who knows, could be a bizarre exception, right? Unsurprisingly, it's of no help either:

[local:/tmp]:5432 dbuser@dbdev# \copy (SELECT * from :'var_tname' WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from : 'var_tname' WHERE :var_cname = 'Test...
                              ^
Time: 152.407 ms
[local:/tmp]:5432 dbuser@dbdev# \set var_tname 'ag_test'
[local:/tmp]:5432 dbuser@dbdev# \copy (SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from :var_tname WHERE :var_cname = 'TestVal...
                              ^
Time: 153.001 ms
[local:/tmp]:5432 dbuser@dbdev# \copy (SELECT * from :'var_tname' WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from : 'var_tname' WHERE :var_cname = 'Test...
                              ^
Time: 153.459 ms

I also tried setting the variables with single quotes (which is probably a best practice anyway) but this made no difference:

[local:/tmp]:5432 dbuser@dbdev# \set var_tname 'ag_test'
[local:/tmp]:5432 dbuser@dbdev# \set var_cname 'fname'
...  ...

Is variable expansion just not supported inside of \copy? If so, that seems like a really crummy limitation, and it doesn't seem to be documented

One last thing to add as I expect someone may ask- there is a reason I'm not implementing these as functions or stored procedures. First, my version of PostgreSQL doesn't support stored procedures at all. It also does not support transactions in functions. Even if it did, the real reason I want these queries in psql files in the application repository is because they are then very easy to read for code reviews, easy to maintain for development, and act as documentation

It's not necessary to read beyond this point unless you also have this problem and want ideas for workarounds

Beyond this I've documented a bunch of workarounds I could quickly think of- the problem can be worked around 1001 different ways. But if there's a solution to this quirky behavior that lets me stick with it, I would much rather know about it than apply any workarounds. I also added use-case information below as it's not unheard of for responses to be along the lines of "why are you doing this? Just don't use xyz feature, problem solved!". I'm hoping to not receive any of those responses :>

Thanks for anyone willing to help out!

Options for Workarounds

I have plenty of options for workarounds, but I really would like to understand why this doesn't work, if it's documented somewhere, or if there may be some special way to cause the expansion to occur when used inside \copy, to avoid needing to change this- for reasons I explain below in the Use-Case section

Here are the workarounds I've come up with...

SELECT into temporary table using the variables, \copy that fixed name table

SELECT * INTO tmp_table FROM :var_tname WHERE :var_cname = 'TestVal' LIMIT 1;
\copy (SELECT * FROM tmp_table) TO 'testvar.csv'

That works, but it's kind of clunky and seems like it shouldn't be unnecessary

Produce a TSV using \pset fieldsetp and redirect stdout to the file (clunky, may have escaping issues)

The other option would be not using \copy and piping stdout to a file after setting the delimiter to tab:

[local:/tmp]:5432 dbuser@dbdev# \set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# \pset format unaligned
Output format is unaligned.
[local:/tmp]:5432 dbuser@dbdev# \pset fieldsep '	'
Field separator is "    ".
[local:/tmp]:5432 dbuser@dbdev# SELECT * from :var_tname LIMIT 1;
fname   lname   score   nonce
TestVal G   500 a
(1 row)
Time: 91.596 ms
[local:/tmp]:5432 dbuser@dbdev#

This could be invoked via psql -f query.psql > /the/output/path.tsv. I haven't checked yet but I'm assuming that should produce a valid TSV file. The one thing I'm not sure of is whether it will correctly escape or quote column values that contain tabs, like \copy or COPY would

Do the expansion in a shell script and write to temporary psql file, use psql -f tmp.psql

The final workaround would be setting the variables in a shell script, and invoking using psql -c "$shellvar", or writing the shell-expanded query to a temporary .psql file and then invoking with psql -f, and deleting the temporary file

The Use-Case (and why I don't particularly like some of the workarounds)

I should probably mention the use case... I have several separate (but related) Python applications that collect, parse and process data and then load them into the database using psycopg2. Once the raw data is in the database, I delegate a bunch of heavier logic into psql files, for readability and to reduce the amount of code that needs to be maintained

The psql files are invoked at completion of the application using something like this:

for psql_file in glob.glob(os.path.expandvars("$VIRTUAL_ENV/etc//psql-post.d/*.psql:
    subprocess.call([which('psql'), '-f', psql_file])

One of the reasons I want to use variables for the table names (and some column names) is because the database is currently being refactored/rebuilt, so the table names and a few column names are going to be renamed over time. Because some of the .psql scripts are quite extensive, the table names are referenced quite a few times in them- so it just makes more sense to set them once at the top, using \set, so as each table gets changed in the database, only one change in each psql file is required. There also may be minor changes in the future that make this approach better than one where you need to search and replace 10-15 instances of various column or table names

One last workaround I don't really want to use: templating psql files from Python

I realize I could use some home-grown templating or Jinja2 directly from the Python code to dynamically generate the PSQL files from templates as well. But I much prefer to have pure psql in the files as it makes the project much more readable and editable for those who may need to perform a code review, or take over maintenance of the projects in the future. It's also easier for me to work with. Obviously there are tons of options for workarounds once we start talking about doing this from within Python using queries via psycopg2- but having the .psql files in the same relative directory of each project repository serves a very useful purpose

Adrian Klaver · Accepted Answer

It seems to be a parsing issue with \copy. UPDATE: Actually a documented behavior: https://www.postgresql.org/docs/current/app-psql.html

\copy ... 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.

\set var_tname 'cell_per'

\copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( select * from :var_tname ) TO STDOUT WITH (FORMAT CS...

 \copy (select * from :"var_tname") to stdout WITH (FORMAT CSV, HEADER);
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( select * from : "var_tname" ) TO STDOUT WITH (FORMAT...

--Note the added space when using the suggested method of including a variable as 
--table name. 

copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
copy (select * from :"var_tname") to stdout WITH (FORMAT CSV, HEADER);

--Using COPY directly works.

--So:

\o cp.csv
copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
\o

--This opens file cp.csv  COPYs  to it and then closes file. 

-- Or per docs example and UPDATE:

copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER) \g cp.csv


cat cp.csv  

line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category
5,H PREM 3.5,18,,06/02/2004 15:11:26,,postgres,herb,none,HP3
7,HERB G,1,,06/02/2004 15:11:26,,postgres,herb,none,HG
9,HERB TOP,1,,06/02/2004 15:11:26,,postgres,herb,none,HT
10,VEGGIES,1,,06/02/2004 15:11:26,,postgres,herb,annual,VG

Using \set variables in psql cli working in normal queries but not working/expanding in \copy

Answers (2)

Related Questions