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, Jan 09 '19 at 14:51
Duplicate of [Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables](https://stackoverflow.com/questions/9232572/splitting-a-comma-separated-field-in-postgresql-and-doing-a-union-all-on-all-the) — Kaushik Nayak, Jan 09 '19 at 14:55
It's similar, but not a duplicate, since I also ask about positioning in the original array. — A.T., Jan 09 '19 at 15:16
Postgres 9.4+ `with ordinality` is available for `regexp_split_to_table` https://dba.stackexchange.com/questions/27279/how-to-preserve-the-original-order-of-elements-in-an-unnested-array — Kaushik Nayak, Jan 09 '19 at 15:25

score 3 · Accepted Answer · answered Jan 09 '19 at 14:55

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)

Postgres - From array to multiple rows

1 Answers1