PostgreSQL: How to Convert Array into Rows

Often in PostgreSQL you may want to convert an array into rows.

You can use the unnest function with the following syntax to do so:

SELECT id,
       team,
       unnest(positions) AS player_positions
FROM athletes;

This particular example will create a new column named player_positions that converts the values from the array in the positions column into rows.

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

Example: How to Convert Array into Rows 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', '{"Guard", "Forward", "Forward"}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{"Guard", "Center", "Guard"}');
INSERT INTO athletes VALUES (0003, 'Kings', '{"Guard", "Forward", "Center"}');
INSERT INTO athletes VALUES (0004, 'Lakers', '{"Forward", "Forward", "Forward"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nuggets', '{"Guard", "Center", "Forward"}');

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

 id |  team   |         positions         
----+---------+---------------------------
  1 | Mavs    | {Guard,Forward,Forward}
  2 | Hawks   | {Guard,Center,Guard}
  3 | Kings   | {Guard,Forward,Center}
  4 | Lakers  | {Forward,Forward,Forward}
  5 | Celtics | {Guard,Center,Center}
  6 | Nuggets | {Guard,Center,Forward}

Suppose that we would like to convert each of the array values in the positions column into their own rows.

We can use the following syntax to do so:

SELECT id,
       team,
       unnest(positions) AS player_positions
FROM athletes;

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

Notice that the new column named player_positions contains each of the values from the arrays in the positions column each on their own row.

Feel free to use similar syntax to convert arrays into rows 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: Select Rows where Value NOT in Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty