postgres array range notation with jooq

user_8019169 0
Viewed 91

in my postgres database i have an array of jsons (json[]) to keep track of errors in the form of json objects. if an update comes in i want to append the latest error to that array but only keep the latest 5 errors (all sql is simplified):

update dc
set 
    error_history           =
        array_append(dc.error_history[array_length(dc.error_history, 1) - 4:array_length(dc.error_history, 1)+1], cast('{"blah": 6}' as json))
where dc.id = 'f57520db-5b03-4586-8e77-284ed2dca6b1'
;

this works fine in native sql, i tried to replicate this in jooq as follows:

.set(dc.ERROR_HISTORY,
    field("array_append(dc.error_history[array_length(dc.error_history, 1) - 4:array_length(dc.error_history, 1) + 1], cast('{0}' as json))", dc.ERROR_HISTORY.getType(), latestError)
);

but it seems the : causes the library to think that there is a bind parameter there. the generated sql is:

array_append(dc.error_history[array_length(dc.error_history, 1) - 4?(dc.error_history, 1) + 1], cast('{0}' as json)

and the error i get is

nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "$5"

which i totally agree with :D

is there some way to escape the : in the java code or is there a better way to do this?

edit: i have also tried just to remove the first element upon updating using the arrayRemove function but that also didn't work because it doesn't work by index but by element and postgres doesn't know how to check json elements for equality.

jooq postgresql
1 Answers

omg, the answer was really simple :D

field("array_append(dc.error_history[array_length(dc.error_history, 1) - 4 : array_length(dc.error_history, 1) + 1], cast({0} as json))", dc.ERROR_HISTORY.getType(), latestError)

just add spaces around the colon and it works correctly. note that i made another mistake by putting single quotes around {0}, because the latestError object is already of type JSON.

user_80191690
Related