DslContext Jooq update timestamp with time zone column as null

Question

I have Customer table in postgres DB with id as primary key and below columns

id -> Integer(PK)
name -> Varchar2
details-> jsonb
created_timestamp -> TIMESTAMP WITH TIME ZONE

I'm trying to update table based on primary key using dslContext.resultQuery method, i have to update the name, details the jsonb column and set created_timestamp as null

String sql = "UPDATE customer SET name = :name, details = :details, created_timestamp = 'NULL' where id = :id RETURNING id";

Java Code

List<Map<String,Object>> updatedCapacityResult = dslContext.resultQuery(sql,
      DSL.param("name","value"),
      DSL.param("details",objectmapper.writeValueAsString(object))).fetchMaps();

I'm having error while executing the code and it simply says

org.jooq.exception.DataAccessException: SQL [UPDATE customer SET name = :name, details = :details, created_timestamp = 'NULL' where id = :id RETURNING id; ERROR: syntax error at or near "details"
 Position: 60

When i removed the code to update details, then it started erroring out on created_timestamp field, if i just updated the name using above query it updates and return the data as well.

Curious what i have missed here and how to updated jsonb and timestamp columns ?

Answer 1

I couldn't figure out the issue with jooq library, but on updating values to NULL i found alternative like using StringBuilder and forming it as string

String sql = new StringBuilder("UPDATE customer SET" )
                .append("name = '"+name+"', ")
                .append("details = '"+details+"', ")
                .append("created_timestamp = NULL ")
                .append("where id = '"+id+"' RETURNING id").toString();

and the just using resultQuery

 List<Map<String,Object>> updatedCapacityResult = dslContext.resultQuery(sql).fetchMaps();

Answer 2

The JSONB column

While sometimes, the pgjdbc driver and PostgreSQL server can coerce a string value into a JSONB value, it doesn't always work, so the safest bet is to always cast explicitly, i.e.:

details = cast(:details as jsonb)

The NULL value

You're not setting the timestamp to NULL (the value), you're setting it to 'NULL' (the string literal). Just omit the apostrophes

created_timestamp = NULL

Using the code generator and the DSL

You probably have your reasons for using plain SQL like this, but please note that jOOQ's API, DSL, and code generator handle all of these things for you transparently. You'd just write:

ctx.update(CUSTOMER)
   .set(CUSTOMER.NAME, name)

   // You could even attach a Converter to this column to auto-convert
   // between your object representation and the JSONB representation!
   .set(CUSTOMER.DETAILS, jsonb(objectmapper.writeValueAsString(object)))
   .setNull(CUSTOMER.CREATED_TIMESTAMP)
   .returning()
   .fetchMaps();

See this blog post on why it is usually recommended to use code generation with jOOQ.