Обсуждение: How to split an array-column?
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?
You can try select id, unnest(array_col) from table .... ________________________________________ Venky Kandaswamy Principal Engineer, Adchemy Inc. 925-200-7124 ________________________________________ From: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org] on behalf of Andreas [maps.on@gmx.net] Sent: Monday, March 18, 2013 12:13 PM To: pgsql-sql@postgresql.org Subject: [SQL] How to split an array-column? 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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; Am 18.03.2013 20:24, schrieb Venky Kandaswamy: > You can try > > select id, unnest(array_col) from table > > .... > ________________________________________ > > Venky Kandaswamy > > Principal Engineer, Adchemy Inc. > > 925-200-7124 > > ________________________________________ > From: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org] on behalf of Andreas [maps.on@gmx.net] > Sent: Monday, March 18, 2013 12:13 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] How to split an array-column? > > 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 (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > >