In PostgreSQL 18, what do COPY's ON_ERROR ignore and REJECT_LIMIT options skip in a dirty CSV load, and what still aborts it?
answer
- 17 added it, 18 capped it
- only type-conversion failures
- constraints are not conversions
- a counter in pg_stat_progress_copy
basics
~20 sON_ERROR ignore (PostgreSQL 17) discards rows whose values fail their column type's input conversion; REJECT_LIMIT (18) fails the COPY once more than N rows are skipped. Wrong field counts and constraint violations still abort the whole load.
solid answer
~50 sBy default `COPY FROM` runs as `ON_ERROR stop`: the first bad value aborts the statement and nothing is kept. `ON_ERROR ignore`, added in 17 for the `text` and `csv` formats, catches **data type conversion errors** (`abc` in a `bigint`, `2026-02-30` in a `date`), drops that whole row, carries on, and ends with a NOTICE giving the count; `LOG_VERBOSITY verbose` adds a NOTICE per row with line number and column, and 18's `silent` suppresses them. PostgreSQL 18's `REJECT_LIMIT n` turns "skip everything" into "skip up to n": the row that pushes the count past n fails the statement with SQLSTATE `22P02`, rolling back everything loaded. Errors outside type conversion stay fatal, including too many or too few fields (`22P04`), `NOT NULL`, `CHECK`, unique and foreign-key violations, and trigger errors. Skipped rows are counted live in `pg_stat_progress_copy.tuples_skipped`.
code
sql · 10 linesCOPY census_staging
FROM '/srv/import/census_2026.csv'
WITH (FORMAT csv, HEADER,
ON_ERROR ignore,
REJECT_LIMIT 5000,
LOG_VERBOSITY verbose);
-- From another session while it runs
SELECT relid::regclass, tuples_processed, tuples_skipped
FROM pg_stat_progress_copy;go deeper
Recall that COPY normally stops at the first bad value, and that PostgreSQL 17 added ON_ERROR ignore to skip rows with values that do not fit their column types.
Explain that only type-conversion failures are skipped, whole rows are dropped, REJECT_LIMIT in 18 caps the count, and how NOTICEs and tuples_skipped report it.
Show production judgement: set a REJECT_LIMIT that turns a broken export into a loud failure, keep staging constraint-free, and keep rejected rows when an audit needs them.
Treat skip-and-continue as a data-quality policy: decide who owns the tolerance, where rejected records are kept, and how a silent partial load is detected downstream.
## The default: one bad value loses the whole load `COPY FROM` is a single SQL statement, so it is atomic. With the default error action, **`ON_ERROR stop`**, the first value that cannot be converted (a stray `N/A` in a numeric column, a `31/02/2026` in a `date` column) raises an error and the statement fails. Rows inserted before the failure are rolled back; the manual notes they stay on disk as dead rows until `VACUUM` reclaims the space, which on a 50-million-row census file can be most of a table's worth of bloat. Before PostgreSQL 17 the usual remedies were cleaning the file first or loading every column as `text` and converting in SQL. ## ON_ERROR ignore (PostgreSQL 17) `ON_ERROR ignore` tells `COPY FROM` to treat **data type conversion failures as soft errors**: - Each field is passed to its column type's input function in a mode that reports failure instead of raising an error. When one field fails, **the whole row is discarded**, not just that value, and loading continues with the next line. - It applies only to `COPY FROM` and only to the `text` and `csv` formats. With `FORMAT binary` the server refuses: `only ON_ERROR STOP is allowed in BINARY mode`; on `COPY TO` the option is rejected outright. - At the end, if anything was dropped, a NOTICE reports it, for example `3 rows were skipped due to data type incompatibility`. Rejected rows are **not saved anywhere**: there is no reject file or error table. If you need them, turn on `LOG_VERBOSITY verbose`, which emits one NOTICE per discarded row naming the input line number, the column and the offending value. ## REJECT_LIMIT (PostgreSQL 18) Unbounded skipping is dangerous: a file exported with the wrong date format would load with every row silently dropped. PostgreSQL 18 adds **`REJECT_LIMIT maxerror`**: 1. It must be a positive `bigint`, and it requires `ON_ERROR ignore`; without it the server answers `COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE`. 2. Up to `maxerror` bad rows are skipped. The row that pushes the count past the limit raises `skipped more than REJECT_LIMIT (n) rows due to data type incompatibility`, SQLSTATE `22P02`. 3. That error fails the **whole statement**, so every row already loaded by this `COPY` is rolled back as well. Without `REJECT_LIMIT`, `ON_ERROR ignore` tolerates an unlimited number of errors. ## What still aborts the load | Problem in the input | Skipped by `ON_ERROR ignore`? | What happens | |---|---|---| | Value cannot be converted to the column type | **yes** | row dropped, counted | | More fields than columns | no | `extra data after last expected column`, `22P04` | | Fewer fields than columns | no | `missing data for column "..."`, `22P04` | | NULL in a `NOT NULL` column | no | `23502`, statement fails | | `CHECK` violation | no | `23514`, statement fails | | Duplicate key in a unique index | no | `23505`, statement fails | | Missing referenced row | no | `23503`, statement fails | The reason is where the check happens. Conversion runs while a line is parsed; constraints, unique indexes, foreign keys and triggers run when the converted row is inserted, and those raise ordinary errors that `ON_ERROR` never sees. ## Seeing what was dropped - **The closing NOTICE** gives the total, unless `LOG_VERBOSITY silent` (new in 18) suppresses it. - **`LOG_VERBOSITY verbose`** gives one NOTICE per skipped row: `skipping row due to data type incompatibility at line 18245 for column "birth_date": "2026-02-30"`. - **`pg_stat_progress_copy.tuples_skipped`** (added in 17) counts dropped rows while the load runs, beside `tuples_processed` and `tuples_excluded` (rows filtered by a `COPY ... WHERE` clause). ## A pattern for the census load 1. Decide the tolerance before you start: if 0.01% bad rows is acceptable in 50 million, that is `REJECT_LIMIT 5000`, and anything worse means a broken export that should fail loudly. 2. Keep the **staging table free of constraints**, so that only conversion errors can occur during `COPY`; enforce `NOT NULL`, uniqueness and references when moving rows into the real table. 3. When rejected rows must be kept, load every column as `text` instead and test values in SQL, for example with `pg_input_is_valid()`, routing failures to a quarantine table.
- Why does PostgreSQL's ON_ERROR ignore not skip a row that violates a NOT NULL constraint?It catches only soft errors raised while converting input text to the column's type. `NOT NULL`, `CHECK`, unique and foreign-key checks run later, when the converted row is inserted, and raise ordinary errors that abort the statement. Keep the staging table constraint-free and enforce those rules when moving rows to the final table.
- How do you watch a long PostgreSQL COPY with ON_ERROR ignore while it runs?Query `pg_stat_progress_copy` from another session: `tuples_processed` counts rows loaded, `tuples_skipped` counts rows discarded by `ON_ERROR`, `tuples_excluded` counts rows filtered by a `WHERE` clause, and `bytes_processed` against `bytes_total` shows file progress when the size is known.
- ON_ERROR ignore also skips rows that break a unique or foreign key constraint.
- In PostgreSQL 18, ON_ERROR ignore keeps the row and stores NULL in the bad column.
- Exceeding REJECT_LIMIT keeps the rows that were loaded before the limit was hit.
- REJECT_LIMIT can be used on its own, without setting ON_ERROR.
- ON_ERROR ignore saves rejected lines to an error table for later review.
- ON_ERROR ignore is available for binary-format COPY as well.
Assumes PostgreSQL 18: ON_ERROR ignore and LOG_VERBOSITY arrived in 17; REJECT_LIMIT and LOG_VERBOSITY silent in 18.