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...
Show quoted text
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))x where 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.
Show quoted text
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 20By 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...
Show quoted text
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.