Update multiple values from an array with array_replace()

Question

I am trying to do an update of a table in PostgreSQL.

In fact, I am trying to update an array. I have an table call switch_ids of OLD_ID, NEW_ID and I have the table TABLE_TO_UPDATE with a column my_array (an array of ids). I want to modify some of the ids in this array. I do something like that:

UPDATE TABLE_TO_UPDATE
SET my_array=array_replace(my_array,OLD_ID,NEW_ID)
FROM switch_ids
WHERE switch_ids.old_id = ANY(my_array);

The problem is that when there are multiple values to change in the same row (in my_array), it only changes one value and not all. How can I update them all at the same time? Nested calls?

You can nest the calls, yes. But your case is a sign that you really shouldn't be using an array to begin with: use a separate table to store those lists of ids. — Denis de Bernardy
– Denis de Bernardy, Commented May 8, 2014 at 20:51
Unfortunately, I can change the design. How can I do to nest the calls? What will it look like? — mo_alain
– mo_alain, Commented May 8, 2014 at 20:55
e.g. array_replace(array_replace(my_array,OLD_ID,NEW_ID), OLD_ID2, NEW_ID2) — as many as needed. Ugly, but works. — Denis de Bernardy
– Denis de Bernardy, Commented May 8, 2014 at 21:03

Erwin Brandstetter · Accepted Answer · 2014-05-08 21:13:48Z

1

There must be some kind of misunderstanding. array_replace() (pg 9.3+) replaces all occurrances of the item, not just the first. Consider:

SELECT array_replace(ARRAY[5,1,2,5,4,5], 5, 3);

Result:

{3,1,2,3,4,3}

SQL Fiddle.

Per documentation:

replace each array element equal to the given value with a new value

edited May 8, 2014 at 21:13

answered May 8, 2014 at 21:06

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

Update multiple values from an array with array_replace()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related