The column index is out of range: 1, number of columns: 0

Question

i updated the UPSERT_DELETE_JOB_COUNTERS. Now i think the "upsert" works with the new Query =) Or what do you think?

regards, Peter

`private static final String UPSERT_DELETE_JOB_COUNTERS =
        "UPDATE rating.delete_job_counters SET delete_count = delete_count + ? WHERE fee_group_id = ? AND delete_job_id = ?; "+
        "INSERT INTO  rating.delete_job_counters(delete_job_id, fee_group_id, delete_count ) "+
        "SELECT ?, ? , ? " +
        "WHERE NOT EXISTS (SELECT 1 FROM rating.delete_job_counters  WHERE fee_group_id = ? AND delete_job_id = ?); ";

@Override
public Boolean insertIntoDeleteStatistic(final CachedRowSet deletedEntries,
                                      Long deleteJobId,
                                      Transaction transaction) throws SQLException {
    boolean finish = true;
    if (deletedEntries.size() == 0) {
        return finish;
    }
    final Connection connection = transaction.getConnection();
    final Timer executeTimer = Timer.start("Insert took ");
    try {
        PreparedStatement pstmt = connection.prepareStatement(UPSERT_DELETE_JOB_COUNTERS);
        while (deletedEntries.next()) {
            pstmt.setInt(1, deletedEntries.getInt(2));
            pstmt.setInt(2, deletedEntries.getInt(1));
            pstmt.setLong(3, deleteJobId);
            pstmt.setLong(4, deleteJobId);
            pstmt.setInt(5, deletedEntries.getInt(1));
            pstmt.setInt(6, deletedEntries.getInt(2));
            pstmt.setInt(7, deletedEntries.getInt(1));
            pstmt.setInt(8, deletedEntries.getInt(2));
            pstmt.execute();
        }...

`

Answer 1

You cannot use a parameterized java.sql.PreparedStatement with a DO statement.

Why don't you use INSERT ... ON CONFLICT DO UPDATE (available since PostgreSQL 9.5)?

For older versions of PostgreSQL, you'll probably have to code an “endless loop” like this pseudocode sample:

while (1) {
   INSERT ...
   if (insert went ok) break;
   UPDATE ...
   if (update changed one row) break;
}