Handling BatchUpdateException using withBatch

Question

I am looping over a set of objects inserting them into my PostgreSQL database like this:

sql.withTransaction {
    try {
        sql.withBatch(1000, 'insert into category (id, version, name, parent_id) values (:id, :version, :name, :parent_id);') { stmt ->
            categoryInserts.each {
                try {
                    stmt.addBatch([id: it.id, version: 0, name: it.name, parent_id: it.parent?.id])
                } catch (SQLException e) {
                    log.error("Category ${it.name} with parent ${it.parent?.id} could not be inserted.")
                }
            }
         }
      } catch (BatchUpdateException e) {
          log.error("Categories could not be inserted.")
      }
      sql.commit()
}

There is a unique constraint (name, parent_id) on the category table. If the constraint is violated the program catches a BatchUpdateException and subsequent objects are not inserted. Unfortunately the exception is not thrown while executing the addBatch-method.

Is there any way to continue with the withBatch statement, so that duplicates are ignored and new records are inserted? It seems strange to me that a batch-Insert does not offer this behaviour.

Possible duplicate of http://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already — Alexandros, Feb 03 '15 at 12:24

score 0 · Accepted Answer · answered Feb 03 '15 at 14:52

I was able to solve this with the post mentioned in Alexandros comment. The solution now looks like this:

sql.withTransaction {
    try {
        sql.withBatch(1000, 'insert into category (id, version, name, parent_id) ' +
        'select :id, :version, :name, :parent_id ' +
        'where not exists (select name, parent_id from category where name = :name and parent_id = :parent_id);') { stmt ->
            categoryInserts.each {
                try {
                    stmt.addBatch([id: it.id, version: 0, name: it.name, parent_id: it.parent?.id])
                } catch (SQLException e) {
                  log.error("Category ${it.name} with parent ${it.parent?.id} could not be inserted.")
                }
            }
        }
    } catch (BatchUpdateException e) {
        log.error("Categories could not be inserted.", e)
    }

    sql.commit()
}

Be aware that this is solved with the postgresql dialect of SQL. For other DBMSs it might be a useful approach to use a SQL-procedure in the withBatch-method.

If someone knows a way to do this with a standard-SQL, please give me a hint.

Handling BatchUpdateException using withBatch

1 Answers1

Linked