I am trying to run, via a linux shell script, a Postgresql command that reads a CSV and updates a table with it.
The difficulty I am facing, is that this shell script has to pass an argument to the SQL program, argument that contains the path where the CSV is stored.
I just can't make it…
The PSQL command in my shell script is:
PSQL -f ${UNXSQLPATH}/new_nomenclature.sql - v v1=${UNXDATAPATH}
And the new_nomenclature.sql program contains the following line:
COPY nomenclature FROM :v1||'new_nomenclature.csv' CSV DELIMITER ';' HEADER ENCODING 'LATIN9';
Note: when I hardcopy the path in the SQL program, everything works fine.
Best Answer
Utility commands like
COPY
and many others do not accept expressions where literals are expected, so the file name must be given verbatim.You may use
\set
in psql to build the filename by concatenating a path with a name, as in:When
\set
is not suitable, another way is to call SELECT to build the string, get the result into a psql variable, and feed it back to COPY (this might come handy if building the path requires more than a mere concatenation)