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?


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);
-- 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?

  • I've asked a new question instead of editing this one, so as not to invalidate this existing answer. – das-g Mar 25 '17 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.