current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
    
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 May 8 at 20:51
    
Unfortunately, I can change the design. How can I do to nest the calls? What will it look like? –  mo_alain May 8 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 May 8 at 21:03

1 Answer 1

up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.