CODEWITHSUNDEEP
arrayspostgresqlaggregate-functions
A.T.
A.T.

Reputation: 311

Postgres - From array to multiple rows

I have a table like:

STREET     | NUMBERS (varchar)
broadway   | 01, 03
helmet     | 01, 03, 07

And I would like to know if it is possible to get those arrays and make a new table disaggregating them like this:

oid | STREET     | NUMBERS
1   | broadway   | 01
2   | broadway   | 03
3   | helmet     | 01
4   | helmet     | 03
5   | helmet     | 07

From searching, I found that array_agg() does the exact opposite. Is there some kind of reverse array_agg() or any other method to get that result?

Also, it would be great if I could also get another column with the position that that row had in the original array:

oid | STREET     | NUMBERS | POSITION
1   | broadway   | 01      | 1
2   | broadway   | 03      | 2
3   | helmet     | 01      | 1
4   | helmet     | 03      | 2
5   | helmet     | 07      | 3

Thank you in advance

Upvotes: 2

Views: 1367

Answers (1)

klin
klin

Reputation: 121474

Use string_to_array() and unnest() with ordinality in a lateral join:

with my_table(street, numbers) as (
values
    ('broadway', '01, 03'),
    ('helmet', '01, 03, 07')
)

select street, number, position
from my_table
cross join unnest(string_to_array(numbers, ', ')) with ordinality as u(number, position)

  street  | number | position 
----------+--------+----------
 broadway | 01     |        1
 broadway | 03     |        2
 helmet   | 01     |        1
 helmet   | 03     |        2
 helmet   | 07     |        3
(5 rows)

Upvotes: 3

Related Questions