1

Oracle has something they call array binds (from docs) where the SQL sent could be UPDATE mytable SET val = :2 WHERE id = :1 along with an array bind with contents:

1 | val1
2 | val2
3 | val3

(PS. The array's size is > 1000 items, the 3 items are for illustration.)

This is efficient and is done in a single round-trip to the server.

Is this possible using libpq as well?

meastp
  • 682
  • 1
  • 7
  • 15

2 Answers2

1

No, PostgreSQL doesn't have anything like that.

I would use a named prepared statement created with PQprepare and execute it for each row using PQexecPrepared. Similar idea, but a different method.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

If your intention is to minimize roundtrips, then you could do something like this:

UPDATE mytable m
  SET val = t.val
from ( 
  values (:1, :2), (:3, :4), (:5, :6), ....
) as t(id,val)
WHERE t.id = m.id;