CODEWITHSUNDEEP
javapostgresqljooqltree
Alina Didenko
Alina Didenko

Reputation: 178

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

Upvotes: 2

Views: 1393

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

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

Upvotes: 1

Related Questions