Search code examples
javajooqratpack

DslContext Jooq update timestamp with time zone column as null


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 ?


Solution

  • 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();