pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to split an array-column?
3+ messages / 2 participants
[nested] [flat]
* How to split an array-column?
@ 2013-03-18 19:13 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2013-03-18 19:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to split an array-column?
@ 2013-03-18 19:24 Venky Kandaswamy <venky@adchemy.com>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Venky Kandaswamy @ 2013-03-18 19:24 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to split an array-column?
@ 2013-03-18 20:43 Andreas <maps.on@gmx.net>
parent: Venky Kandaswamy <venky@adchemy.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andreas @ 2013-03-18 20:43 UTC (permalink / raw)
To: Venky Kandaswamy <venky@adchemy.com>; +Cc: 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
>
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-03-18 20:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-18 19:13 How to split an array-column? Andreas <maps.on@gmx.net>
2013-03-18 19:24 ` Venky Kandaswamy <venky@adchemy.com>
2013-03-18 20:43 ` Andreas <maps.on@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox