2

I have a column named ids that is of type bigint[]. ids can be null or of any length

It has values based on certain hierarchy.

My use case is I want to update value that is at a particular index. For example ids[2] = 50.

How can I achieve this?

I looked at PostgreSQL documentation here but couldn't find anything that could help.

3 Answers 3

3

Indeed there is no such function, but you can easily write your own:

create function array_set(p_input anyarray, p_index int, p_new_value anyelement)
  returns anyarray
as
$$
begin
  if p_input is not null then
    p_input[p_index] := p_new_value;
  end if;
  return p_input;
end;
$$
language plpgsql
immutable;

(If you never need it for anything different than bigint[] then you can replace the anyelement and anyarray with the equivalent types)

Then you can use it like this:

update the_table
  set ids = array_set(ids, 2, 50::bigint)
where ...;
3
  • Thank you :) That's a pretty elegant approach.
    – Sarita
    May 29, 2020 at 9:45
  • When used in a function, that approach is more efficient than my answer. May 29, 2020 at 10:00
  • There is no need to create function for that, see my answer for details.
    – Konard
    Nov 6, 2020 at 16:36
2

You need to use unnest to make a table from the array and array_agg to aggregate it back into an array.

To replace the third element in an array of integer with 42, you could for example use

SELECT array_agg(
          CASE WHEN num = 3
               THEN 42
               ELSE elem
          END
          ORDER BY num
       )
FROM unnest('{1,2,4,5}'::int[]) WITH ORDINALITY AS a(elem, num);

 array_agg  
------------
 {1,2,42,5}
(1 row)

You could write your own function based on that.

WITH ORDINALITY tracks the ordering of the array elements, so that you can identify the currect one, and ORDER BY makes sure that the final aggregate constructs the array in the same order.

2
  • Thank you so much :). This works. But I wanted to confirm will it change the actual position of the array elements? and how does just ELSE unnest work? Because unnest doesnot have any parameter here.
    – Sarita
    May 29, 2020 at 9:43
  • 1
    Sorry, that's confusing. ordinality and unnest are the column names that PostgreSQL chooses by default. I have modified the answer to use an explicit alias - maybe that makes things clearer. May 29, 2020 at 9:54
2
create temporary table the_table (
    ids bigint[]
);

insert into the_table (ids) values
    (null),
    (array[1, 2]),
    (array[1, 2, 3]);

update the_table
set ids[2] = 50
where 
  ids is not null and
  array_upper(ids, 1) >= 2;
  
select * from the_table;

enter image description here

Source: https://www.postgresql.org/docs/current/arrays.html#ARRAYS-MODIFYING

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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