The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Import a client file | \copy app.stage_order (external_id, ordered_at, amount)
FROM './orders.csv'
WITH (FORMAT csv, HEADER match) | View examples |
| Import a server file | COPY app.stage_order (external_id, ordered_at, amount)
FROM '/srv/import/orders.csv'
WITH (FORMAT csv, HEADER match); | View examples |
| Stream from a client | COPY app.stage_order (external_id, ordered_at, amount)
FROM STDIN
WITH (FORMAT csv, HEADER match); | View examples |
| Verify CSV headings | COPY app.stage_order (external_id, ordered_at, amount)
FROM STDIN
WITH (FORMAT csv, HEADER match); | View examples |
| Choose a NULL marker | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, NULL 'NULL'); | View examples |
| Keep empty text | COPY app.contact
FROM STDIN
WITH (FORMAT csv, HEADER match, FORCE_NOT_NULL (display_name)); | View examples |
| Declare source encoding | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, ENCODING 'UTF8'); | View examples |
| Create transaction staging | CREATE TEMP TABLE stage_order (LIKE app.orders INCLUDING DEFAULTS)
ON COMMIT DROP; | View examples |
| Promote valid rows | INSERT INTO app.orders (external_id, ordered_at, amount)
SELECT external_id, ordered_at, amount
FROM stage_order; | View examples |
| Bound conversion rejects | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, ON_ERROR ignore, REJECT_LIMIT 20); | View examples |
| Report rejected fields | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, ON_ERROR ignore, REJECT_LIMIT 20, LOG_VERBOSITY verbose); | View examples |
| Export to a client file | \copy app.orders (external_id, ordered_at, amount) TO './orders.csv'
WITH (FORMAT csv, HEADER true) | View examples |
| Export a stable projection | \copy (
SELECT external_id, ordered_at, amount
FROM app.orders
ORDER BY external_id
) TO './orders.csv'
WITH (FORMAT csv, HEADER true) | View examples |
| Monitor COPY progress | SELECT pid,
command,
type,
bytes_processed,
tuples_processed
FROM pg_stat_progress_copy; | View examples |
| Reclaim failed-load space | VACUUM (ANALYZE) app.stage_order; | View examples |
Bulk transfer crosses database, filesystem, encoding, and trust boundaries at once. Prefer client-side \copy unless a controlled server-side path is genuinely required, name columns explicitly, define the CSV contract, and load untrusted data into a restricted staging table before promotion. Treat skipped rows, trigger behavior, RLS limitations, exported secrets, and spreadsheet formulas as security and correctness concerns—not cleanup details.
Step by step
Detailed examples
Choose the filesystem and privilege boundary deliberately
SQL COPY with a filename reads or writes on the database server as the operating-system PostgreSQL account. It requires superuser or a powerful predefined server-file role and can reach any file that account can access. psql \copy instead uses COPY FROM STDIN or TO STDOUT and accesses the client filesystem as the local user. Prefer the client path for routine transfers. Never interpolate untrusted input into COPY PROGRAM: the server invokes it through a shell. COPY FROM also cannot target an RLS-enabled table in PostgreSQL 18; use authorized INSERT statements or a separately secured staging workflow.
\copy app.stage_order (external_id, ordered_at, amount) FROM './orders.csv' WITH (FORMAT csv, HEADER match, ENCODING 'UTF8')Make the CSV dialect part of the interface
Always list destination columns so schema additions do not shift incoming fields. In PostgreSQL 18, HEADER MATCH rejects a header whose names, number, or order differ from that list; older supported releases accept only a Boolean HEADER option. CSV's default unquoted empty field means NULL, while a quoted empty field means an empty string. Set NULL, FORCE_NULL, or FORCE_NOT_NULL only when the producer's contract requires it, and declare ENCODING when the source is known. CSV values can contain embedded line breaks, so line counts are not row counts.
COPY app.stage_contact (external_id, display_name, email)
FROM STDIN
WITH (
FORMAT csv,
HEADER match,
NULL 'NULL',
FORCE_NOT_NULL (display_name),
ENCODING 'UTF8'
);Load untrusted data into a narrow staging contract
COPY FROM invokes destination triggers and check constraints, but it does not invoke rules and it supplies explicit values to identity columns. A dedicated staging table lets you preserve source text, quarantine invalid rows, check uniqueness and foreign keys, and promote only reviewed columns. Keep it inaccessible to runtime roles, run the workflow in a transaction when size permits, record source metadata and row counts, and make promotion idempotent for safe retries.
BEGIN;
CREATE TEMP TABLE stage_order (
external_id text PRIMARY KEY,
ordered_at timestamptz NOT NULL,
amount numeric(12,2) CHECK (amount >= 0)
) ON COMMIT DROP;
COPY stage_order (external_id, ordered_at, amount)
FROM STDIN WITH (FORMAT csv, HEADER match);
INSERT INTO app.orders (external_id, ordered_at, amount)
SELECT external_id, ordered_at, amount
FROM stage_order
ON CONFLICT (external_id) DO UPDATE
SET ordered_at = EXCLUDED.ordered_at, amount = EXCLUDED.amount;
COMMIT;Treat tolerated errors as an audited exception path
PostgreSQL 18 can ignore data-type conversion errors in text or CSV COPY FROM, but ON_ERROR ignore does not make arbitrary constraint, trigger, encoding, or structural failures safe. Without REJECT_LIMIT, every conversion error may be skipped, so always set a small business-approved ceiling. LOG_VERBOSITY verbose helps diagnose rejected fields but can expose sensitive source values in notices and logs. Reconcile the COPY count against source, accepted, and quarantined counts; use fail-fast loading when you cannot account for every rejected record.
COPY app.stage_order (external_id, ordered_at, amount)
FROM STDIN
WITH (
FORMAT csv,
HEADER match,
ON_ERROR ignore,
REJECT_LIMIT 20,
LOG_VERBOSITY verbose
);Export an approved projection, not an entire table
COPY TO requires SELECT privilege and applies relevant SELECT policies when RLS is active, but a table owner, superuser, or BYPASSRLS exporter can still expose every row. Use a query with explicit columns and stable ordering, run as a least-privilege export role, and store the result with restrictive local permissions and retention. CSV opened in spreadsheet software can interpret cells beginning with =, +, -, or @ as formulas; neutralize or avoid spreadsheet delivery when values are user-controlled.
\copy (SELECT external_id, ordered_at, amount FROM app.orders WHERE ordered_at >= DATE '2026-08-01' ORDER BY external_id) TO './orders-2026-08.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')Observe throughput and plan for failure cleanup
Each active COPY backend appears in pg_stat_progress_copy, though some counters can be zero when the source cannot report them. COPY FROM appends rows and takes a ROW EXCLUSIVE table lock; indexes, triggers, constraints, WAL, and replicas can dominate runtime. A failed COPY leaves its inserted tuples invisible but occupying space until vacuum can reclaim them. Benchmark realistic batches, watch WAL and replica lag, analyze after major data changes, and prefer restartable chunks over a single unbounded production load.
SELECT pid, datname, relid::regclass AS relation,
command, type, bytes_processed, bytes_total,
tuples_processed, tuples_excluded
FROM pg_catalog.pg_stat_progress_copy
ORDER BY pid;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.