1

I am building a Postgres query using jOOQ (the query isn't sent to the DB using jOOQ though, it's used for creating the queries that are used by vertx).

The query incorporates a piece with a "case":

...
set "state" = case
    when "theschema"."actions"."is_promoted" = true then 'OK'
    else "theschema"."actions"."state"
end

The query fails with

ERROR: "CASE types actions_state_enum and text cannot be matched"

The solution is to cast 'OK' into actions_state_enum, something like 'OK'::"theschema"."actions_state_enum".

The code creating the query piece above looks like:

DSL.iif(ACTIONS.IS_PROMOTED.isTrue(), ActionsStateEnum.OK, ACTIONS.STATE)

I can't find the way to add the required casting in the code. Tried to play with DSL.inline adding a custom Binding but with no luck.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
atarno
  • 329
  • 1
  • 3
  • 14
  • What's `ActionsStateEnum`? A hand-written, custom enum, or the enum type generated by the code generator? – Lukas Eder Jun 10 '21 at 12:56
  • 1
    @LukasEder It's jooq generated enum. actually i think i've found a reason it didn't work: the dsl context was using static_statement type, with prepared_statement the casting is added out of the box. – atarno Jun 10 '21 at 16:09

1 Answers1

0

This looks like https://github.com/jOOQ/jOOQ/issues/5612. Will look into fixing this soon. The workaround is to use a custom data type binding that generates the cast explicitly

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509