How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

Question

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.

Daniel Vérité · 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:
$ cat script.sql
set path :v1 '/new_nomenclature.csv'
COPY nomenclature FROM :'path' CSV ....

$ psql -f script.sql -v v1="/tmp"

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)
$ cat script.sql
select :'v1' || '/new_nomenclature.csv' AS path gset
COPY nomenclature FROM :'path' CSV ....

How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

One Answer

Add your own answers!

Ask a Question