pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to generate a list of distinct scalar values from a column which type is array
6+ messages / 3 participants
[nested] [flat]
* how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-21 18:21 Sergio Andreozzi <sergio.andreozzi@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Sergio Andreozzi @ 2007-02-21 18:21 UTC (permalink / raw)
To: pgsql-sql
Dear all,
given a column which type is for instance varchar(20)[], is there any SQL
command that let me generate the list of distinct scalar values?
e.g.:
col1
row 1: (aaa, bb, c)
row 2: (dddd, eeee)
row 3: (aaa, eeee)
the query should return:
aaa
bb
c
dddd
eeee
if not, I guess the approach is to use stored procedure. Any code
snippet/suggestion?
Thanks, Sergio
^ permalink raw reply [nested|flat] 6+ messages in thread
* how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-22 11:38 Sergio Andreozzi <sergio.andreozzi@gmail.com>
parent: Sergio Andreozzi <sergio.andreozzi@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Sergio Andreozzi @ 2007-02-22 11:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Dear all,
given a column which type is for instance varchar(20)[], is it possible via
SQL to generate the list of distinct scalar values?
e.g.:
col1
row 1: (aaa, bb, c)
row 2: (dddd, eeee)
row 3: (aaa, eeee)
the query should return:
aaa
bb
c
dddd
eeee
if not, I guess the approach is to use stored procedure. Any code
snippet/suggestion?
Thanks, Sergio
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-22 12:28 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Sergio Andreozzi <sergio.andreozzi@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2007-02-22 12:28 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 21.02.2007, um 19:21:09 +0100 mailte Sergio Andreozzi folgendes:
> Dear all,
>
> given a column which type is for instance varchar(20)[], is there any SQL
> command that let me generate the list of distinct scalar values?
>
>
> e.g.:
> col1
> row 1: (aaa, bb, c)
> row 2: (dddd, eeee)
> row 3: (aaa, eeee)
>
> the query should return:
>
> aaa
> bb
> c
> dddd
> eeee
>
> if not, I guess the approach is to use stored procedure. Any code snippet/
> suggestion?
You can, for each row, split the array into the elements with a loop
over array_lower, array_upper, returns this element. Yes, this is a
setof-function. Than you can do a simple select distinct * from
your_function.
I hope this may help you.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-22 13:25 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Sergio Andreozzi <sergio.andreozzi@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2007-02-22 13:25 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 21.02.2007, um 19:21:09 +0100 mailte Sergio Andreozzi folgendes:
> Dear all,
>
> given a column which type is for instance varchar(20)[], is there any SQL
> command that let me generate the list of distinct scalar values?
>
>
> e.g.:
> col1
> row 1: (aaa, bb, c)
> row 2: (dddd, eeee)
> row 3: (aaa, eeee)
>
> the query should return:
>
> aaa
> bb
> c
> dddd
> eeee
Okay, next solution:
test=*# select * from a;
c
-------------
{aaa,bb,c}
{dddd,eeee}
{aaa,eeee}
(3 rows)
test=*# select distinct c[s] from a, generate_series(1,3)s where c[s] is not null;
c
------
aaa
bb
c
dddd
eeee
(5 rows)
You need to know the greatest upper dimension of the array, in this case
3, for the generate_series - function.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-22 13:34 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2007-02-22 13:34 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 22.02.2007, um 14:25:36 +0100 mailte A. Kretschmer folgendes:
> test=*# select * from a;
> c
> -------------
> {aaa,bb,c}
> {dddd,eeee}
> {aaa,eeee}
> (3 rows)
>
>
> test=*# select distinct c[s] from a, generate_series(1,3)s where c[s] is not null;
> c
> ------
> aaa
> bb
> c
> dddd
> eeee
> (5 rows)
>
>
> You need to know the greatest upper dimension of the array, in this case
> 3, for the generate_series - function.
select distinct c[s] from a, generate_series(1,(select max(array_upper(c,1)) from a))s where c[s] is not null;
;-)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to generate a list of distinct scalar values from a column which type is array
@ 2007-02-22 16:56 David Fetter <david@fetter.org>
parent: Sergio Andreozzi <sergio.andreozzi@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Fetter @ 2007-02-22 16:56 UTC (permalink / raw)
To: Sergio Andreozzi <sergio.andreozzi@gmail.com>; +Cc: pgsql-general@postgresql.org
On Thu, Feb 22, 2007 at 12:38:50PM +0100, Sergio Andreozzi wrote:
> Dear all,
>
> given a column which type is for instance varchar(20)[],
This is almost never a good design. If you must have an interface
like that, make it VIEW over an aggregate, which you can make
writeable.
> is it possible via SQL to generate the list of distinct scalar
> values?
Yes, but you should fix your design :)
CREATE TABLE foo (ft TEXT[]);
COPY foo(ft) FROM stdin;
{aaa,bb,c}
{dddd,eeee}
{aaa,eeee}
\.
SELECT DISTINCT ft[i]
FROM (
SELECT ft,
generate_series(
array_lower(ft,1),
array_upper(ft,1)
) AS i
FROM foo
) AS bar;
So, yes, you can do it, and no, you shouldn't.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-02-22 16:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-21 18:21 how to generate a list of distinct scalar values from a column which type is array Sergio Andreozzi <sergio.andreozzi@gmail.com>
2007-02-22 11:38 ` Sergio Andreozzi <sergio.andreozzi@gmail.com>
2007-02-22 16:56 ` David Fetter <david@fetter.org>
2007-02-22 12:28 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-22 13:25 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-22 13:34 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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