Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
It's customary to add a link when quoting. – Erwin Brandstetter Feb 4 '13 at 19:37
    
I've found the source and added the link. – Erwin Brandstetter Feb 4 '13 at 20:55
up vote 2 down vote accepted

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

echo '\copy tbl FROM ''/path/to/myfile.pgsql''' | psql mydb
share|improve this answer
    
Thanks, that does the job. – beacon_bonanza Feb 5 '13 at 9:18
    
This is just ugly especially when one wants to load stuff from pstdin and on MS Windows :( – mlt Dec 23 '15 at 4:19

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.