How do I insert a value into a Postgres DB column array at a specific position?

Question

CREATE TABLE array_test (
  id serial primary key,
  data text[]
);

INSERT INTO array_test (data) VALUES
  ('{"one", "two"}');

-- Now I need to insert a second member of the array. My try would be the following, but the code only redefines the second member:

UPDATE array_test SET data[2] = 'three'
WHERE id = 1;

Answer 1

You can slice the existing array and append the new values to those slices:

update array_test
   set data = data[:1]||'three'::text||data[2:];

data[:1] selects everything up to the first element and data[2:] selects everything after (and including) the second element.

If you need this very often, it might make sense to put this into a function.

create or replace function array_set_at(p_data text[], p_pos int, p_element text)
  returns text[]
as
$$
  select p_data[:p_pos - 1]||p_element||p_data[p_pos:];
$$ 
language sql;

Then you can do it like this:

update array_test
   set data = array_set_at(data, 2, 'three');