5

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?

  • 1
    Note that it's not possible to actually remove an array element, you have to replace the entire array by a near-copy of itself that differs from the original by not having this particular element. The same is true with any database type, JSON or HSTORE or composite types. – Daniel Vérité Mar 7 '15 at 16:17
7

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:

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:

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

  • Thank you. It's a bit wordy but probably worth settling for this instead of hoping for a function that doesn't exist. – Fawn Mar 7 '15 at 2:32
  • @Fawn: You can create your own function for ease of use. Plus, I added another solution. – Erwin Brandstetter Mar 9 '15 at 0:04
  • 1
    i hate that there isn't a core method of doing this. – Evan Carroll Jun 8 '17 at 17:26
  • @EvanCarroll: It did get a bit simpler with pg 9.6 though. Consider the update. – Erwin Brandstetter Jun 9 '17 at 2:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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