How to split an array-column?

[Andreas]

Hi,

I've got a table to import from csv that has an array-column like:

import ( id, array_col, ... )

Those arrays look like ( 42, ";4941;4931;4932", ... )
They can have 0 or any number of elements separated by ;

So I'd need a result like this:
42, 4941
42, 4931
42, 4932

How would I get this?


--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Venky Kandaswamy]

You can try

select id, unnest(array_col) from table

....
________________________________________

Venky Kandaswamy

Principal Engineer, Adchemy Inc.

925-200-7124

________________________________________
From: pgsql-s...@postgresql.org [pgsql-s...@postgresql.org] on behalf of Andreas [map...@gmx.net]
Sent: Monday, March 18, 2013 12:13 PM
To: pgsq...@postgresql.org
Subject: [SQL] How to split an array-column?

[Andreas]

Thanks for the pointer.
It got me half way.

This is the solution:

select distinct id, unnest ( string_to_array ( trim ( array_column, ';'
), ';' ) ) from import;