[GENERAL] Craeteing sparse arrays
Is there any easy way to create sparse arrays with Postres?
Specifically, when I construct, add or aggregate data to an array, I
want to be able to specify the position within the array where the
data is placed and have any intervening positions that have not yet
been populated just marked as nulls. eg, something like
insert into foo (bar[3],[7]) values ( 'a', 'b')
would build an array
bar = { null, null, 'a', null, null, null, 'b' }
or some such thing. I suspect I'm going to have to write a function
to just find the length and append nulls until I reach the desired
position? Given that some of the arrays I will be dealing with could
potentially be 1000s of elements long that seems a bit perverse.
I'm currently using 8.3 but 8.4 solutions are also welcome. C code
not considered out of the question if it isn't a lot of work and will
make the rest of the process close to trivial...
--
Peter Hunsberger
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Craeteing sparse arrays
Peter Hunsberger peter.hunsber...@gmail.com writes:
Is there any easy way to create sparse arrays with Postres?
Have you tried it?
regression=# create table foo (bar text[]);
CREATE TABLE
regression=# insert into foo (bar[3],bar[7]) values ( 'a', 'b');
INSERT 0 1
regression=# select * from foo;
bar
[3:7]={a,NULL,NULL,NULL,b}
(1 row)
In the last couple of releases, assigning to a nonexistent subscript
will fill nulls into positions between that and the existent ones,
so something like UPDATE foo SET bar[7] = 'b' will clearly do what
you want. The above syntax is less obvious but IIRC it's treated
as an assignment to bar[3] followed by an assignment to bar[7].
I wouldn't want to try working with very large arrays in PG, mind
you --- it's not terribly efficient with them.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Craeteing sparse arrays
Peter Hunsberger peter.hunsber...@gmail.com writes:
Is there any easy way to create sparse arrays with Postres?
Have you tried it?
regression=# create table foo (bar text[]);
CREATE TABLE
regression=# insert into foo (bar[3],bar[7]) values ( 'a', 'b');
INSERT 0 1
regression=# select * from foo;
bar
[3:7]={a,NULL,NULL,NULL,b}
(1 row)
In the last couple of releases, assigning to a nonexistent subscript
will fill nulls into positions between that and the existent ones,
so something like UPDATE foo SET bar[7] = 'b' will clearly do what
you want. The above syntax is less obvious but IIRC it's treated
as an assignment to bar[3] followed by an assignment to bar[7].
I wouldn't want to try working with very large arrays in PG, mind
you --- it's not terribly efficient with them.
regards, tom lane
You may be better off using hstore instead of straight arrays.
http://www.postgresql.org/docs/8.4/interactive/hstore.html
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
