Most efficient way to do a bulk UPDATE with pairs of input

Question

Suppose I want to do a bulk update, setting a=b for a collection of a values. This can easily be done with a sequence of UPDATE queries:

UPDATE foo SET value='foo' WHERE id=1
UPDATE foo SET value='bar' WHERE id=2
UPDATE foo SET value='baz' WHERE id=3

But now I suppose I want to do this in bulk. I have a two dimensional array containing the ids and new values:

[ [ 1, 'foo' ]
  [ 2, 'bar' ]
  [ 3, 'baz' ] ]

Is there an efficient way to do these three UPDATEs in a single SQL query?

Some solutions I have considered:

A temporary table
```
CREATE TABLE temp ...;
INSERT INTO temp (id,value) VALUES (....);
UPDATE foo USING temp ...
```
But this really just moves the problem. Although it may be easier (or at least less ugly) to do a bulk INSERT, there are still a minimum of three queries.
Denormalize the input by passing the data pairs as SQL arrays. This makes the query incredibly ugly, though
```
UPDATE foo
USING (
    SELECT
        split_part(x,',',1)::INT AS id,
        split_part(x,',',2)::VARCHAR AS value
    FROM (
        SELECT UNNEST(ARRAY['1,foo','2,bar','3,baz']) AS x
    ) AS x;
)
SET value=x.value WHERE id=x.id
```
This makes it possible to use a single query, but makes that query ugly, and inefficient (especially for mixed and/or complex data types).

Is there a better solution? Or should I resort to multiple UPDATE queries?

I'm having a hard time concisely describing what I want to do, which makes it very difficult to search for an answer. So there is likely a good answer in existence which I can't find, and this question is really a duplicate — Jonathan Hall, Feb 25 '15 at 15:43
I think, you're searching for [`VALUES`](http://www.postgresql.org/docs/current/static/sql-values.html) -- *It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own.* The documentation even has as example for your problem. — pozs, Feb 25 '15 at 15:55
@pozs: VALUES would work on static values, but since I'll be abstracting this to a parameterized query, I don't think it helps me. — Jonathan Hall, Feb 25 '15 at 16:35
Your examples uses static values, and you didn't mentioned you want to set up a parameterized query: then the most you can do is to create a composite type (or use an existing one, like your table's type -- which is implicitly created) and pass/bind an array of that type (and unnest it in your query). Sadly, binding such arrays at client isn't that easy; i could only think of passing their input representation, like `'{"(1,foo)","(2,bar)","(3,baz)"}'` -- as you can see, it's far from optimal. — pozs, Feb 26 '15 at 09:24
You could use the constructor syntax too, like `ARRAY[ROW(1,'foo'), ROW(2, 'bar'), ...]` but building that query is even harder than building a dynamic `VALUES` query. — pozs, Feb 26 '15 at 09:26

wildplasser · Accepted Answer · 2015-02-25T16:21:54.887

Normally you want to batch-update from a table with sufficient index to make the merge easy:

CREATE TEMP TABLE updates_table
        ( id integer not null primary key
        , val varchar
        );
INSERT into updates_table(id, val) VALUES
 ( 1, 'foo' ) ,( 2, 'bar' ) ,( 3, 'baz' )
        ;

UPDATE target_table t
SET value = u.val
FROM updates_table u
WHERE t.id = u.id
        ;

So you should probably populate your update_table by something like:

INSERT into updates_table(id, val)
SELECT
        split_part(x,',',1)::INT AS id,
        split_part(x,',',2)::VARCHAR AS value
    FROM (
        SELECT UNNEST(ARRAY['1,foo','2,bar','3,baz']) 
         ) AS x
     ;

Remember: an index (or the primary key) on the id field in the updates_table is important. (but for small sets like this one, a hashjoin will probably by chosen by the optimiser)

In addition: for updates, it is important to avoid updates with the same value, these cause extra rowversions to be created + plus the resulting VACUUM activity after the update was committed:

UPDATE target_table t
    SET value = u.val
    FROM updates_table u
    WHERE t.id = u.id
    AND (t.value IS NULL OR t.value <> u.value)
            ;

score 0 · Answer 2 · answered Dec 02 '22 at 14:22

0

You can use CASE conditional expression:

UPDATE foo
SET "value" = CASE id
    WHEN 1 THEN 'foo'
    WHEN 2 THEN 'bar'
    WHEN 3 THEN 'baz'
END

answered Dec 02 '22 at 14:22

Oleksandr Boiko

182
3
9

Most efficient way to do a bulk UPDATE with pairs of input

2 Answers2