Bulk Loading with COPY

Inserting a large dataset (thousands to millions of rows) with individual INSERT statements is slow — each one pays the overhead of a full SQL round trip. COPY streams data in bulk instead, and is dramatically faster.

COPY FROM — Loading Data In

COPY employees (name, email, department, salary)
FROM '/path/to/employees.csv'
DELIMITER ','
CSV HEADER;

• CSV HEADER tells Postgres the first line is column names, not data — skip it, don't insert it.
• The file path must be readable by the PostgreSQL server process, not just your own user — which is often inconvenient for remote/managed databases.

\copy — The psql Client-Side Alternative

\copy (with a backslash, run from psql) reads the file from your own machine instead of the server's filesystem — this is what you'll actually use most of the time when connecting to a remote or managed database.

psql -d shop -c "\copy employees(name, email, department, salary) FROM 'employees.csv' CSV HEADER"

COPY TO — Exporting Data Out

COPY (SELECT * FROM employees WHERE department = 'Engineering')
TO '/path/to/engineering.csv'
CSV HEADER;

psql -d shop -c "\copy (SELECT * FROM employees) TO 'all_employees.csv' CSV HEADER"

Why COPY Is So Much Faster

• A single COPY statement is one operation for the whole dataset, instead of one round trip per row.
• It skips much of the per-statement parsing/planning overhead that individual INSERTs incur.
• It's the standard tool for ETL jobs, data migrations, and loading exports from other systems.

A Realistic Bulk-Load Pattern

For very large loads, it's common to load into an unconstrained staging table first, then insert the cleaned data into the real table:

CREATE TABLE staging_employees (LIKE employees INCLUDING DEFAULTS);

\copy staging_employees FROM 'employees_export.csv' CSV HEADER

INSERT INTO employees
SELECT * FROM staging_employees
WHERE email IS NOT NULL
ON CONFLICT (email) DO NOTHING;

This lets you validate/dedupe in SQL before the data ever reaches the real, constrained table.

Common Errors & Troubleshooting

• ERROR: could not open file for reading: Permission denied — plain COPY runs on the server, which needs its own filesystem permission to read that path; use \copy (client-side, in psql) instead if the file is only on your machine.
• ERROR: extra data after last expected column — the CSV has more (or fewer) columns than the target table/column list — double-check the delimiter matches (comma vs. semicolon vs. tab) and that no field contains an unescaped comma.
• Duplicate key errors partway through a large COPY — the whole COPY is one transaction by default, so a single bad row aborts the entire load; load into an unconstrained staging table first (as shown above), then filter/dedupe before inserting into the real, constrained table.

Key Takeaways

• COPY is orders of magnitude faster than row-by-row INSERT for large datasets — always prefer it for bulk loads.
• \copy (client-side, in psql) reads from your own machine; plain COPY (server-side, in SQL) requires the file to be on the database server itself.
• Loading into an unconstrained staging table first, then INSERT ... SELECT into the real table, is the standard pattern for messy bulk data.