[postgres] INSERT and SELECT in one request doesn't work properly

[Mario-Eis]

Consider the following query:

INSERT INTO courses (id, name, institution_id)
VALUES (nextval('course_id'), 'test', 123);

SELECT courses.id, courses.name,  institutions.name AS "institution_name"
FROM courses
         JOIN institutions ON courses.institution_id = institutions.id
WHERE courses.id = currval('course_id');

When executed like this:

 _jdbi.withHandle(
                handle -> handle.createQuery(queryString)
                        .registerRowMapper(ConstructorMapper.factory(Course.class))
                        .mapTo(Course.class)
                        .one()
);

It will result in the followind error: Expected one element, but found none

Expected would be to receive the newly inserted data. This works fine with jdbi and mssql. Also pgAdmin works correct when executing two statements in one go.

I also tried using a WITH statement for the insert. But the result is the same error.

Sidenote: While using the RETURNING keyword would work correct, as you can see, it won't be usable in this case.

[stevenschlansker]

Hi @Mario-Eis , I think this is not a bug. The JDBC api does not guarantee that you can execute multiple SQL statements in one JDBC Statement with a ; - supporting that would be on a vendor-by-vendor basis.

In order to execute multiple statements in a cross-db compatible way, you would use either the Batch or Script api, or make multiple calls with a handle.inTransaction(txn -> txn.createUpdate("INSERT ...").execute(); txn.createQuery("SELECT ...").one())

Hope that helps.

[Mario-Eis]

Oh, I see. The intention to do it in one go was to save access time. I was used to it from MsSQL Server.
Thanks for the information.

It also doesn't work with WITH INSERT...SELECT. That's something I also found strange.