Search code examples
javasqlpostgresqljooq

Explicitly casting enum in case query

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.

Solution

  • 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