最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin2浏览0评论

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
  • 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

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

发布评论

评论列表(0)

  1. 暂无评论