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.
Share Improve this question edited Feb 8 at 19:21 Zegarek 26.7k5 gold badges24 silver badges30 bronze badges asked Jan 22 at 7:53 Ludovic AubertLudovic Aubert 10.6k4 gold badges24 silver badges31 bronze badges 3 |3 Answers
Reset to default 2trim_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
1
and thearray_length()
(andcardinality()
) are unreliable. – Zegarek Commented Feb 8 at 19:24