Often in PostgreSQL you may want to slice an array to extract specific elements.
You can use the following methods to do so:
Method 1: Extract Specific Range of Elements
SELECT points[2:5] FROM athletes;
This particular example will return elements in positions 2 through 5 of each array in the points column of the athletes table.
Method 2: Extract All Elements Up to Specific Position
SELECT points[:3] FROM athletes;
This particular example will return the first 3 elements of each array in the points column of the athletes table.
Method 3: Extract All Elements Starting from Specific Position
SELECT points[3:] FROM athletes;
This particular example will return all elements starting from position 3 to the last position of each array in the points column of the athletes table.
The following examples show how to use each method in practice with the following table named athletes:
-- 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, 9, 20, 34, 12, 16}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{22, 20, 40, 32, 9, 13}');
INSERT INTO athletes VALUES (0003, 'Rockets', '{4, 10, 14, 13, 20, 23}');
INSERT INTO athletes VALUES (0004, 'Spurs', '{10, 14, 14, 13, 18, 20}');
INSERT INTO athletes VALUES (0005, 'Kings', '{8, 14, 20, 46, 20, 29}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{6, 12, 12, 19, 15, 20}');
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+---------------------
1 | Mavs | {12,9,20,34,12,16}
2 | Hawks | {22,20,40,32,9,13}
3 | Rockets | {4,10,14,13,20,23}
4 | Spurs | {10,14,14,13,18,20}
5 | Kings | {8,14,20,46,20,29}
6 | Celtics | {6,12,12,19,15,20}
Let’s jump in!
Example 1: Extract Specific Range of Elements
We can use the following syntax to extract elements in positions 2 through 5 of each array in the points column of the athletes table:
SELECT *,
points[2:5]
FROM athletes;
id | team | points | points
----+---------+---------------------+---------------
1 | Mavs | {12,9,20,34,12,16} | {9,20,34,12}
2 | Hawks | {22,20,40,32,9,13} | {20,40,32,9}
3 | Rockets | {4,10,14,13,20,23} | {10,14,13,20}
4 | Spurs | {10,14,14,13,18,20} | {14,14,13,18}
5 | Kings | {8,14,20,46,20,29} | {14,20,46,20}
6 | Celtics | {6,12,12,19,15,20} | {12,12,19,15}
Example 2: Extract All Elements Up to Specific Position
We can use the following syntax to extract all elements from the first position up to position 3 of each array in the points column of the athletes table:
SELECT *,
points[:3]
FROM athletes;
id | team | points | points
----+---------+---------------------+------------
1 | Mavs | {12,9,20,34,12,16} | {12,9,20}
2 | Hawks | {22,20,40,32,9,13} | {22,20,40}
3 | Rockets | {4,10,14,13,20,23} | {4,10,14}
4 | Spurs | {10,14,14,13,18,20} | {10,14,14}
5 | Kings | {8,14,20,46,20,29} | {8,14,20}
6 | Celtics | {6,12,12,19,15,20} | {6,12,12}
Example 3: Extract All Elements Starting from Specific Position
We can use the following syntax to extract all elements starting from position 3 up to the last element of each array in the points column of the athletes table:
SELECT *,
points[3:]
FROM athletes;
id | team | points | points
----+---------+---------------------+---------------
1 | Mavs | {12,9,20,34,12,16} | {20,34,12,16}
2 | Hawks | {22,20,40,32,9,13} | {40,32,9,13}
3 | Rockets | {4,10,14,13,20,23} | {14,13,20,23}
4 | Spurs | {10,14,14,13,18,20} | {14,13,18,20}
5 | Kings | {8,14,20,46,20,29} | {20,46,20,29}
6 | Celtics | {6,12,12,19,15,20} | {12,19,15,20}
Feel free to use any of the methods shown here to extract specific elements from arrays in your own table in PostgreSQL.
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
