Delete array element by index

Question

Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.)

I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but perhaps there are other functions I am missing?

Answer 1

There is no built-in function that I would know of. In Postgres 9.3+ you have the similar function array_remove(anyarray, anyelement) to (per documentation):

remove all elements equal to the given value from the array (array must be one-dimensional)

To delete the 3rd element from a 1-dimenstional array with unique elements:

SELECT array_remove(arr, arr[3]);

If uniqueness is not guaranteed the above might delete multiple elements. You can concat two array slices instead:

Postgres 9.5 or older

SELECT arr[1:2] || arr[4:2147483647] AS arr_without_3rd_elem;

2147483647 is just the maximum possible array subscript to cover all possibilities. Works for any version since at least 8.3, probably more.
About array subscripts:

• Normalize array subscripts for 1-dimensional array so they start with 1

Old SQL Fiddle for Postgres 9.3.

If you need this a lot, create a function. I suggest an IMMUTABLE polymorphic SQL function. (An implementation in C would be a bit faster.)

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[1:$2-1] || $1[$2+1:2147483647]';

Call:

SELECT f_array_remove_elem('{1,2,3,4,5}'::int[], 4)    AS i
     , f_array_remove_elem('{foo,bar,baz}'::text[], 1) AS t;

Result:

i         | t
----------+-----------
{1,2,3,5} | {bar,baz}

It's more complicated ...

• for multi-dimensional arrays.
  You cannot just remove a single element, dimensions have to stay in sync. So you have to define which slice to remove exactly. Then I would probably unnest the array using generate_suscripts(), remove selected subscripts and array_agg() / array_agg_mult() the result.

• for non-standard array-subscripts.
  Normalize subscripts in the source like demonstrated in the linked question or adapt subscripts in the expression with array_lower() and array_upper() ...

Postgres 9.6

.. introduced short notation for upper and lower limit (just omit the number). I added an answer to the question linked above:

• Normalize array subscripts for 1-dimensional array so they start with 1

Simpler now:

SELECT arr[:2] || arr[4:] AS arr_without_3rd_elem;

The function:

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:$2-1] || $1[$2+1:]';

Works for all 1-dimensional arrays, including non-standard array-subscripts. Just be sure to provide the correct array index of the element to remove.

dbfiddle here