Postgres - From array to multiple rows

Question

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

Does numbers column contain comma separated strings ( char/text type) or an array type as you are saying? — Kaushik Nayak
– Kaushik Nayak, Commented Jan 9, 2019 at 14:51
Duplicate of Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables — Kaushik Nayak
– Kaushik Nayak, Commented Jan 9, 2019 at 14:55
It's similar, but not a duplicate, since I also ask about positioning in the original array. — A.T.
– A.T., Commented Jan 9, 2019 at 15:16
Postgres 9.4+ with ordinality is available for regexp_split_to_table dba.stackexchange.com/questions/27279/… — Kaushik Nayak
– Kaushik Nayak, Commented Jan 9, 2019 at 15:25

klin · Accepted Answer · 2019-01-09 14:55:31Z

3

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)

answered Jan 9, 2019 at 14:55

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luis Over a year ago

That is beautiful!

Collectives™ on Stack Overflow

Postgres - From array to multiple rows

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related