Remove n elements from array using start and end index

Question

I have the following table in a Postgres database:

CREATE TABLE test(
  id SERIAL NOT NULL,
  arr int[] NOT NULL
)

And the array contains about 500k elements.

I would like to know if there is an efficient way to update arr column by removing a set of elements from the array given the start and end index or just the number of "n first elements" to remove.

Answer 1

You can access individual elements or ranges of elements:

If you e.g. want to remove elements 5 to 8, you can do:

select arr[1:4]||arr[9:]
from test;

or as an update:

update test
   set arr = arr[1:4]||arr[9:];

To remove the "first n elements", just use the slice after the n+1 element, e.g. to get remove the first 5 elements:

select arr[6:]
from test;

The syntax arr[6:] requires Postgres 9.6 or later, for earlier versions you need

select arr[6:cardinality(arr)]
from test;

cardinality() was introduced in 9.4, if you are using an even older version, you need:

select arr[6:array_lengt(arr,1)]
from test;