How to pass array parameters to a Postgres query?

Question

I am very new to PostgreSQL and struggling to work with arrays.
I have a table bookIds like this:

book_id	location_id	checked_out_version
12	1	10
15	1	12

From Java code, I am passing arrays of checked out versions, location ids and book ids to a query, and the query needs to:

UPDATE bookIds SET checked_out_version = (values in the array?)
WHERE location_id = ANY (:locationIDS)
AND book_id = ANY (:bookIDS)

I keep getting errors that Postgres does not support updating a col with an array using the SET clause in a UPDATE statement.

I have also tried looping but, obviously, it sets the checked out version to the latest in the array.

Example:

If I pass the following arrays:

bookIds [12,15]
LocationIDS [1,1]
checkedOutVersions [11, 20]

The table needs to be updated to look like

book_id	location_id	checked_out_version
12	1	11
15	1	20

This is the loop I tried:

DO $$
DECLARE
   i integer;
BEGIN
   FOREACH i IN ARRAY :checkedOutVersions::integer[] LOOP
      RAISE NOTICE 'Value: %', i;
      UPDATE bookIds SET checked_out_version = i
      WHERE location_id = ANY (:locationIDs::bigint[])
      AND book_id = ANY (:bookIDs::integer[]);
    END LOOP;
END $$;

How to do this properly?

Erwin Brandstetter · Accepted Answer · 2024-04-14 23:44:20Z

You don't a need a loop in a PL/pgSQL block, so no DO command either.

Use a plain UPDATE statement and unnest your arrays in lockstep with unnest() as set-returning function:

UPDATE bookids b
SET    checked_out_version = t.ver
FROM   unnest('{12,15}'::int[]   -- :bookIDs
            , '{1,1}'::bigint[]  -- :locationIDs
            , '{11,20}'::int[]   -- :checkedOutVersions
       ) AS t(book_id, loc_id, ver)
WHERE  b.location_id = t.loc_id
AND    b.book_id = t.book_id;
AND    checked_out_version IS DISTINCT FROM t.ver  -- avoid empty updates
;

(Ideas with location_id = ANY (:locationIDs::bigint[]) etc. would result in a Cartesian product of elements from combined arrays. Expensive nonsense.)

See:

Unnest multiple arrays in parallel

I added the filter AND checked_out_version IS DISTINCT FROM t.ver to avoid empty updates. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

Note the syntax for Postgres array literals:

Pass array literal to PostgreSQL function

Maybe as prepared statement? Prepare:

PREPARE upd1(int[], bigint[], int[]) AS
UPDATE bookids b
SET    checked_out_version = t.ver
FROM   unnest($1, $2, $3) AS t(book_id, loc_id, ver)
WHERE  b.location_id = t.loc_id
AND    b.book_id = t.book_id
AND    checked_out_version IS DISTINCT FROM t.ver;

Execute:

EXECUTE upd1('{12,15}', '{1,1}', '{11,20}');

fiddle

No explicit type casts needed in the call, since parameter types have been declared in PREPARE. Also implicitly safe against SQL injection (which can be a problem with simple interpolation / concatenation). Don't know much about JDBC, but every major programming language has libraries to handle prepared statements.

Aside: Use legal, lower-case, unquoted identifiers to make your life with Postgres easier. See:

Are PostgreSQL column names case-sensitive?

Collectives™ on Stack Overflow

How to pass array parameters to a Postgres query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related