postgresql - Remove first and last element from an array - Stack Overflow

I can remove easily the first element from an array:

SELECT (ARRAY[1,2,3])[2:];

{2,3}

I can remove easily the first element from an array:

SELECT (ARRAY[1,2,3])[2:];

{2,3}

What is the simplest way to remove the last element as well? The size is dynamic and is not known at compile time.

• arrays
• postgresql
• postgresql-17

Share Improve this question Follow edited Feb 8 at 19:21 Zegarek 27.4k55 gold badges2424 silver badges3030 bronze badges asked Jan 22 at 7:53 Ludovic AubertLudovic Aubert 10.6k44 gold badges2424 silver badges3131 bronze badges 3

• SELECT (ARRAY[1,2,3])[1:array_length(ARRAY[1,2,3], 1) - 1]; – coman Commented Jan 22 at 7:58
• Do you want to remove just the last element or both the first and the last? – Proteus Commented Jan 22 at 8:38
• @coman Both the 1 and the array_length() (and cardinality()) are unreliable. – Zegarek Commented Feb 8 at 19:24

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

trim_array() is there since PostgreSQL 14:

select (trim_array(array[1,2,3],1))[2:];

select arr[array_lower(arr,1)+1 : array_upper(arr,1)-1]
from(select '{1,2,3}'::int[] as arr)_;

Or

select trim_array(arr[array_lower(arr,1)+1 :],1)
from(values(array[1,2,3]))_(arr);

Postgres array indexing isn't strictly 1-based, which is why it offers array_lower(), array_upper(), trim_array(). The [2:] slice doesn't remove the 1st element, it removes all elements prior to index 2, which could be many, one or none:
_{demo at db<>fiddle}

select arr[array_lower(arr,1)+1 : array_upper(arr,1)-1] as ok1
     , trim_array(arr[array_lower(arr,1)+1 :],1)        as ok2
     , arr[2 :  ]  as wrong1
     , arr[2 : 3]  as wrong2
     , arr[  :array_length(arr,1)-1] as wrong3
     , arr[2 :-2]  as wrong4
     , (arr[2:])[:-2] as wrong5
from (SELECT '[50:53]={50,51,52,53}'::int[]) as your(arr);

ok1	ok2	wrong1	wrong2	wrong3	wrong4	wrong5
{51,52}	{51,52}	{50,51,52,53}	{}	{}	{}	{}

In this array, the first index is 50, not 1. Using array_length() (and cardinality()) to get the index of the last element isn't reliable either: its length is 4 but the last index is 53.

You can even stumble into negative indices:

select ('[-5:-3]={a,b,c}'::char[])[-4] as minus_fourth;

minus_fourth
b

In this case, all your slices and subscripts using length and positive indices would wipe the whole thing.

The example also demonstrates why negative subscripts aren't counted backwards from the end - they are perfectly valid as regular indices without flipping the order. The [2:-2] and (arr[2:])[:-2] you were probably expecting get you an empty array: the first part trims away everything below index 2 which includes all elements at positions with a negative index, then the second asks for only those up to -2, all of which you just dropped.

You can use the array length function to get the array length:

SELECT (ARRAY[1,2,3])[2:array_length(ARRAY[1,2,3], 1) - 1];
                      ^ Beginning                       ^ Length - 1 = End