skip to content

In psql, how does the \copy meta-command differ from the server-side COPY command, and when do you need \copy instead?

level: middleimportance: should knowfreq 56%

answer

  1. whose filesystem, whose permissions
  2. FROM STDIN under the hood
  3. no server-file role needed
  4. one line, no variables

basics

~20 s

psql's \copy runs COPY ... FROM STDIN or TO STDOUT, and psql reads or writes the file on the client host with the client user's permissions. Server-side COPY uses the server's filesystem and needs superuser or pg_read_server_files / pg_write_server_files.

solid answer

~50 s

`COPY tracking.parcels FROM '/data/parcels.csv'` makes the **server** open that path on the database host, so it needs superuser or the `pg_read_server_files` role (`pg_write_server_files` for `COPY ... TO` a file, `pg_execute_server_program` for `PROGRAM`). `\copy tracking.parcels FROM 'parcels.csv' WITH (FORMAT csv, HEADER)` sends `COPY ... FROM STDIN` instead, and psql streams the file from the **client** host, opened with the permissions of whoever runs psql and resolved against psql's working directory. So you use `\copy` whenever the file is on your laptop or a bastion rather than on the database server, or your role has no server-file privileges, which is normal on managed databases. The options are COPY's own. The costs: every byte crosses the connection, and `\copy` must fit on one line with no variable interpolation; `COPY ... TO STDOUT` ended with `\g file` is the multi-line alternative.

code

console · 6 lines
console
logistics=> COPY tracking.parcels FROM '/tmp/parcels.csv' WITH (FORMAT csv, HEADER);
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.
logistics=> \copy tracking.parcels FROM 'parcels.csv' WITH (FORMAT csv, HEADER)
COPY 18240

go deeper

for a junior

Remember the one-line rule: COPY reads files on the database server, \copy reads files where psql runs. That alone answers most screening versions of this question.

for a middle

Explain the mechanism, \copy becoming COPY FROM STDIN or TO STDOUT, plus the privilege roles that server-side file COPY needs and the one-line, no-interpolation parsing rule.

for a senior

Choose deliberately: server-side COPY for large files already on the host, \copy or STDIN streaming from clients and CI, COPY TO STDOUT with \g when a script needs variables, and never superuser just to load data.

for a principal

Treat server-file roles as a security boundary: pg_read_server_files lets a role read any file the server account can, so keep it out of application roles and design data loads around client-side streaming.

## Two commands, two filesystems PostgreSQL's **`COPY`** is a SQL command executed by the server. When it names a file (`COPY tracking.parcels FROM '/data/parcels.csv'`), the **backend process on the database host** opens that path, with the operating-system permissions of the account the server runs as. Relative paths for `COPY FROM` are resolved against the server's working directory, normally the data directory, and an output file for `COPY TO` must be an absolute path. psql's **`\copy`** is a meta-command with almost the same syntax. psql rewrites it into `COPY ... FROM STDIN` or `COPY ... TO STDOUT`, and **psql itself** reads or writes the file on the machine where psql is running. The data travels over the normal client connection. ## What \copy actually does For `\copy tracking.parcels FROM 'parcels.csv' WITH (FORMAT csv, HEADER)`: 1. psql opens `parcels.csv` on the client host, relative to psql's current directory, as the OS user running psql. 2. It sends `COPY tracking.parcels FROM STDIN WITH (FORMAT csv, HEADER)` to the server. 3. It streams the file's bytes through the connection; the server parses them exactly as it would a server-side file. 4. It reports `COPY n` when the server finishes. `\copy ... FROM PROGRAM 'gunzip -c parcels.csv.gz'` also runs that program on the **client**, again with the client's permissions. ## Privileges, and the error that sends people to \copy Because a file or program named in a server-side `COPY` is opened by the server, PostgreSQL restricts it to superusers and to members of three predefined roles: | Server-side form | Role required (or superuser) | |---|---| | `COPY ... FROM 'file'` | `pg_read_server_files` | | `COPY ... TO 'file'` | `pg_write_server_files` | | `COPY ... FROM/TO PROGRAM` | `pg_execute_server_program` | | `COPY ... FROM STDIN` / `TO STDOUT` | none beyond the table privileges | An ordinary application role trying the first form gets `ERROR: permission denied to COPY from a file` (SQLSTATE `42501`), and the server's own hint says that anyone can copy from stdin or to stdout and that psql's `\copy` works for anyone. Granting superuser to load one CSV file is the wrong fix; `\copy` needs only `INSERT` on the table (or `SELECT` for an export). ## The parsing rules that trip people up - **One line.** psql takes the entire rest of the line as `\copy`'s arguments, so the command cannot span lines the way SQL can. - **No interpolation.** psql performs neither variable interpolation nor backquote expansion in `\copy`'s arguments, so `\copy :tbl FROM 'x.csv'` does not use the variable `tbl`. - **The workaround.** Write ordinary SQL, `COPY (SELECT ...) TO STDOUT WITH (FORMAT csv, HEADER)`, and end it with `\g stuck_parcels.csv` instead of a semicolon. It can span lines, use `:'variables'`, and still writes to a client-side file. - **`stdin` versus `pstdin`.** Inside a script, `\copy ... FROM stdin` reads data rows from the script itself until a line containing only `\.`; `pstdin` reads psql's real standard input regardless of where commands come from. - **Quiet exports.** `\copy ... TO stdout` sends rows to wherever psql output is going and does not print the `COPY n` status line, because it could be mistaken for a data row. ## A typical load from a bastion host An on-call engineer who must backfill a correction file into production, with no shell access to the database host, usually does this: 1. Copy `parcel_fixes.csv` to the bastion and start psql there, in the directory that holds the file. 2. Load it into a scratch table first: `\copy tracking.parcel_fixes_staging FROM 'parcel_fixes.csv' WITH (FORMAT csv, HEADER)`. 3. Check row counts and a sample with ordinary queries. 4. Apply the change with one `UPDATE ... FROM` inside a transaction, so a mistake can still be rolled back. No superuser and no server-file role is involved at any step; the role needs only `INSERT` on the staging table and the privileges the final `UPDATE` requires. ## Choosing between them - The file is on the database host and the load is large: server-side `COPY` avoids pushing every byte through the client, which the psql documentation notes makes `\copy` less efficient. - The file is on your workstation, a bastion or a CI runner: `\copy`, or `COPY ... FROM STDIN` from an application driver. - Your role has no server-file privileges, or there is no access to the server's filesystem at all: `\copy` is the only file-based option. - You need variables or a multi-line query for an export: `COPY ... TO STDOUT` plus `\g file`. Options such as `FORMAT`, `HEADER`, `NULL` and error handling are the same in both forms, because `\copy` passes everything except the source or destination through to `COPY`.

  • How do you export a parameterised, multi-line query to a file on the client from a psql script?
    Write it as SQL, `COPY (SELECT ... WHERE status = :'status') TO STDOUT WITH (FORMAT csv, HEADER)`, and end it with `\g stuck_parcels.csv` instead of a semicolon. Because it is an ordinary query buffer it can span lines and use psql variables, while `\g` sends the output to a client-side file, or to a program with `\g |command`.
  • When is server-side COPY the better choice despite the privileges it needs?
    When the file already sits on the database host, or on storage the server process can read, and the volume is large. Server-side COPY reads it locally instead of pushing every byte through the client connection. Where nobody may touch the server's filesystem, as on most managed services, \copy or COPY FROM STDIN from a driver is the only route.

  • Expecting COPY FROM '/home/me/parcels.csv' to read the file from my laptop
  • Granting superuser because it is the only way to load a CSV file
  • Saying \copy needs pg_read_server_files just like server-side COPY
  • Writing \copy across several lines or with :variables like ordinary SQL
  • Claiming \copy is faster than COPY because the client parses the data