I am attempting to create a collection of scripts to modify some tables in a Postgres 9.6 database. These scripts are stored in a variety of directories, and are in turn called from one master coordinator script. I'm running the scripts in psql.
In the master script, I use
\ir to specify relative pathnames to the other scripts. Accordingly, I can run psql from anywhere, and the master script will find the other scripts. This works as expected.
In some of the scripts, however, I am using the
\copy command to load data from CSV files. Unfortunately, it appears that the input filename for
\copy is always interpreted as relative to psql's current working directory, rather than relative to its script's directory, even if I call the script containing the
\copy command using
For example, let's say I start
C:\, and plan to use the following files:
C:\ dir1\ script1.sql dir2\ script2.sql input.csv
I can call the first script using
\ir dir1/script1.sql. That works fine.
Also, that script can call the second script using
\ir dir2/script2.sql. That works fine, as well.
The problem is the
\copy command in the second script:
\COPY foo.bar (col1) FROM 'input.csv' WITH (FORMAT CSV);
This fails unless
input.csv exists in the current directory of the
psql session. Therefore, my question is:
- Is there any way for me to call the
\COPY ... FROMcommand from inside a script without requiring the user to run the
psqlsession from a specific directory relative to the input file?
I understand that I could use an absolute path name, but we're going to be running this script from a variety of different machines/platforms when upgrading our different deployments, so there is no common absolute path that will work in this case. I'm looking for some sort of relative or configurable path solution.
Thank you, in advance.