PostgreSQL Error Handling – Report Offending Rows When Multi-Row INSERT Fails

error handlinginsertpostgresql

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?

Best Answer

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?

Related Solutions

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.

Insert Multiple Rows and Columns in PostgreSQL 9.2

If it's guaranteed (like you commented on your previous question) that each SELECT returns exactly 1 row, the simplest way to combine both would be to just nest one SELECT within the other:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Alisha%'
       AND    name_last LIKE 'Dson%') AS foo  -- alias irrelevant
FROM   junk.wm_260_billables2
WHERE  info ILIKE '%Alisha%';

If there can be 0 - n results per SELECT, it gets more complex. You would have to define what the result should look like for each possible combination.

Note that you get no row (nothing inserted) if the outer SELECT finds nothing, but a NULL value for the second column if the nested SELECT finds nothing.

Or you could just use two subqueries:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
VALUES (
  (SELECT billable_id
   FROM   junk.wm_260_billables2
   WHERE  info ILIKE '%Alisha%')
, (SELECT id
   FROM   public.ja_mobiusers
   WHERE  name_first LIKE 'Alisha%'
   AND    name_last LIKE 'Dson%')
   );

Now you get NULL for each column where nothing is found, and an error if more than one row is found.

I seriously doubt that an ILIKE expression can guarantee a single result row.

Aside: your constraint would be faster and cleaner this way - though more verbose:

ALTER TABLE dm.billables_links ADD CONSTRAINT cc_one_and_only_one_target
CHECK (customer_id IS NOT NULL AND role_id IS     NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS NOT NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS     NULL AND mobiuser_id IS NOT NULL)

Best Answer

Related Solutions

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

Insert Multiple Rows and Columns in PostgreSQL 9.2

Related Question