2

Is it possible to create a INSERT INTO ... SELECT ... RETURNING statement with jOOQ? If so, how?

There is no returning function here:

context.insertInto(table, list of fields).select(select statement).returning()

If this is indeed absent, is there a smart workaround available? I cannot express my INSERT INTO ... SELECT ... as a INSERT INTO ... VALUES .... See question jOOQ - multi-field for insertion for more details about the query.

Improve this question

1 Answer 1

Reset to default
2

That combination of INSERT keywords was indeed not possible until jOOQ 3.7 via #3779.

A workaround in the meantime would be to use plain SQL:

Result<Record> result = context.fetch("{0} returning *", insertInto(...).select(...));
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi Lukas. This works fine. However, I suspect that my org.jooq.Converter's (from Timestamp to LocalDateTime in my case) are not being called anymore with this approach. Any extra workaround necessary for this? (Should I open a new question for this?)
@KevinDeGrote: I see, Unfortunately, I currently don't see any workarounds, short of going deep into the internals of jOOQ. One workaround would be to form a CTE for the INSERT statement, but this currently cannot be done with plain SQL. (github.com/jOOQ/jOOQ/issues/4474). What we'd need is a way to set the <R> type on the ResultQuery prior to actually executing it. I've registered a feature request for this: github.com/jOOQ/jOOQ/issues/4473. Thanks for the heads-up!