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 ? stackoverflow.com/a/20224370/3277024 — JMG
– JMG, Commented Feb 6, 2020 at 18:02

Gordon Linoff · Accepted Answer · 2020-02-06 18:03:53Z

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 6, 2020 at 18:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

7urkm3n Over a year ago

This is supposed to help me, let me try

7urkm3n Over a year ago

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.

Gordon Linoff Over a year ago

@7urkm3n . . . postgresql.org/docs/current/….

Collectives™ on Stack Overflow

Postgresql batch update

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related