PostgreSQL: Flattening a relation with an array to emit one row per array entry
You can put the set-returning function unnest() into the SELECT list like Raphaël suggests. But in Postgres 9.3 or later use a LATERAL join for this instead. It is the cleaner, preferable, standard-compliant way to put set-returning functions into the FROM list, not into the SELECT list:
SELECT name, valueFROM tbl, unnest(values) value; -- implicit CROSS JOIN LATERALOne 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.valueFROM tbl tLEFT JOIN unnest(t.values) v(value) ON true;
Well, you give the data, the doc, so... let's mix it ;)
select name, unnest(values) as value from test_valuessee SqlFiddle