sqlarrayspostgresqlset-returning-functionslateral

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

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.

Solution

  • 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:

    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: