Often in PostgreSQL you may want to get the last element of an array.
You can use the array_upper() function with the following syntax to do so:
SELECT *,
points[array_upper(points, 1)] AS last_points
FROM athletes;
This particular example creates a new column named last_points that extracts the last element from each array in the points column of the table named athletes.
Note that we use the array_upper() function, which returns the upper bound of a specific dimension of an array.
In this example, we assume the arrays are one-dimensional so we specify a value of 1 as the second argument in the array_upper() function.
The following example shows how to use this syntax in practice.
Example: How to Get Last Element of an Array in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
points NUMERIC[]
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', '{12.1,15.6,17.8}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{22.5,16.5,18.7}');
INSERT INTO athletes VALUES (0003, 'Rockets', '{20.1,24.4,34.6}');
INSERT INTO athletes VALUES (0004, 'Spurs', '{29.4,30.5,31.2}');
INSERT INTO athletes VALUES (0005, 'Kings', '{12.6,15.5,17.9}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{20.2,23.4,26.6}');
INSERT INTO athletes VALUES (0007, 'Lakers', '{18.8,20.1,23.5}');
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+------------------
1 | Mavs | {12.1,15.6,17.8}
2 | Hawks | {22.5,16.5,18.7}
3 | Rockets | {20.1,24.4,34.6}
4 | Spurs | {29.4,30.5,31.2}
5 | Kings | {12.6,15.5,17.9}
6 | Celtics | {20.2,23.4,26.6}
7 | Lakers | {18.8,20.1,23.5}
The points column shows the average points scored by each player during three consecutive seasons.
Suppose that we would like to get the last element from each array in the points column.
We can use the following syntax to do so:
SELECT *,
points[array_upper(points, 1)] AS last_points
FROM athletes;
id | team | points | last_points
----+---------+------------------+-------------
1 | Mavs | {12.1,15.6,17.8} | 17.8
2 | Hawks | {22.5,16.5,18.7} | 18.7
3 | Rockets | {20.1,24.4,34.6} | 34.6
4 | Spurs | {29.4,30.5,31.2} | 31.2
5 | Kings | {12.6,15.5,17.9} | 17.9
6 | Celtics | {20.2,23.4,26.6} | 26.6
7 | Lakers | {18.8,20.1,23.5} | 23.5
Notice that the new column named last_points contains the last element from each array in the points column.
Feel free to use similar syntax to get the last element from an array in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Random Value from Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty
