Postgresql batch update

Question

How to batch update the following sample more efficiently.

users = [{id: 424, pos: 1}, {id: 23, pos: 2}, {id: 55, pos: 3}, ...]

//currently loop updating each {i}:
   UPDATE users SET position = i.pos WHERE id = i.id

Aren't you looking for this ? https://stackoverflow.com/a/20224370/3277024 — JMG, Feb 06 '20 at 18:02

score 1 · Answer 1 · answered Feb 06 '20 at 18:03

1

You can use unnest():

update users u
    set position = user.pos 
    from (values ([{id: 424, pos: 1}, {id: 23, pos: 2}, {id: 55, pos: 3}, ...])
         ) v(users) cross join lateral
         unnest(users) user
    where u.id = user.id

answered Feb 06 '20 at 18:03

Gordon Linoff

1,242,037
58
646
786

This is supposed to help me, let me try – 7urkm3n Feb 06 '20 at 18:07
Could you explain me this `v(users) cross join lateral unnest(users) user`, if my array is named `list = []` for example ? I am kinda getting syntax issue here. – 7urkm3n Feb 06 '20 at 21:06
@7urkm3n . . . https://www.postgresql.org/docs/current/queries-table-expressions.html#Lateral. – Gordon Linoff Feb 07 '20 at 02:21

Postgresql batch update

1 Answers1