Search code examples
javapostgresqljooqltree

Update field with custom types in JOOQ/PostgreSQL

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
Solution

  • 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