Variable substitution in psql when using \copy

Question

I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that:

The syntax of the command is similar to that of the SQL COPY command. Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply.

This seems quite definitive, however I'm wondering if anyone knows of a workaround?

Answer 1

You can circumvent this limitation by piping the whole command to psql:

echo "\copy tbl FROM '/path/to/myfile.pgsql'" | psql mydb

Answer 2

Remark: I stumbled upon this problem myself on MS Windows and I already had something to feed into psql using its stdin. I had to combine inputs. And that is where it gets really tricky. I thought to share a somewhat valuable in my opinion example, besides Erwin's answer, so I'm posting it here as an answer in case someone else is also in need to "use variable" with \copy while feeding data into stdin on Windows platform.

If you want to copy some data from stdin and use "variable" for \copy at the same time, things may get tricky with parentheses escaping. Below is an example how it can be done. Note triple (sic!) caret escape for parentheses scoping column names

@echo off
set TBL=wd
(
 echo truncate %TBL%;
 echo \copy %TBL% (depth,path,name,created,accessed,modified,size^^^) from stdin csv
 C:\msys64\usr\bin\find ^
  "e:/somepath" ^
  -type f -printf "%%d,\"%%h\",\"%%f\",\"%%t\",\"%%a\",\"%%c\",%%s\n"
) | "C:\Program Files\PostgreSQL\9.4\bin\psql.exe" -h some.server -U user dbname