Postgresql – Relative path for psql \copy file

postgresqlpsql

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 \ir.

For example, let's say I start psql in 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 ... FROM command from inside a script without requiring the user to run the psql session 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.

Best Answer

I think there's no way to indicate to \copy that the file is relative to the location of the script, but you may use \cd to change the current directory before \copy.

\cd interpolates variables so the directory can be passed on the command line with -v:

psql -vscriptdir="c:\path\to\script" -f c:\path\to\script\script1.sql

Before including anything, say there's this command in the top-level script:

\cd :scriptdir

(except if the -f argument is simply a file without a path: in this case it's not necessary since the current directory is already set where the script is)

Then every inclusion of a script in a subdirectory should be:

\cd dir2
\i script2.sql
\cd ..

(as opposed to \ir dir2/script2.sql)

When script2.sql will invoke \copy with a file relative to it, the data file will be found since it's in the current directory.

That should work recursively, assuming scripts in subdirectories also follow that convention, and that they don't do an unrelated \cd that would interfere with the \cd .. waiting to happen on the way back to the top.

Related Question