1

So, lets say I have two ordered arrays with the same number of elements where each index of each array refers to the other one at the same index

values = [ 5, 7, 8, 9 ]
keys = ['a', 'b', 'c', 'd' ]

instead of doing multiple UPDATES inside a loop

for thing, index in values
    UPDATE table SET column1 = thing WHERE column2 = keys[index]

is there a way to pull these arrays into Postgres and use them in a single UPDATE query?

Something like:

UPDATE table SET column1 = values[?] WHERE column2 = keys[?]
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Christopher Reid
  • 4,318
  • 3
  • 35
  • 74
  • there isnt a direct way. You need convert those two array into a table with unnest http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number – Juan Carlos Oropeza Jul 21 '16 at 00:43
  • If you store the keys and values together in an hstore or json object it would be fairly easy to do but nothing direct. – Wolph Jul 21 '16 at 01:13

1 Answers1

2

Yes there is a way.

Assuming current Postgres 9.5 since you did not specify. Use the new form of unnest() to unnest multiple arrays in parallel:

And use the derived table in a plain UPDATE: 

UPDATE tbl t
SET    column1 = a.value
FROM   unnest ('{5, 7, 8, 9}'::int[]
              ,'{a, b, c, d}'::text[]) AS a(value, key)
WHERE  t.column2 = a.key;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228