Page 1 of 1
Postgres batch update can not work
Posted: 01.09.2022, 08:19
by abk964
Batch update can not work with Postgres - function unnest can not be used in WHERE clause.
Generated query:
Code: Select all
UPDATE BATCH_TEST_TABLE SET VAL_FIELD = unnest($1::int4[]) WHERE VAL_FIELD = unnest($2::int4[])
Table DDL:
Code: Select all
CREATE TABLE BATCH_TEST_TABLE (
VAL_FIELD INTEGER
);
Project attached.
Re: Postgres batch update can not work
Posted: 05.09.2022, 10:35
by abk964
Solved by replacing SQL from
Code: Select all
UPDATE BATCH_TEST_TABLE SET VAL_FIELD = :P0 WHERE VAL_FIELD = :P1
to
Code: Select all
UPDATE batch_test_table SET val_field = "CAST"(r.c0 as integer) FROM (SELECT :p0 as c0, :p1 as c1 ) r
WHERE val_field = "CAST"(r.c1 as integer)
Version with CTE
Code: Select all
WITH r(c0 ,c1 ) AS (SELECT :P0, :P1)
UPDATE public.batch_test_table SET val_field = "CAST"(r.c0 as integer)
FROM r WHERE val_field = "CAST"(r.c1 as integer)
raises access violation in TZBindList.AcquireBuffer.
BTW, can't use CAST operator "::" - one semicolon disappears.
Re: Postgres batch update can not work
Posted: 10.09.2022, 14:21
by stoffman
You can use the the semicolon operator, you need to double it
so instead var::int
you write var::::int
":" is being used by zeos to parametrize the query, so it kind of conflicts with postgress, but as I said just double its number
Re: Postgres batch update can not work
Posted: 10.09.2022, 15:46
by marsupilami
Another option is to change the paramchar property. Setting paramchar to # might work:
Code: Select all
UPDATE BATCH_TEST_TABLE SET VAL_FIELD = #P0 WHERE VAL_FIELD = #P1