PostgreSQL: How to Split One Column into Multiple Columns

Often in PostgreSQL you may want to split one column into multiple columns.

You can use the split_part() function with the following syntax to do so:

SELECT split_part(player_info, ' ', 1) AS team_name,
       split_part(player_info, ' ', 2) AS status,
       split_part(player_info, ' ', 3) AS position
FROM athletes;

This particular example splits the string in the player_info column (using a space as a delimiter) into the following three columns:

• The first element is assigned to a new column named team_name.
• The second element is assigned to a new column named status.
• The third element is assigned to a new column named position.

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

Example: Split One Column into Multiple Columns 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,
  player_info TEXT
);

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

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

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

Suppose that we would like to split each string in the player_info column into three columns.

We can use the following syntax to do so:

SELECT split_part(player_info, ' ', 1) AS team_name,
       split_part(player_info, ' ', 2) AS status,
       split_part(player_info, ' ', 3) AS position
FROM athletes;

 team_name |   role   | position 
-----------+----------+----------
 Mavs      | Starting | Guard
 Hawks     | Backup   | Guard
 Kings     | Starting | Forward
 Lakers    | Backup   | Forward
 Celtics   | Starting | Center
 Nuggets   | Backup   | Center

The resulting table contains  three new columns named team_name, role and position that all contain an element from the original string in the player_info column.

You can use similar syntax with the split_part() function to split one column into multiple columns in your own table.

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