Often in PostgreSQL you may want to convert a text column to an array.
You can use the string_to_array() function with the following syntax to do so:
SELECT *,
string_to_array(points,',') AS points_array
FROM athletes;
This particular example converts the text in the points column of the athletes table to an array column named points_array.
Note that we use the string_to_array() function to convert the text to an array and we specify that a comma should be used as the delimiter for splitting the text.
The following example shows how to use this syntax in practice.
Example: How to Convert Text to 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 TEXT
);
-- 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 is currently a text column.
However, we can use the string_to_array() function with the following syntax to convert it to an array column instead:
SELECT *,
string_to_array(points,',') AS points_array
FROM athletes;
id | team | points | points_array
----+---------+----------------+------------------
1 | Mavs | 12.1,15.6,17.8 | {12.1,15.6,17.8}
2 | Hawks | 22.5,16.5,18.7 | {22.5,16.5,18.7}
3 | Rockets | 20.1,24.4,34.6 | {20.1,24.4,34.6}
4 | Spurs | 29.4,30.5,31.2 | {29.4,30.5,31.2}
5 | Kings | 12.6,15.5,17.9 | {12.6,15.5,17.9}
6 | Celtics | 20.2,23.4,26.6 | {20.2,23.4,26.6}
7 | Lakers | 18.8,20.1,23.5 | {18.8,20.1,23.5}
Notice that the new column named points_array contains the text from the points column, formatted as an array instead.
Note: The benefit of converting a text column into an array column is that you can perform operations on the array such as finding the length, the minimum value, the maximum value, etc.
Feel free to use similar syntax to convert text to an array 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
