asUpdateAndReturnGeneratedKey does not work with postgresql returning id

[tbohnen]

When we execute a postgresql insert statement that does returning id, we get the following exception:
A result was returned when none was expected

The below example will throw the exception: (Untested but created from other code that did)

 val insertQuery = "insert into test(name)  values (?) RETURNING id;"

using(kotliquery.sessionOf(HikariCP.dataSource())){session ->
                val query = queryOf(insertQuery,
                        "test name",
                ).asUpdateAndReturnGeneratedKey
                session.run(query)
        }

Stacktrace:

org.postgresql.util.PSQLException: A result was returned when none was expected.

at org.postgresql.jdbc.PgStatement.getNoResultUpdateCount(PgStatement.java:254)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:158)
at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:17)
at kotliquery.LoanPattern.using(LoanPattern.kt:11)
at kotliquery.PackageKt.using(package.kt:30)
at kotliquery.Session.updateAndReturnGeneratedKey(Session.kt:157)
at kotliquery.action.UpdateAndReturnGeneratedKeyQueryAction.runWithSession(UpdateAndReturnGeneratedKeyQueryAction.kt:9)
at kotliquery.Session.run(Session.kt:175)

[seratch]

A result was returned when none was expected

I've never seen the case no value returned is expected when using RETURN_GENERATED_KEYS for a JDBC statement. What is the definition of your database table? If you really need the support for your case, you may need to work on pull requests. I myself don't have any plans to dedicate my time to work on it.

[seratch]

As you may already know, the library supports returning id syntax when the id column is serial type or whatever with auto-generated value (although the CI builds don't have tests using PostgreSQL/MySQL, I've made sure that it works with PostgreSQL/MySQL). That'd be enough as far as I know.

[tbohnen]

Thanks for the response.
ID definition is: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Which is similar to serial, just new syntax (for the most part) in postgresql 10.
So given the above code and the id, is it you're understanding that it should work? If so, I can dig and see why not.

[tbohnen]

Just to add: If I remove the returning id; it throws an exception:

caused by: org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.
	at org.postgresql.jdbc.PgResultSet.checkResultSet(PgResultSet.java:2768)
	at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2072)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
	at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:161)
	at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:17)
	at kotliquery.LoanPattern.using(LoanPattern.kt:11)
	at kotliquery.PackageKt.using(package.kt:30)
	at kotliquery.Session.updateAndReturnGeneratedKey(Session.kt:157)
	at kotliquery.action.UpdateAndReturnGeneratedKeyQueryAction.runWithSession(UpdateAndReturnGeneratedKeyQueryAction.kt:9)
	at kotliquery.Session.run(Session.kt:175)

[seratch]

@tbohnen I've never checked if the library works with the column definition yet.

[nieldw]

@tbohnen @seratch A workaround is to use asSingle or just single.

val insertQuery = "insert into test(name)  values (?) RETURNING id;"

using(kotliquery.sessionOf(HikariCP.dataSource())) { session ->
    val query = queryOf(insertQuery, "test name")
    session.single(query) { row -> row.int("id") }
}

[Saxintosh]

Release: 1.3.0 with Postgres

I got this problem but then resolved opening the session setting returnGeneratedKey = true

	using(sessionOf(datasource, true)) { session ->
		...
	}

... no needs to use INSERT .... RETURNING ID

hope it can be useful!