Re: Craeteing sparse arrays

> 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