Array to IN operator within Database
Scenario:
We are querying a table in Postgresql and retrieving records with ID.
The query output is similar to this:
[
{
"record_id": [
403,
412,
414
]
}
]
After completing the processing, I want to update the postgresql table for those record_id that the processing is complete.Problem: Converted the record_id as:
“(”+record_id.map(x => parseInt(x)).join(“,”)+“)” before sending this to the sql query (using JDBC execute)
After this step, my output is this: “record_idSQL”: “(403,412,414)”
since record_idSQL is now a string, the moment I invoke the database query and using IN operator to find the rows with these record_ids, the adapter is adding a ’ ’ and it is erroring out.
From the error i get the following:
update abc.def set status = ‘3’ where record_id in ‘(403,412,414)’ was aborted. ERROR: syntax error at or near “$5” Position.
record_id in postgresql is a int4 datatype and is sequence field with auto incrementing.
Any help is highly appreciated.