Thread: generic way to retrieve array as rowset
When storing data in an array, like this
id array
1, {1,2}
2, {10,20}
3, {100,200}
is there a generic way to retrieve them as arowset, like this
id array_dimension1
1 1
1 2
2 10
2 20
By writing something like this:
Select id, explode(array) From foo Where id<3
(I know - store it as a table instead of an array, but beside that.)
Thanks.
Balázs
am 03.01.2006, um 12:37:51 +0100 mailte SunWuKung folgendes:
> When storing data in an array, like this
>
> id array
> 1, {1,2}
> 2, {10,20}
> 3, {100,200}
Forever 2 Elements in the array? Then:
test=# select * from t1;
id | foo
----+-----------
1 | {1,2}
2 | {10,20}
3 | {100,200}
(3 rows)
test=# select id, array_upper(foo,1), foo[idx.i] from t1, generate_series (1, 2) idx(i);
id | array_upper | foo
----+-------------+-----
1 | 2 | 1
1 | 2 | 2
2 | 2 | 10
2 | 2 | 20
3 | 2 | 100
3 | 2 | 200
(6 rows)
If not: http://www.varlena.com/GeneralBits/105.php
Read the chapter 'Querying and Flattening Array Columns'.
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
Unfortunately the number of elements in the array is not known
beforehand. The dimension of the array is always 1, but the number of
elements changes from 50-500.
I looked at the article you mention and it creates a set returning
function. I found some functions like that in the archive
- like select * from explode(array) but I don't see how to join that to
the original table the array was in.
In article <20060103120223.GC9478@webserv.wug-glas.de>,
andreas.kretschmer@schollglas.com says...
> am 03.01.2006, um 12:37:51 +0100 mailte SunWuKung folgendes:
> > When storing data in an array, like this
> >
> > id array
> > 1, {1,2}
> > 2, {10,20}
> > 3, {100,200}
>
> Forever 2 Elements in the array? Then:
>
> test=# select * from t1;
> id | foo
> ----+-----------
> 1 | {1,2}
> 2 | {10,20}
> 3 | {100,200}
> (3 rows)
>
> test=# select id, array_upper(foo,1), foo[idx.i] from t1, generate_series (1, 2) idx(i);
> id | array_upper | foo
> ----+-------------+-----
> 1 | 2 | 1
> 1 | 2 | 2
> 2 | 2 | 10
> 2 | 2 | 20
> 3 | 2 | 100
> 3 | 2 | 200
> (6 rows)
>
>
> If not: http://www.varlena.com/GeneralBits/105.php
> Read the chapter 'Querying and Flattening Array Columns'.
>
>
> HTH, Andreas
>
am 03.01.2006, um 13:24:54 +0100 mailte SunWuKung folgendes:
> Unfortunately the number of elements in the array is not known
> beforehand. The dimension of the array is always 1, but the number of
> elements changes from 50-500.
Okay.
test=# select * from t1;
id | foo
----+-----------------
1 | {1,2}
2 | {10,20}
3 | {100,200}
4 | {1,2,3,4,5,6,7}
(4 rows)
test=# select * from (select id, foo[idx.i] from t1, generate_series (1, (select max(array_upper(foo,1)) from t1))
idx(i))x;
id | foo
----+-----
1 | 1
1 | 2
1 |
1 |
1 |
1 |
1 |
2 | 10
2 | 20
2 |
2 |
2 |
2 |
2 |
3 | 100
3 | 200
3 |
3 |
3 |
3 |
3 |
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
(28 rows)
test=#
test=# select * from (select id, foo[idx.i] from t1, generate_series (1, (select max(array_upper(foo,1)) from t1))
idx(i))xwhere foo is not NULL;
id | foo
----+-----
1 | 1
1 | 2
2 | 10
2 | 20
3 | 100
3 | 200
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
(13 rows)
test=#
ugly, i know...
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
Hi,
Here's a modified version of A. Kretschmer's answer. This one checks
array_upper() sizes and depending on it, doesn't provide unnecessary
NULL fields. HTH.
SELECT id, val[s.i]
FROM t7
LEFT JOIN
(SELECT g.s
FROM generate_series(1,
(SELECT max(array_upper(val, 1)) FROM t7)) AS g(s)
) AS s(i)
ON (s.i <= array_upper(val, 1));
Query is inspired by the pg_database_config view in newsysview.
(Thanks AndrewSN for pointing out the source.)
Regards.
On Jan 03 12:37, SunWuKung wrote:
> When storing data in an array, like this
>
> id array
> 1, {1,2}
> 2, {10,20}
> 3, {100,200}
>
> is there a generic way to retrieve them as arowset, like this
>
> id array_dimension1
> 1 1
> 1 2
> 2 10
> 2 20
>
> By writing something like this:
>
> Select id, explode(array) From foo Where id<3
am 03.01.2006, um 16:42:08 +0200 mailte Volkan YAZICI folgendes: > Hi, > > Here's a modified version of A. Kretschmer's answer. This one checks > array_upper() sizes and depending on it, doesn't provide unnecessary > NULL fields. HTH. > > SELECT id, val[s.i] > FROM t7 > LEFT JOIN > (SELECT g.s > FROM generate_series(1, > (SELECT max(array_upper(val, 1)) FROM t7)) AS g(s) > ) AS s(i) > ON (s.i <= array_upper(val, 1)); Cool ;-) Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
Thank you both, I will make good use of this. On a side note: isn't it a pity this has to be so difficult? Balázs In article <20060103151044.GF9478@webserv.wug-glas.de>, andreas.kretschmer@schollglas.com says... > am 03.01.2006, um 16:42:08 +0200 mailte Volkan YAZICI folgendes: > > Hi, > > > > Here's a modified version of A. Kretschmer's answer. This one checks > > array_upper() sizes and depending on it, doesn't provide unnecessary > > NULL fields. HTH. > > > > SELECT id, val[s.i] > > FROM t7 > > LEFT JOIN > > (SELECT g.s > > FROM generate_series(1, > > (SELECT max(array_upper(val, 1)) FROM t7)) AS g(s) > > ) AS s(i) > > ON (s.i <= array_upper(val, 1)); > > Cool ;-) > > > Andreas >
SunWuKung schrieb: > Thank you both, I will make good use of this. > > On a side note: isn't it a pity this has to be so difficult? Well the pity is your data model - or the lack of it ;)) If redesign is possible, you probably want to change from array to real connected table. *wink* ;) Tino
Tino Wildenhain <tino@wildenhain.de> schrieb: > *wink* ;) > Tino hehe ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Maybe you are right. I am currently storing these values in a separate table and was looking for a way to optimize it. Without trying to pretend to be an expert on this I thought that using arrays, storing calculated values, or using materialized views in a database all violate the rules of normalization. I thought that you do all of these for optimization: use matviews or store calculated values when you want to access your data very often, and use arrays when you want to access your data within the array very rarely. My reasoning was why to store many millions of rows in a table when I would only need less than 1% of that frequently and the rest probably only once in the live system (we would need to analyze that offline). The data comes in as a single update of the array field (fast), than I put the items I will need into a separate table with a function - admiteddly it takes time to do that but I only need to do that once - and I will query the resulting table many times. So it seemed logical. Independently from my case I found a few topics about creating a rowset from an array in the archive which suggested that its not only me who could benefit from an easier way to manipulate this datatype. regards, Balázs -----Original Message----- From: Tino Wildenhain [mailto:tino@wildenhain.de] Sent: 2006. január 3. 18:59 To: SunWuKung Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] generic way to retrieve array as rowset SunWuKung schrieb: > Thank you both, I will make good use of this. > > On a side note: isn't it a pity this has to be so difficult? Well the pity is your data model - or the lack of it ;)) If redesign is possible, you probably want to change from array to real connected table. *wink* ;) Tino
SunWuKung <Balazs.Klein@axelero.hu> writes: > Thank you both, I will make good use of this. > > On a side note: isn't it a pity this has to be so difficult? It doesn't have to be. Look in the contrib directory, build the intagg module (or if you use debian install the postgresql-contrib package) and then: SELECT id, int_array_enum(val) FROM t7 -- greg
am 03.01.2006, um 18:19:12 -0500 mailte Greg Stark folgendes: > Look in the contrib directory, build the intagg module (or if you use debian > install the postgresql-contrib package) and then: > > SELECT id, int_array_enum(val) FROM t7 Cool, it works perfectly. Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
In article <87ek3o6gsv.fsf@stark.xeocode.com>, gsstark@mit.edu says... > > SunWuKung <Balazs.Klein@axelero.hu> writes: > > > Thank you both, I will make good use of this. > > > > On a side note: isn't it a pity this has to be so difficult? > > It doesn't have to be. > > Look in the contrib directory, build the intagg module (or if you use debian > install the postgresql-contrib package) and then: > > SELECT id, int_array_enum(val) FROM t7 > > Unfortunately I now work on a popular, but cripled proprietary system with no C compiler (WinXP), so I don't think I can make use of that yet - I will change though (one of those new year wows). Thanks. B.