From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: how to generate a list of distinct scalar values from a column which type is array |
Date: | 2007-02-22 13:34:33 |
Message-ID: | 20070222133433.GF15488@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Tassonis | 2007-02-22 14:12:52 | Re: postgresql vs mysql |
Previous Message | Joris Dobbelsteen | 2007-02-22 13:27:17 | Guarenteeing ordering constraints |
From | Date | Subject | |
---|---|---|---|
Next Message | Sabin Coanda | 2007-02-22 13:47:14 | Re: How compare current_setting(..) ? |
Previous Message | A. Kretschmer | 2007-02-22 13:25:36 | Re: how to generate a list of distinct scalar values from a column which type is array |