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;

but the second member of array is two?.. so it updates it with new value - all as expected — Vao Tsun
– Vao Tsun, Commented Oct 23, 2017 at 12:14
Yes, it does update the array. I need to insert a value into it. For the result to be {"one", "three", "two"} — Ivan Zavadsky
– Ivan Zavadsky, Commented Oct 23, 2017 at 12:17
:D without it solution is obvious, with it will require a bit of coding, so if the length is defined - things will be much easier — Vao Tsun
– Vao Tsun, Commented Oct 23, 2017 at 12:24

user330315 · Accepted Answer · 2017-10-23 12:50:05Z

4

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');

answered Oct 23, 2017 at 12:50

user330315

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

2 Comments

Ivan Zavadsky Over a year ago

Is there a similar elegant way to do the same with JSON?

user330315 Over a year ago

@ИванЗавадский: not that I am aware off. JSON arrays are not as flexible as "native" arrays.

Vao Tsun · Accepted Answer · 2017-10-23 12:34:16Z

0

I dont recal array function that would allow you auto shift, so here's some coding:

t=# begin;
BEGIN
Time: 0.117 ms
t=#  with idx(v,t) as (values(2,'three'))
t-# , u as (select case when o > v then o+1 else o end, e from array_test,idx,unnest(data) with ordinality u(e,o) where id =1)
t-# , f as (select * from u union select * from idx)
t-# , r as (select array_agg(e order by o) from f)
t-# update array_test set data = array_agg
t-# from r
t-# where id =1;
UPDATE 1
Time: 0.635 ms
t=# select * from array_test;
 id |      data
----+-----------------
  1 | {one,two,three}
(1 row)

Time: 0.207 ms
t=# end;
COMMIT
Time: 0.874 ms

CTE idx(v,t) as (values(2,'three')) is your [2] and three - value for it

answered Oct 23, 2017 at 12:34

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related