COPY and Bulk Loading

Import and export large datasets efficiently using the COPY command.

Overview

COPY is PostgreSQL's bulk data transfer command far faster than repeated INSERTs for loading or exporting large datasets. COPY FROM imports data from a file or stdin into a table; COPY TO exports query results to a file or stdout. It supports CSV, text, and binary formats. The \COPY psql meta-command (with a backslash) works similarly but transfers data through the client connection, so it doesn't require superuser privileges and can access files on the client machine. COPY (without backslash) runs on the server and requires file access on the server.

Key Points

•COPY table FROM '/path/file.csv' CSV HEADER
•COPY (SELECT ...) TO '/path/out.csv' CSV HEADER
•\COPY in psql client-side, no superuser needed
•Much faster than individual INSERTs for bulk data
•Supports CSV, text (tab-delimited), and binary formats
•Use COPY ... FREEZE for even faster loading into empty tables

Examples

COPY import and export

-- Import from CSV (server-side, requires superuser)
COPY users FROM '/tmp/users.csv' CSV HEADER;

-- Export to CSV
COPY users TO '/tmp/users_backup.csv' CSV HEADER;

-- Export specific columns
COPY (SELECT id, email FROM users WHERE is_active) TO STDOUT CSV HEADER;

-- psql client-side (no superuser needed)
-- \COPY users FROM 'local_file.csv' CSV HEADER

COPY and Bulk Loading

Overview

Key Points

Examples

Exercises

Knowledge Check

What is the main advantage of COPY over INSERT for bulk loading?