PostgreSQL 18 COPY REJECT_LIMIT: Safer Bulk Imports

PostgreSQL 18 makes bulk imports less all-or-nothing. The release adds REJECT_LIMIT to COPY FROM when ON_ERROR is set to ignore, so an import can tolerate a known number of bad rows but still fail if the file is worse than expected.

That is a small syntax change with a meaningful operational effect. Before this, ON_ERROR ignore could be useful for messy CSV loads, but it also created a dangerous default: if you forgot to watch the skipped-row count, PostgreSQL could keep discarding conversion failures for the whole file. REJECT_LIMIT gives the import a circuit breaker.

What changed in PostgreSQL 18

PostgreSQL 17 introduced COPY FROM ... ON_ERROR ignore for text and CSV imports. When a column value could not be converted to the target data type, PostgreSQL could discard that input row and continue instead of aborting the whole command. PostgreSQL 18 keeps that behavior and adds a maximum tolerated error count.

The new option is available only with ON_ERROR = ignore, and the documented value must be a positive bigint. If the input causes more conversion errors than the limit, the COPY command fails even though ON_ERROR is still set to ignore. If no limit is specified, ON_ERROR ignore continues to allow an unlimited number of conversion errors, which is rarely the right production default for customer or billing data.

PostgreSQL 18 also adds a silent level for LOG_VERBOSITY in this path. With the default behavior, PostgreSQL emits a notice with the ignored row count when rows are discarded. With verbose, it can report line and column information for discarded rows. With silent, messages about ignored rows are suppressed. That can be useful for noisy automation, but it makes external logging and validation more important.

COPY staging_orders (account_id, order_id, total_cents, ordered_at)
FROM STDIN
WITH (
  FORMAT csv,
  HEADER true,
  ON_ERROR ignore,
  REJECT_LIMIT 25,
  LOG_VERBOSITY verbose
);

This is not a replacement for validation. It is a better failure mode for imports where a small amount of bad source data is expected and the rest of the file is still valuable.

Why it matters for managed PostgreSQL teams

Bulk imports are often operationally awkward because the database command is only one part of the workflow. A team may receive a customer export, load a partner feed, backfill analytics rows, or import historical transactions during a migration. The input file may contain a few malformed timestamps, numeric strings with currency symbols, or empty values that do not match the destination type.

Without row-level tolerance, the first bad value stops the whole import. That is safe but frustrating when the file has millions of good rows and a handful of obvious defects. With unlimited ignore behavior, the import completes but may hide a much larger quality problem. A reject limit is the middle ground: it lets the planned cleanup continue, while still making the import fail loudly when the source data is not the file you thought you had.

For a managed database, that distinction matters because import windows are finite. Long-running loads consume I/O, generate WAL, grow tables, and may compete with application traffic. Failing after row three because of one dirty field is annoying. Succeeding after silently discarding 40% of the file is worse. A bounded import policy makes the runbook clearer before anyone starts the load.

Use a staging table first

The safest pattern is still to import into a staging table, validate, and then insert into production tables deliberately. REJECT_LIMIT helps with conversion errors, but it does not tell you whether the remaining rows are semantically correct. A date can be valid but in the wrong timezone. A tenant identifier can parse correctly but point to the wrong account. A price can be numeric but use dollars when the table expects cents.

A practical workflow is to load into a staging table whose columns match the intended types closely enough to catch obvious conversion errors. Set a small reject limit based on the import's size and risk. After the load, run validation queries that count missing required fields, unknown foreign keys, duplicate business keys, impossible timestamps, and suspicious value ranges. Only then promote the clean rows into the destination table in a transaction or controlled migration step.

For production systems, pair the import with a restore point or backup routine. ArmorDB users should review backup documentation before large loads, and teams that import through application workers should also make sure connection pressure is not the real bottleneck; the PgBouncer guide is relevant when many workers try to load data at once.

Choosing the reject limit

The right reject limit is a policy decision, not a performance tuning knob. For a recurring feed, start from historical data quality. If a nightly file normally has zero to two malformed rows, a limit of ten might allow ordinary noise while failing quickly on a broken upstream change. For a one-time migration from an old system, the limit may be higher, but every skipped row should still be captured by logs or a parallel reconciliation process.

Avoid percentage-only thinking unless your automation calculates it before running COPY. PostgreSQL's option is a count, so REJECT_LIMIT 100 means something very different for a 500-row import than for a 50-million-row import. In many SaaS migrations, the safest approach is to split files by tenant, source object, or date range. Smaller batches make rejected-row counts easier to interpret and make reruns less painful.

What errors does it cover?

The documented behavior is specifically about errors converting a column's input value into its data type. That means REJECT_LIMIT is most useful for malformed values such as bad dates, invalid numeric input, or text that cannot be parsed for the destination column. It should not be treated as a general constraint-error handler or a data-quality engine.

If you need to handle uniqueness conflicts, foreign-key problems, or business validation, use a staging table and explicit SQL checks. That separation is healthy. COPY can get raw data into PostgreSQL efficiently. Your schema and validation queries should decide what is allowed to become production state.

Takeaway

PostgreSQL 18's REJECT_LIMIT makes COPY FROM ... ON_ERROR ignore much safer for real import workflows. Use it when a small number of conversion failures is acceptable, set the limit low enough to catch a bad file, keep row-skipping visible through notices or logs, and validate in staging before promoting data. The feature is most valuable when it is part of an import runbook, not when it is used to make dirty data disappear.

Sources and further reading

• PostgreSQL 18 release notes for COPY changes: https://www.postgresql.org/docs/18/release-18.html
• PostgreSQL 18 COPY documentation, including ON_ERROR, REJECT_LIMIT, and LOG_VERBOSITY: https://www.postgresql.org/docs/18/sql-copy.html
• PostgreSQL 17 release notes for the original ON_ERROR ignore behavior: https://www.postgresql.org/docs/17/release-17.html
• PostgreSQL COPY progress reporting through pg_stat_progress_copy: https://www.postgresql.org/docs/18/progress-reporting.html#COPY-PROGRESS-REPORTING