Greetings,
I believe I found a defect in the latest postgresql driver (42.1.4) which did not occur with the earlier version 9.3-1102-jdbc41.
For a PreparedStatement used for inserting with the option RETURN_GENERATED_KEYS, if the sql contains a "with" query, then the generated keys are not returned.
For example:
---
create table testjdbckey (
id SERIAL PRIMARY KEY,
mydate date
);
---
String sql = "WITH t AS (SELECT NOW() AS thedate) INSERT INTO testjdbckey (mydate) VALUES ((SELECT thedate FROM t))";
Connection conn =...;
PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
// Will not enter the while loop with postgresql-42.1.4
System.out.println("Key: " + rs.getInt(1));
}
---
Of course if I remove the "with" and inline my subrequest it works. This used to work perfectly with the 9.3-1102-jdbc41 driver.
What do you think?
Best Regards,