Psql copy Versus Server Side COPY and Which One You Need

PostgreSQL has two commands that look the same and read files in different places, and most of the questions about "file not found" or "permission denied" in a CSV load come from mixing them up. \copy is a psql command. COPY is a SQL command that runs in the server. They accept nearly the same options, and the only thing that really differs is which computer opens the file. This article shows the three failures that follow from that, measures the speed difference, and gives the rule I use. The larger procedure around both is in the guide to loading a CSV into PostgreSQL.

What actually differs between them

\copy is not a SQL command. psql parses it, opens the file itself, and sends the rows to the server over the connection with COPY ... FROM STDIN. The server never sees a file name. That is why it needs no special privileges beyond the right to insert into the table, and why it works against a database in a container, in a cloud service or across the internet.

COPY ... FROM 'path' is SQL. The server process opens the path on its own filesystem and reads it. That is why it can be fast on a big file sitting next to the data directory, why it needs a privilege, and why it fails when the file is on your laptop. The reference page for COPY says so in one sentence, and its note on psql's \copy repeats it.

Failure 1: the server cannot see your file

Here is what happens when you run server side COPY with a path that exists on the client:

psql -q -c "TRUNCATE customers"
psql -c "COPY customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)" 2>&1

ERROR:  could not open file "/batches/csv-0001-0200/lab/data/pg/customers.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

could not open file ... for reading: No such file or directory, and the server's hint that you may want a client side facility such as psql's \copy. The path is correct and the file is there, from where you stand. The server is another machine, or at least another filesystem, and it looked for the path there. With Docker the mistake is easy because the same path often works on a host and not in the container.

Failure 2: a relative path

A relative path in server COPY is not relative to your shell. It is relative to the server's working directory, which for a PostgreSQL server is normally the data directory:

psql -c "COPY customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)" 2>&1

ERROR:  could not open file "customers.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

The file customers.csv was in my current directory on the client, and the server did not find it, by the same reasoning as before. The fix is an absolute path on the server, which means the file has to be put there first. On Linux that is a scp or a mounted volume, and the path then has to be readable by the operating system user the database runs as, usually postgres. A "permission denied" from the operating system, as opposed to the one below from PostgreSQL, means the file's mode or directory permissions need fixing.

Failure 3: no privilege

When the path exists on the server, the next failure is PostgreSQL's own permission check. A role that can insert into the table but has no file privilege gets this:

psql -q -c "DROP ROLE IF EXISTS loader" -c "CREATE ROLE loader LOGIN PASSWORD 'secret'" -c "GRANT INSERT, SELECT ON customers TO loader"
PGUSER=loader psql -c "COPY customers FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER true)" 2>&1
echo "--- the same role with \\copy"
PGUSER=loader psql -c "\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true)" -c "SELECT count(*) FROM customers" 2>&1 | head -6

ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
--- the same role with \copy
COPY 4
 count 
-------
     4
(1 row)

permission denied to COPY from a file, with the detail that only roles with the privileges of pg_read_server_files may do it, and the hint that anyone may use stdin and \copy. The same role, in the second half of the output, loaded the four rows with \copy and saw them: COPY 4 and a count of 4.

Granting the role fixes the first form:

psql -q -c "GRANT pg_read_server_files TO loader" -c "TRUNCATE customers"
PGUSER=loader psql -c "COPY customers FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER true)" 2>&1
psql -q -c "REVOKE pg_read_server_files FROM loader"

COPY 4

I would not grant it to an application role. pg_read_server_files lets the role read any file the server process can read, within the limits of file permissions, and COPY into a table is a way to display the contents of a configuration file. The predefined roles page describes the grant, and the point of the design is that the file facilities are for administrators. A loader that needs them is almost always better written with \copy or with standard input.

Is one faster

The usual answer is that server side COPY is faster because the data skips the network and the client. I measured it on the 2,000,000 row file with the file on the client for \copy and on the server for COPY, median of three runs each, with the table emptied before each run:

python time_copy.py

method                          median s
\copy, file on the client           2.11
COPY, file on the server            2.04

\copy took 2.11 s and server side COPY 2.04 s. A 3% difference between two runs on one laptop is noise, and the reason is that the bottleneck in this test is the server parsing and inserting, not the transfer: a local container network moves 130 MB in a fraction of a second. On a link with real latency or limited bandwidth, \copy is bound by the network and a server side file avoids it, so the ordering would change. Test with your own link before believing either number, including mine.

The third option: standard input

Both commands can read standard input, and that is the form to use from a script or an application. The psql reference lists \copy among its meta-commands:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM STDIN WITH (FORMAT csv, HEADER true)" < customers.csv

COPY 4

\copy ... FROM STDIN told psql to use the data that followed on its input, and the shell redirected the file in. The other spelling, COPY ... FROM STDIN, is plain SQL and works in any driver: psycopg has cursor.copy, JDBC has CopyManager, and Node's pg-copy-streams provides the same. They send the same protocol messages, which is why \copy is just a convenience wrapper. From a program, you do not need either command to have a file at all: stream the rows.

Which one I use

\copy for everything run by a person or a script on a machine that has the file, which is nearly every case. COPY ... FROM STDIN from application code. Server side COPY ... FROM 'path' when I am an administrator on the database host, the file is already there and I do not want to move 40 GB through the client. In that case I make sure the role is mine and not an application's.

When the choice is made for you

On managed PostgreSQL such as RDS, Cloud SQL or Azure, you have no shell on the server and the file facilities are disabled or replaced by an import from object storage, so \copy is the only generic route, and the provider's own import tool is the alternative for files already in the cloud. If your psql runs on Windows, a \copy path with backslashes needs quoting or forward slashes, and \copy accepts C:/data/file.csv. If the data must come from a compressed file or a URL, the tool is \copy ... FROM PROGRAM, not a server path, and the articles on gzipped files and URLs cover it. And if the permission message is the only thing in your way, check whether you really need the file on the server before asking for the privilege.