Update field with custom types in JOOQ/PostgreSQL

Question

There is a table in PostgreSQL with ltree field. How to update this field with string value?

DSL.using(configuration)
.update(AREAS)
.set(AREAS.TREE, area.getTree());//getTree() return String

I tried several variants:

1. val(area.getTree()).cast(field("ltree")) throwing

   A org.jooq.exception.DataAccessException has been caught, 
   SQL [update "public"."areas" set "tree" = cast(? as any) where "public"."areas"."id" = ?]; 
   ERROR: syntax error at or near "any"*
   

2. inline(String.format("CAST(\'%s\' AS ltree)", area.getTree())) using excess quotes

   A org.jooq.exception.DataAccessException has been caught, 
   SQL [update "public"."areas" set "tree" = 'CAST(''1.35.1284739'' AS ltree)' where "public"."areas"."id" = ?]; 
   ERROR: syntax error at position 4*
   

3. val(field("CAST({0} AS ltree)", area.getTree())) throwing

   A org.jooq.exception.SQLDialectNotSupportedException has been caught, 
   Type class org.jooq.impl.SQLField is not supported in dialect DEFAULT
   

Answer 1

Thorough solution

In the long run, the best approach to add support for vendor-specific data types is to specify a custom data type binding for it, which allows you to define how jOOQ should serialise / deserialise the type to the JDBC API, including the cast that you might need.

A quick win might be these:

field("CAST(? as ltree)", area.getTree());
field("CAST({0} as ltree)", val(area.getTree()));

This is why your various attempts didn't work:

val(area.getTree()).cast(field("ltree"))

That approach seems to make sense at first, as you're using jOOQ's built-in cast() support. However, your field("ltree") expression models a field named ltree of an unknown type, so when you pass that to cast(), jOOQ doesn't know what to cast the val to, thus: any

inline(String.format("CAST(\'%s\' AS ltree)", area.getTree()))

This doesn't really make sense in your context, because DSL.inline() creates an inline bind variable, or constant, or a string literal (all different names for the same concept). You don't want a string literal 'CAST(... AS ltree)', you want the expression CAST(... AS ltree).

val(field("CAST({0} AS ltree)", area.getTree()))

That's similar to the above, although you were close here. The inner field(...) expression creates a SQL template, which is what you wanted. But then you wrapped that in a bind variable using DSL.val(), which doesn't really make sense for two reasons:

• You don't want your cast expression to be a bind variable
• The cast expression (of type Field) is not a valid type to create a bind variable from. jOOQ doesn't know how to bind a value of type Field