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 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 workaroud?
You could use shell variable substitution with heredoc syntax. Example:
#!/bin/sh
tablename=foo
psql -d test <<EOF
\copy $tablename FROM '/path/to/file'
EOF
You can pass the variable from the shell to psql with -v psql_var="$shell_var" commandline parameter (or access it directly with a shell escape `echo "$shell_var"` after exporting it). Then, you can build the \copy meta command in another meta command (locally with \set or server side with \gset). Example:
#!/bin/sh
tablename=foo
psql -d test -v tbl="$tablename" <<\EOF
\set cmd '\\copy ' :tbl ' FROM ''/path/to/file'''
\echo :cmd
:cmd
EOF
