How to get the last element of an array in PostgreSQL?

Question

The PostgreSQL Documentation on arrays provides an example using [-1] to access what appears to be the last element of an array; however while SELECT arr[2:3]; produces {5,9}, arr[2:-1] results in {}.

How can the last element of an array be obtained in PostgreSQL?

Edit: Windows, PostgreSQL v9.2.1

Solution 1

I think you're misinterpreting the example. PostgreSQL arrays don't have to be indexed from 1 to n, that's just the default:

By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

The example you're looking at is this:

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

But those negative numbers aren't indexing from the end of the arrays as in languages such as Perl. In the FROM (SELECT ... part, they're specifying the starting and ending indexes so the -1 in f1[1][-1][5] is just a plain old index. Consider this array_dims result:

=> SELECT array_dims('[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[]);
    array_dims     
-------------------
 [1:1][-2:-1][3:5]

If you're using the default 1-based arrays then you can get the last element with a simple arr[array_length(arr, 1)]. If you're not using the default [1:n] arrays then you'll have to mess around with array_lower and array_upper to get the first and last elements; or, depending on the circumstances, you might be able to use unnest to unpack the array then work with the array as a rowset.

Solution 2

For any array "arr", to fetch the last element of array arr use

SELECT arr[array_upper(arr, 1)];

Solution 3

If someone is using Postgre 9.5, the documentation says:

-> int

Get JSON array element (indexed from zero, negative integers count from the end)

So this works for me:

to_json(arr)->-1

Comments

[Julian Mehnle]

This works only if arr is of the json type. This does not work for PostgreSQL array types.

[igorkf]

Oh, okay. Good point, but still may help some

[Dorian]

worked for me on an array_agg(id)

[TheAddonDepot]

This answer helped me to come up with this solution: array_to_json(ARRAY(SELECT...))->-1