Bulk update in postgreSQL using unnest

Question

I am trying to do bulk update:

> update ti_table set enabled=T.enabled 
    from (select * from 
         unnest(array['2001622', '2001624', '2007903']) as id,
         unnest(array[15,14,8]) as ver,
         unnest(array['type1', 'type1', 'type1']) as type,
         unnest(array[false, true, true]) as enabled) T 
    where ti_table.id=T.id AND ti_table.ver=T.ver AND ti_table.type=T.type;

However, when I read back:

> select id, ver, type, enabled from ti_table where id in ('2001622', '2001624', '2007903');

I see:

   id    | ver | type  | enabled 
---------+-----+-------+---------
 2001622 |  15 | type1 | f
 2001624 |  14 | type1 | f
 2007903 |   8 | type1 | f

In the last two rows enabled is false while I expected it to be true

Why is this happening and how would I do it correctly?

Thanks.

Well, you are updating `ti_table` and reading back `ti_rules`. — MatheusOl, Sep 21 '16 at 20:49
it appears that the subquery is returning 81 results (3*3*3*4)... apparently each unnest returns a set for each value... — Sᴀᴍ Onᴇᴌᴀ, Sep 21 '16 at 20:50
@MatheusOl I fixed that it is a typo. The problem is that the inner select does a cartesian product rather than creating a single row for each set of values with the same index. — jazzblue, Sep 21 '16 at 20:55

Marina · Answer 1 · 2016-09-21T21:06:06.430

4

You could do it using PostgreSQL VALUES:

UPDATE ti_table ti
SET enabled = data.enabled
FROM (
  VALUES
    ('2001622', 15, 'type1', false),
    ('2001624', 14, 'type1', true),
    ('2007903', 8, 'type1', true)
) AS data(id, ver, type, enabled)
WHERE ti.id = data.id
AND ti.ver = t.ver
AND ti.type = data.type;

It's easier to read, because values are grouped together by rows.

edited Sep 21 '16 at 21:06

answered Sep 21 '16 at 20:56

Marina

129
7

MatheusOl · Accepted Answer · 2016-09-21T21:06:48.373

You are calling unnest 3 times on FROM clause, that means you are doing a CROSS JOIN (cartesian product) of the 3.

If you are on PostgreSQL 9.4 or higher, you can simple do one call of unnest giving each array as input:

select * from 
         unnest(
            array['2001622', '2001624', '2007903'],
             array[15,14,8],
             array['type1', 'type1', 'type1'],
             array[false, true, true]
        ) as u(id, ver, type, enabled)

Another option, for any version, is to add the call to unnest in SELECT instead of FROM:

select
   unnest(array['2001622', '2001624', '2007903']) as id,
   unnest(array[15,14,8]) as ver,
   unnest(array['type1', 'type1', 'type1']) as type,
   unnest(array[false, true, true]) as enabled

In both cases, but specially on the last one, you must be sure each array have the exact same number of elements. If it doesn't on the first method each missing row will be filled as NULL, but the second one it will return as many rows as the LCM of the number of rows returned by each, what you probably do not want. Example:

SELECT * FROM unnest(array[1,2,3,4], array['a','b','c','d','e','f']);
 unnest | unnest 
--------+--------
      1 | a
      2 | b
      3 | c
      4 | d
 [null] | e
 [null] | f
(6 rows)

SELECT unnest(array[1,2,3,4]), unnest(array['a','b','c','d','e','f']);
 unnest | unnest 
--------+--------
      1 | a
      2 | b
      3 | c
      4 | d
      1 | e
      2 | f
      3 | a
      4 | b
      1 | c
      2 | d
      3 | e
      4 | f
(12 rows)

Check the documentation on table functions calls for more information.

Bulk update in postgreSQL using unnest

2 Answers2

Linked