Row-by-row INSERT is the slowest way to get data into Postgres. Every statement pays parse, plan, WAL, and network round-trip costs. COPY skips most of that overhead, which is why a nightly CSV drop — energy meter reads, well-site sensor exports, vendor feeds — belongs in COPY and not in a loop.

This tutorial walks the whole path: creating a target table, loading a file from the server and from your laptop, surviving bad rows, tuning the load, and verifying what actually landed. Examples assume PostgreSQL 16 or newer; behavior that differs by version is called out.

What you need before you start

• A CSV file and a table whose column types match it.
• Membership in pg_read_server_files if you want COPY to read a file that lives on the database server. Otherwise you use \copy (Step 3), which needs no elevated role.
• Patched client drivers if an application, not psql, does the loading. psqlODBC 18.00.0005 fixes CVE-2026-109448 and PostgreSQL JDBC 42.7.14 carries multiple CVE fixes. No tuning option compensates for a vulnerable driver.

Step 1 — Create a target table, ideally a staging table

Index maintenance and constraint checks are the most expensive part of a bulk load. Load into a staging table that has none of them, then move the data with a single INSERT ... SELECT.

CREATE UNLOGGED TABLE meter_reads_staging (
  meter_id     text,
  read_at      timestamptz,
  kwh          numeric(12,4),
  raw_payload  jsonb
);

UNLOGGED skips WAL for the table, and it is not replicated to standbys, so a crash truncates it. For a staging table you can reload, that is usually a good trade. Just remember to ALTER TABLE ... SET LOGGED on the table that keeps the data, or you will meet the downside after the next restart.

Step 2 — Load a file that sits on the database server

COPY meter_reads_staging (meter_id, read_at, kwh, raw_payload)
FROM '/var/lib/postgresql/imports/meter_reads.csv'
WITH (FORMAT csv, HEADER MATCH, ENCODING 'UTF8');

Server-side COPY is the fastest path because the bytes never cross the network. Two things to know. First, HEADER true only skips the first line — it does not match columns by name, so the file’s column order must match the list you wrote. Use HEADER MATCH instead and Postgres verifies the header against your column list and errors out if they disagree, which kills the most common silent data-corruption bug in bulk loading. Second, like any SQL statement COPY runs in a transaction, so one bad row at line 4,000,000 rolls back the entire file unless you handle errors (Step 4).

Step 3 — Load a file from your laptop with \copy

\copy meter_reads_staging (meter_id, read_at, kwh)
FROM '/Users/bob/exports/meter_reads.csv'
WITH (FORMAT csv, HEADER MATCH, FORCE_NULL (kwh))

\copy is a psql meta-command rather than SQL: the client reads the file and streams it over the connection. That means no server file access and no special role, which makes it the only option on managed Postgres such as RDS and Aurora. It is also the version to put in a script, because it works from psql -f load.sql and from cron.

Step 4 — Decide what happens to bad rows

PostgreSQL 17 added ON_ERROR, which turns an abort-everything load into a best-effort load that reports its failures:

COPY meter_reads_staging FROM '/var/lib/postgresql/imports/meter_reads.csv'
WITH (FORMAT csv, HEADER MATCH, ON_ERROR ignore, LOG_VERBOSITY verbose);
-- COPY 998745
-- NOTICE: 1255 rows were skipped due to data type incompatibilities

Every skipped row is written to the server log with its context, so the log becomes your reject file and you can reload the rejects after fixing them. On versions before 17 your options are a text-typed staging table plus a validating INSERT ... SELECT, or a WHERE clause on the COPY FROM to filter rows out as they load.

Step 5 — Make the load faster

• Build indexes after the load. Loading a million rows into a table with three indexes performs a million index insertions. Load bare, then CREATE INDEX, which sorts once.
• Turn off synchronous commit for the session: SET synchronous_commit = off; A crash can lose the last few hundred milliseconds of commits, but cannot corrupt the database — a fair trade for a reloadable staging load.
• Raise max_wal_size for the load so it does not trigger mid-load checkpoints, and give maintenance_work_mem room before building the post-load indexes.
• Split the file. A single COPY is single-threaded. Run four sessions against four partitions or four distinct files and let the server use the cores you paid for.
• Freeze rows as you load when the table is fresh: WITH (FREEZE true) writes rows already frozen and saves a later vacuum pass. It only works if the table was created or truncated in the same subtransaction, and it does not work on partitioned or foreign tables.

Step 6 — Verify the load

Compare more than row counts. Check a control total such as SELECT sum(kwh) against the source file, and hunt for duplicate natural keys with SELECT meter_id, read_at, count(*) FROM meter_reads_staging GROUP BY 1,2 HAVING count(*) > 1;. While the load is running, SELECT * FROM pg_stat_progress_copy; (PostgreSQL 16+) shows bytes processed, tuples processed, and tuples excluded — enough to tell a slow load from a stuck one.

Then run ANALYZE meter_reads_staging;. Without it the planner is still working from pre-load statistics, and the first query after a bulk load can pick a terrible plan. That is the classic version of this complaint: the load was fast, the report is slow.

Gotchas that cost the most time

• In CSV mode an unquoted empty field is NULL, but a quoted empty string is an empty string and will fail on a numeric or date column. FORCE_NULL (kwh) converts those to NULL.
• You cannot load into a generated column. Leave it out of an explicit column list; without a column list Postgres skips generated columns automatically.
• COPY FROM fires triggers and check constraints, so one audit trigger can silently double your load time.
• Timestamps with no time zone are interpreted in the session’s TimeZone. Set it explicitly in scheduled loaders.
• A failed COPY leaves the rows it inserted behind as dead tuples that still occupy disk until VACUUM runs. After a load that died halfway through a big file, vacuum the table before you retry.

The pattern that holds up in production

Use COPY TO for exports, COPY FROM or \copy for loads, and pg_restore -j when you are moving an entire database. The workflow worth standardizing is deliberately boring: land the file in a staging table, validate it, move it with one statement, then index and analyze. Done that way, a failed night is a re-run rather than an incident.

Full option list in the PostgreSQL COPY documentation, including the PROGRAM option if you want to pipe a compressed file straight in with gzip -dc.