I have the following sql script:
\COPY my_table (column_1, column_2) FROM :csv_file WITH (FORMAT CSV, DELIMITER ',', ESCAPE '"');
which I am calling from:
psql -d $DB_NAME -f $SQL_FILE -v csv_file="$CSV_FILE"
But it keeps looking for a file called ":csv_file". Am I doing something wrong?
In general, variable substitution does work with meta-commands (starting with backslash), but \copy is an exception, as documented in psql's manpage:
The syntax of this command is similar to that of the SQL COPY command. All options other than the data source/destination are as specified for COPY. Because of this, special parsing rules apply to the \copy command. In particular, psql's variable substitution rules and backslash escapes do not apply.
I disagree with @BaconBits comment to the question that plpgsql's EXECUTE could be the answer. A server-side statement, dynamic or otherwise, will not access the client-side file system, contrary to \copy. You may use COPY instead, but it requires to be superuser and that file to be accessible to the postgres user on the server.
I believe that to \copy from a variable filename, the variable must be injected into the script before psql reads it. You might integrate the SQL script into a shell script and feed it to psql as a patchable here-string, or filter it through sed or perl or any similar unix-ish method.
psql -c '\COPY ...'EXECUTE.\set table pg_user \\ select * from :"table";