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
– alzee, Commented Dec 16, 2015 at 0: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
– Erwin Brandstetter, Commented Dec 16, 2015 at 6:22

Community · Accepted Answer · 2017-05-23 12:31:23Z

4

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.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Dec 16, 2015 at 6:21

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Bulk update with associative array in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related