psql \copy: Usage, Options & Common CI Errors
\copy bulk-loads or exports table data through the psql client, not the server.
psql \copy is the fast way to load seed CSVs in CI. The crucial distinction from SQL COPY is that \copy reads the file on the client, so it works even when the server cannot see the runner filesystem.
What it does
The \copy meta-command in psql streams data between a local file and a table. Unlike server-side COPY (which reads paths on the database host and needs superuser), \copy runs over the client connection and uses the file path on the machine running psql.
Common usage
psql -h localhost -U postgres -d app -c "\copy users FROM 'users.csv' CSV HEADER"
psql -h db -U postgres -d app -c "\copy users TO 'out.csv' CSV HEADER"
psql -h localhost -U postgres -d app -c "\copy users(id,email) FROM 'u.csv' CSV"
cat data.csv | psql -h localhost -U postgres -d app -c "\copy t FROM STDIN CSV"Options
| Item | What it does |
|---|---|
| FROM / TO 'file' | Import from / export to a client-side file |
| CSV | Treat the data as CSV |
| HEADER | First line is column names (skip on import) |
| DELIMITER 'x' | Field separator (default comma for CSV) |
| (col1, col2) | Restrict to named columns |
| FROM STDIN | Read piped data instead of a file |
Common errors in CI
ERROR: extra data after last expected column / missing data for column "x" - the CSV column count or order does not match the table; list the columns explicitly and use HEADER. Server-side COPY ... FROM '/path' fails with "could not open file ... Permission denied" or "No such file" because the path is resolved on the database host, not the runner - use \copy for client-side files. \copy is a psql meta-command, so it must be on one line and cannot be split like SQL.