Relative path for psql \copy file?

Question

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.

Answer 1

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.

Answer 2

If you want to store data / (root) get data from your local machine and communicate with a PostgreSQL server on a different, remote machine, you cannot simply use COPY.

Try the meta-command \copy in psql. It's a wrapper for the SQL COPY command and uses local files.

Your filename should work as is on a Windows machine, but PostgreSQL interprets it as a local filename on the server, which is probably an Unix derivative and there the filename would have to start with '/'.

Answer 3

If the following is true,

1. You're psql running from C:\
2. C:\dir1\dir2\input.csv is the file.

Then you should be able to load it like this,

\COPY foo.bar (col1) FROM 'dir1/dir2/input.csv' WITH (FORMAT CSV);

Answer 4

I've encountered this exact same use case, and I wasn't in love with the \cd solution for my particular situation.

The solution I worked out is fairly simple, but it's only sensible if the ONLY purpose of the CSV files is to feed the tables. In my setup, the CSV files were stored in version control right alongside the SQL files that used them, so the change was easy to make.

Instead of:

\COPY some_table FROM 'input.csv' WITH (FORMAT CSV)

I used:

\COPY some_table FROM STDIN WITH (FORMAT CSV)
some value,some other value
second row,yet another value
and so on,and so forth
\.

In other words, just move the CSV data directly in-line in your SQL file.

Definitely not suitable for all use cases, but I thought it should be mentioned here as an alternate solution. :)

For further reading see the official documentation of the \copy feature.