Say I have a table with an array column:
id | subIds
1 | {1,2,3}
2 | {4,5}
How would I return the resultset:
id | subId
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5
... in a single query without using a function?
Add a comment
|
1 Answer
By "without using a function" I assume you mean "without writing my own function to do it".
The unnest() function will do what you want
select id, unnest(subids) as subid
from the_table;
The order on how the elements are returned is undefined though.
2 Comments
SpliFF
Thank you. Your assumption is correct. Fortunately the order isn't important but I assume an 'ORDER BY' clause would work if it was?
@SpliFF: yes that would work. I was referring to the order in which the elements from the array are returned. Though in reality they are always returned in the order they were stored in the array - but you can't rely on that. The upcoming Postgres 9.4 will have
with ordinality which gives you better control over that.