Can I let PostgreSQL report the offending rows when a multi-row INSERT fails?

Question

When inserting multiple rows into a table by a single INSERT INTO invocation, and the INSERT statement fails due to reasons specific to some individual rows (e.g. a value has the wrong type for the target column, or a NOT NULL constraint is violated), can I let PostgreSQL report the content of the offending rows?

For UNIQUE constraint or exclusion constraint violation errors, it seems I could use the ON CONFLICT clause to access the offending rows, but is there something similar for other row-specific violations?

Answer 1

When inserting multiple rows

Not sure what exactly you mean. It should already report the content of the offending rows.

# CREATE TABLE foo ( x int );
-- Two rows that work.
# INSERT INTO foo VALUES (1), (2);
INSERT 0 2
-- Three rows that explode.
# INSERT INTO foo VALUES (1), (2), ('a');
ERROR: invalid input syntax for integer: "a"
LINE 1: INSERT INTO foo VALUES (1), (2), ('a');
 ^

What more content are you looking for?

• das-g: I've asked a new question instead of editing this one, so as not to invalidate this existing answer.