Обсуждение: 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

Peter Hunsberger <peter.hunsberger@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

> Peter Hunsberger <peter.hunsberger@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