Skipping Bad Rows During a PostgreSQL CSV Import Safely

A load that stops at the first bad value is correct and sometimes inconvenient. If the file has 3 million good rows and 12 bad ones, you want the 3 million and a list of the 12. PostgreSQL 17 added the first half of that, and PostgreSQL 18 the second. This article runs the options on a five row file, shows what each prints, and spends most of its length on the limits, because a feature that skips data quietly deserves suspicion. The load procedure it belongs to is in the PostgreSQL loading guide.

The test file

Five customers. Row 2 has the date not-a-date, and row 3 has the balance abc. The other three are valid.

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,Bo Chen,[email protected],not-a-date,10.00
3,Cy Dubois,[email protected],2025-03-01,abc
4,Dee Evans,[email protected],2025-03-02,5.00
5,Eli Fox,[email protected],2025-03-03,6.00

The default: stop at the first error

Without any option, COPY fails on the first bad value and rolls everything back. The error names line 3, column signup_date, and the table is empty afterwards:

psql -c "\copy customers FROM 'mixed-bad.csv' WITH (FORMAT csv, HEADER true)" 2>&1; psql -qAt -c "SELECT 'rows loaded: ' || count(*) FROM customers"

ERROR:  invalid input syntax for type date: "not-a-date"
CONTEXT:  COPY customers, line 3, column signup_date: "not-a-date"
rows loaded: 0

That is the baseline, and it is the right behavior when you cannot tolerate a missing row. The rest of the article is about the cases where you can.

ON_ERROR ignore

ON_ERROR ignore tells COPY to skip a row it cannot convert and carry on:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'mixed-bad.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)" -c "SELECT id, name FROM customers ORDER BY id" 2>&1

NOTICE:  2 rows were skipped due to data type incompatibility
COPY 3
 id |   name    
----+-----------
  1 | Ana Silva
  4 | Dee Evans
  5 | Eli Fox
(3 rows)

Three rows loaded: ids 1, 4 and 5. The notice says 2 rows were skipped due to data type incompatibility, and COPY 3 is the count of rows inserted. If your script only checks the exit status, this load looks like a success, and that is the first reason to be careful: a bad file now gives a successful command. The notice goes to the client's message stream, so a job that captures only standard output will not see it.

Which rows were skipped

The notice gives a count and nothing else. To see each skipped row, set LOG_VERBOSITY verbose:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'mixed-bad.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, LOG_VERBOSITY verbose)" 2>&1

NOTICE:  skipping row due to data type incompatibility at line 3 for column "signup_date": "not-a-date"
NOTICE:  skipping row due to data type incompatibility at line 4 for column "balance": "abc"
NOTICE:  2 rows were skipped due to data type incompatibility
COPY 3

Each notice names the line, the column and the value: skipping row due to data type incompatibility at line 3 for column "signup_date": "not-a-date", and the same for line 4 and "abc". That is enough to go back to the file and look. It is a message, not a record. Nothing is written to a table or file, so if you want the rejected rows, capture the notices from standard error and parse them, or do the load another way. PostgreSQL 18 also added a silent level that suppresses the per-row messages, which I do not use for the reason above.

REJECT_LIMIT: skip, but not forever

An unbounded skip means a file that is entirely wrong, such as one in the wrong format, loads zero rows and reports success. REJECT_LIMIT puts a ceiling on it:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'mixed-bad.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 1)" 2>&1; psql -qAt -c "SELECT 'rows loaded: ' || count(*) FROM customers"

ERROR:  skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
CONTEXT:  COPY customers, line 4, column balance: "abc"
rows loaded: 0

With REJECT_LIMIT 1 and two bad rows, the load stopped with skipped more than REJECT_LIMIT (1) rows due to data type incompatibility, naming the second offender, and nothing was loaded: rows loaded: 0. The limit applies to the whole command, and exceeding it rolls the load back like any other error. I set it to a small fraction of the expected row count, so the load tolerates the odd bad row and fails on a file that is wrong in bulk. Something like 0.1% of rows is a reasonable first guess for data from a human system, and a much lower number for a machine export, where any bad row is a bug in the source.

What skipping does not reach

The option covers only values that cannot be converted to the column's type. It does not cover a structural problem. A row with an extra field is not a conversion failure, and it still aborts the load:

psql -q -c "TRUNCATE customers"
psql -c "\copy customers FROM 'structural-bad.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)" 2>&1

ERROR:  extra data after last expected column
CONTEXT:  COPY customers, line 3: "2,Bo Chen,[email protected],2025-02-01,10.00,extra"

extra data after last expected column on line 3, with the row printed, even with ON_ERROR ignore in force. Missing fields, an unterminated quote and encoding errors behave the same way. So do constraint violations: with ON_ERROR ignore in force, a duplicate primary key stopped the load with duplicate key value violates unique constraint "customers_pkey" and a missing id stopped it with null value in column "id" ... violates not-null constraint:

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY customers, line 3
ERROR:  null value in column "id" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (null, NoId, [email protected], 2025-01-15, 1.00).
CONTEXT:  COPY customers, line 2: ",NoId,[email protected],2025-01-15,1.00"

Those are errors in the row's shape or in the table's rules, not in a value's syntax, and COPY does not attempt to skip them. The extra data article is about finding those lines.

That limit changes how I use the feature. It handles the case of a few mistyped numbers and dates in an otherwise sound file. It does nothing for a file that is structurally damaged, and a structurally damaged file is exactly the one you should see fail.

A reject table when you need the rows

If you need the rejected rows in a table, to send back or to fix, use a staging table, as in the staging article: load as text, then INSERT the valid rows into the typed table and the rest into a reject table with a reason. That costs a second write of the data and gives you something ON_ERROR ignore cannot, a record. For a very large file, a hybrid is possible: load with ON_ERROR ignore and LOG_VERBOSITY verbose, collect the line numbers from the notices, and extract those lines from the source with sed -n for a second look.

How to tell it worked

Compare three numbers: the records in the file, the COPY n tag and the number in the skipped notice. They must add up: 5 = 3 + 2 here. If a load that used ON_ERROR ignore has a count that does not add up, rows were lost in a way the option did not report, such as a blank line or a header you forgot.

A habit I trust: print the notice count and compare it with a threshold in the script, not only the exit status. A script that sees skipped greater than the tolerated fraction fails the job.

Errors that skipping does not reach

Beyond structure, there are decisions where skipping is wrong. A financial file where every row matters should fail loudly, so use the default. A file whose bad rows are systematic, all the dates in one format you did not expect, should be fixed at the source or cleaned in a staging table; skipping would load only the rows that happen to parse and bias the result. And a file loaded repeatedly into the same table needs an idempotency story that ON_ERROR ignore does not give: rerunning it loads the good rows twice unless a key prevents it.

If your PostgreSQL is older than 17, the option does not exist, and the portable answer is the staging table. The COPY reference lists ON_ERROR, REJECT_LIMIT and LOG_VERBOSITY together with the version notes.