Flattening a relation with an array to emit one row per array entry

Question

Given a table defined as such:

CREATE TABLE test_values(name TEXT, values INTEGER[]);

...and the following values:

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |

I'm trying to find a query which will return:

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |

I've reviewed the upstream documentation on accessing arrays, and tried to think about what a solution using the unnest() function would look like, but have been coming up empty.

An ideal solution would be easy to use even in cases where there were a significant number of columns other than the array being expanded and no primary key. Handling a case with more than one array is not important.

Answer 1

We can put the set-returning function unnest() into the SELECT list like Raphaël suggests. This used to exhibit corner case problems before Postgres 10. See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Since Postgres 9.3 we can also use a LATERAL join for this. It is the cleaner, standard-compliant way to put set-returning functions into the FROM list, not into the SELECT list:

SELECT name, value
FROM   tbl, unnest(values) value;  -- implicit CROSS JOIN LATERAL

One subtle difference: this drops rows with empty / NULL values from the result since unnest() returns no row, while the same is converted to a NULL value in the FROM list and returned anyway. The 100 % equivalent query is:

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;

See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Answer 2

Well, you give the data, the doc, so... let's mix it ;)

select 
 name, 
 unnest(values) as value 
from test_values

see SqlFiddle