PostgreSQL: Get Nth Element from string_to_array Function

Often in PostgreSQL you may want to use the string_to_array function to split a string into an array and then get the Nth element from the resulting array.

You can use the following syntax to do so:

SELECT *,
       (string_to_array(positions,' '))[2] AS second_position
FROM athletes;

This particular example splits the string in the position column into an array and then gets the 2nd element from the resulting array, saving the results to  a new column named second_position.

Note: The index for elements in an array starts at 1 in PostgreSQL.

The following example shows how to use this syntax in practice.

Example: Get Nth Element from string_to_array Function in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  positions TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'The Starting Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'The Backup Guard');
INSERT INTO athletes VALUES (0003, 'Kings', 'The Starting Forward');
INSERT INTO athletes VALUES (0004, 'Lakers', 'The Backup Forward');
INSERT INTO athletes VALUES (0005, 'Celtics', 'The Starting Center');
INSERT INTO athletes VALUES (0006, 'Nuggets', 'The Backup Center');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |      positions       
----+---------+----------------------
  1 | Mavs    | The Starting Guard
  2 | Hawks   | The Backup Guard
  3 | Kings   | The Starting Forward
  4 | Lakers  | The Backup Forward
  5 | Celtics | The Starting Center
  6 | Nuggets | The Backup Center

Suppose that we would like to split each string in the positions column into an array and then get the second element from each array.

We can use the following syntax to do so:

SELECT *,
       (string_to_array(positions,' '))[2] AS second_position
FROM athletes;

 id |  team   |      positions       | second_position 
----+---------+----------------------+-----------------
  1 | Mavs    | The Starting Guard   | Starting
  2 | Hawks   | The Backup Guard     | Backup
  3 | Kings   | The Starting Forward | Starting
  4 | Lakers  | The Backup Forward   | Backup
  5 | Celtics | The Starting Center  | Starting
  6 | Nuggets | The Backup Center    | Backup

The resulting table contains a new column named second_position that contains the second element in the array after the string in the positions column was split into an array.

Note: To get a different element from an array, simply change the 2 in the brackets to a different number.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Use string_agg with Distinct Values
PostgreSQL: How to Use string_agg with Multiple Columns
PostgreSQL: How to Use string_agg with Order By