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:

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"*

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*

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

Can you please specify which version of JOOQ and PostgreSQL you are using? — CuriousMind
– CuriousMind, Commented Nov 8, 2017 at 8:20
Jooq version 3.6.4, PostgreSQL 9.6.2, driver version 42.1.4.jre7 — Alina Didenko
– Alina Didenko, Commented Nov 8, 2017 at 8:56

Lukas Eder · Accepted Answer · 2017-11-08 09:44:17Z

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

Thank you for answer! It was necessary only to cast the type (Object)field("CAST(? AS ltree)", tree); Sorry for the trouble

Collectives™ on Stack Overflow

Update field with custom types in JOOQ/PostgreSQL

1 Answer 1

Thorough solution

This is why your various attempts didn't work:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Thorough solution

This is why your various attempts didn't work:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related