Bulk update with associative array in PostgreSQL

Question

Let's say I have an associative array (defined in another language) like so:

apply = {
  'qwer': ['tju', 'snf', 'rjtj', 'sadgg']
  'asdf': ['rtj', 'sfm', 'rtjt', 'adjdj']
  ...
  'zxcv': ['qwr', 'trj', '3w4u', '3tt3']
}

And I have a table like so:

CREATE TABLE apples (
id integer,
name varchar(10),
key varchar(10),
value varchar(10)
);

I want to apply an update where if apples.value is in one of the lists of the apply variable, then set the apples.key to the key of the array. If apples.value was tju then set apples.key to qwer.

My current approach looks like this (mixing PostgreSQL with any procedural language):

for key in apply.keys:
  UPDATE apples SET key=$key
  FROM (SELECT unnest(array($apply[key])) AS value) AS update_table
  WHERE value=update_table.value

I want to do this in a single statement.

I don't believe there is a way to do this in a literal single statement. With a function (stored procedure) or a smart DB library in your code sure, but as straight SQL, I'm skeptical. I've upvoted the question though because It's interesting and I'd like to be proven wrong. — alzee, Dec 16 '15 at 00:03
@user3137702: There is always a way. Postgres has a large arsenal of string functions. Typically, it would be smarter / less error-prone to transform to compatible data types in the source language, though. I added a proof of concept. — Erwin Brandstetter, Dec 16 '15 at 06:22

score 4 · Accepted Answer · edited May 23 '17 at 12:31

As proof of concept for the given example, with the string formatted exactly as you display:

Demonstrating a prepared statement, like your client probably uses.

PREPARE my_update AS
UPDATE apples a
SET    key = upd.key
FROM  (
   SELECT trim (split_part(key_val, ': ', 1), ' ''') AS key
        , string_to_array(translate(split_part(key_val, ': ', 2), '[]''', ''), ', ') AS val_arr
   FROM   unnest(string_to_array(trim($1, E'{}\n'), E'\n')) key_val
   ) upd
WHERE  a.value = ANY(upd.val_arr);

EXECUTE in the same session any number of times:

EXECUTE my_update($assoc_arr${
  'qwer': ['tju', 'snf', 'rjtj', 'sadgg']
  'asdf': ['rtj', 'sfm', 'rtjt', 'adjdj']
  'zxcv': ['qwr', 'trj', '3w4u', '3tt3']
}$assoc_arr$);

SQL Fiddle.

Bulk update with associative array in PostgreSQL

1 Answers1