UPDATE with RETURNING: Not able to retrieve ResultSet

Question

I’ve tested the following UPDATE-RETURNING statement in my PostgreSQL client, and it correctly updates and returns the updated rows.

However, in Java, I’m not able to retrieve the ResultSet. statement.execute() and statement.getMoreResults() always return false and statement.getResultSet() returns null, always.

Am I missing something here?

PreparedStatement statement = this.prepareStatement(
    "WITH temp AS (" +
    "    SELECT id" +
    "    FROM mytable " +
    "    LIMIT 5 "
    ") " +
    "UPDATE mytable " +
    "SET updated = NOW() " +
    "FROM temp " +
    "WHERE temp.id = mytable.id " +
    "RETURNING mytable.data"
);

boolean hasResult = statement.execute();

if (!hasResult) {
    hasResult = statement.getMoreResults();
    // hasResult is still false
    // statement.getResultSet() still returns null
} else {
    // statement.getUpdateCount() returns the correct count of updated rows
}

`executeQuery()` throws an exception when using with an UPDATE rather than SELECT. — Rob, Oct 27 '21 at 09:20
what is the exception? Are you sure the sql is a valid statement? — bgore, Oct 27 '21 at 09:31
The exception when using `executeQuery()` instead of `execute()` is: *org.postgresql.util.PSQLException: No results returned by the query.* And yes, the query works if I copy/paste it and do a SQL request in the SQL console. The rows also get updated accordingly when using `execute()`. But the result is not accessible. — Rob, Oct 27 '21 at 09:38
Looking at [Get RETURNING value from Postgresql via Java](https://stackoverflow.com/questions/23691715/get-returning-value-from-postgresql-via-java), trying `getResultSet` without checking the return value might work (though that would indicate a bug in the implementation, as that wouldn't be JDBC compliant). — Mark Rotteveel, Oct 27 '21 at 10:11
Yeah, I’ve seen that post as well and tried accessing `getResultSet()` directly. It’s `null`. I’m wondering if it has anything to do with the query itself. Maybe the combination WITH … UPDATE … RETURNING triggers some weird behavior in Java. I’ll confirm this. — Rob, Oct 27 '21 at 10:15

score 0 · Answer 1 · answered Oct 27 '21 at 10:39

The return code from execute() indicates if there is a result available immediately or not.

getMoreResults() tells you if there is another result set after processing the first one that was indicated through the return value of execute().

So the following will work with your UPDATE statement:

boolean hasResult = statement.execute();
if (hasResult) {
  ResultSet rs = statement.getResultSet();
  ... process the result ...
}

Note that you get a ResultSet even if the UPDATE didn't actually change any rows (e.g. because the WHERE clause didn't match anything). The result set will return false immediately when you call next().

You only need to check getMoreResults() if you expect more than one ResultSet. Then you would need a loop:

boolean hasResult = statement.execute();
while (hasResult) {
  ResultSet rs = statement.getResultSet();
  .... process the result ...

  // check if there is another one
  hasResult = statement.getMoreResults();
}

In theory you also need to check getUpdateCount() in the loop as well. that would be needed if the database returns a mixture of result sets and update counts. Not sure if Postgres actually supports that though.

Thanks for your answer. `statement.execute()` always returns `false` in my case above. That’s why `statement.getResultSet()` returns `null`, while `statement.getUpdateCount()` returns a valid number. Probably JDBC can either return an update count or the ResultSet, but not both? — Rob, Oct 27 '21 at 11:19
Are you maybe using an outdated JDBC driver? The code definitely works as described. I'm using it myself in several projects — , Oct 27 '21 at 11:22
That’s interesting. Are you also using it with the combination of WITH … UPDATE … RETURNING? — Rob, Oct 27 '21 at 14:51

score 0 · Accepted Answer · answered Jun 15 '22 at 13:12

Well, I wasn’t really able to solve this properly. However, I ended up using a workaround by wrapping the whole SQL request with a SELECT statement:

PreparedStatement statement = this.prepareStatement(
    "WITH results AS (" +
        "WITH temp AS (" +
        "    SELECT id" +
        "    FROM mytable " +
        "    LIMIT 5 "
        ") " +
        "UPDATE mytable " +
        "SET updated = NOW() " +
        "FROM temp " +
        "WHERE temp.id = mytable.id " +
        "RETURNING mytable.data" +
    ") " +
    "SELECT * FROM results"
);

ResultSet result = statement.executeQuery();

while (result.next()) {
    // Work with result
}

UPDATE with RETURNING: Not able to retrieve ResultSet

2 Answers2