57 COPY

COPY is PostgreSQL’s bulk-data import/export command. It moves rows between a table and a file (or standard input/output) in CSV, text, or binary format. Compared to INSERT, COPY is dramatically faster — sometimes 10–100x — because it skips much of the per-row overhead. For loading large amounts of data, COPY is what you reach for.

What you’ll learn

• COPY ... TO for export and COPY ... FROM for import
• The difference between COPY (server-side) and \copy (client-side)
• CSV format and the HEADER, QUOTE, DELIMITER options
• Streaming via STDIN / STDOUT
• Why COPY beats INSERT for bulk loads

Example

-- Export to STDOUT in CSV (the rows go to your terminal when run via `psql -f`)
COPY (SELECT id, name FROM fruits ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER true);

-- Export a whole table
-- (commented because most workflows use the CTAS / pg_dump path instead)
-- COPY fruits TO STDOUT WITH (FORMAT csv, HEADER true);

-- Server-side import: the SERVER must read the path.
-- This is rarely what you want unless you control the server filesystem.
-- COPY fruits FROM '/srv/data/fruits.csv' WITH (FORMAT csv, HEADER true);

-- Client-side import: the psql CLIENT reads the file from your machine.
-- In interactive psql:
-- \copy fruits FROM '/Users/you/Desktop/fruits.csv' WITH (FORMAT csv, HEADER true)

-- COPY can also stream from STDIN — used by drivers for bulk loading.
-- Example shows the protocol; the actual rows would follow the command in a stream.
-- COPY fruits FROM STDIN WITH (FORMAT csv, HEADER true);

COPY ... TO STDOUT writes to the connection’s output stream. When you run a SQL file with psql -f, that stream is your terminal — handy for ad-hoc exports. Redirect from the shell to save to a file: psql -f source/copy.sql postgres > out.csv.

COPY ... FROM <path> reads from a file on the server. The PostgreSQL process needs read access; on a managed cloud database, you usually do not. The client-side \copy (a psql meta-command) reads the file on your machine, streams it through the connection, and inserts it on the server — exactly the same behavior as COPY FROM STDIN, just wrapped for convenience.

For application code doing bulk loads, almost every driver exposes a “COPY FROM STREAM” API (psycopg’s copy_from, pgx’s CopyFrom, etc.). For a few thousand rows it is much faster than batched INSERTs; it is often the best starting point for millions of rows, though the benefit depends on the workload.

COPY FROM invokes triggers and enforces check constraints and foreign keys on the destination table. Trigger work therefore contributes to load cost. It does not invoke rules. A staging table is useful for validating and transforming a load before inserting into the final table.

Run the example

To run:

$ psql -f source/copy.sql postgres
id,name
1,apricot
2,blueberry
3,chestnut
4,damson
5,elderberry
101,fruit_1
102,fruit_2
103,fruit_3

Common pitfalls

• Mixing up COPY and \copy: the first runs on the server (paths are server paths, permissions are server permissions); the second runs on the client (paths are local). A common mistake is writing COPY ... FROM '~/file.csv' and getting “permission denied” because the server cannot read your home directory.
• Encoding mismatches: The file uses the ENCODING option, or the current client encoding when that option is absent. Mis-encoded CSVs produce garbled text or “invalid byte sequence” errors. Use WITH (ENCODING 'utf8') or convert beforehand.
• CSVs with embedded newlines need QUOTE and ESCAPE options. The defaults (" for both) usually work.
• COPY with constraints on a huge load is slow. Trick: load into a staging table, deduplicate / validate, then INSERT ... ON CONFLICT into the real table.

CREATE TEMP TABLE fruits_stage (id integer, name text);
\copy fruits_stage FROM 'out.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*) FROM fruits_stage;

Downloads

SQL file: copy.sql · All lesson files · Setup guide

Back to Table of Contents