Postgres batch update can not work

[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.

[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.

[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

[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