TopolearnCOPY is an order of magnitude faster than row-by-row INSERTs for imports, exports and data fixes.
COPY moves data between a table (or query) and a file or stream in one efficient operation. It is the fastest way to load data into PostgreSQL — typically many times faster than individual INSERT statements, because it avoids per-statement parsing and network round trips.
There are two flavours that look alike:
COPY table FROM '/path/file.csv'runs on the server: the path is on the database server's filesystem, and you need superuser or thepg_read_server_files/pg_write_server_filesroles. Useless for a managed database where you can't reach the server's disk.\copy table FROM 'file.csv'is a psql command: psql reads the file on your machine and streams it throughCOPY ... FROM STDIN. Same options, ordinary privileges. This is what you usually want.
Common options: (FORMAT csv, HEADER true), DELIMITER, NULL '', and a column list to load only some columns. To export a query result: \copy (SELECT ...) TO 'out.csv' (FORMAT csv, HEADER).
COPY is all-or-nothing: one malformed row aborts the whole load (PostgreSQL 17 added ON_ERROR ignore to skip rows with conversion errors). A robust pattern for messy files is to load into a staging table of text columns, clean with SQL, then INSERT ... SELECT into the real table. Drivers expose COPY too, for example cursor.copy() in psycopg 3.
Example
# products.csv
# sku,name,price
# A1,Pen,1.50
# B2,Notebook,4.00
psql shop <<'SQL'
CREATE TABLE product (sku text PRIMARY KEY, name text, price numeric(8,2));
\copy product FROM 'products.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*) FROM product;
-- 2
\copy (SELECT sku, price * 1.2 AS price_vat FROM product) TO 'vat.csv' WITH (FORMAT csv, HEADER)
SQLRemember
- COPY is the fast path for bulk import and export
- COPY reads files on the server; \copy reads them on the client
- Load messy data into a text staging table, then clean with SQL
Watch out for
- Using server-side COPY with a path on your laptop and getting 'could not open file'.