Re: Arrays ... need clarification.... - Mailing list pgsql-general

Thank you very much Joe....related to Dr. Conway of the Perl community ?

May I also suggest the following,

The "insert into table (col, col, array_col) values ( val, val, '{}')"
construct is a good
model as it resembles a declaration with no value specified.

Also, currently as you noted, one has to fill the array sequentially, ie
no random array
access is allowed unless one has initialized it already. This appears to
be a limitation of
the underlying data structure. Perhaps you could allow for such random
accesses.

Perhaps you could also support the following

insert into Table (array[m:n]) values ('{0}')

to mean place valure zero in elements from array[m] to array[n].

Thanks

Joe Conway wrote:

> Medi Montaseri wrote:
>
>> Yes....your proposed method is indeed the traditional approach....but
>> since PG provides
>> Arrays, I figured "How Nice"....
>
>
> Don't get me wrong -- I like the feature (enough that I'm working on
> improving it for the next release), I just don't think this is a good
> application for it. But that's just IMHO ;-)
>
>> And the link does not provide much, I stopped by there first, before
>> asking....
>> All the examples on the link are using
>> insert into table values ( x, x, x , ...)
>> instead of
>> insert into table (col, col, ...) values ( val, val, ...)
>
>
> I agree that the documentation on arrays is pretty limited. Again, I
> hope to improve that before 7.4 is released. In your original question:
>
> > method-1:
> > insert into test ( name , grades) values ( 'user1', '{}');
> > select * from test where name = 'user1';
> > name | id
> > --------+--------
> > user1   | {}
> > update test set grades[1] = 10 where name = 'user1';
> > ERROR:  Invalid array subscripts
>
> I think you need to do this instead if the array is not null, but empty:
>
> update test set grades = '{10}' where name = 'user1';
>
> At that point you can add elements by specifying an index of plus or
> minus one from the array lower bound or upper bound:
>
> regression=# create table test ( name varchar(20) , grades integer[]);
> CREATE TABLE
> regression=# insert into test ( name , grades) values ( 'user1', '{}');
> INSERT 2466139 1
> regression=# update test set grades = '{10}' where name = 'user1';
> UPDATE 1
> regression=# update test set grades[0] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[-1] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[2] = 9 where name = 'user1';
> UPDATE 1
> regression=# update test set grades[3] = 9 where name = 'user1';
> UPDATE 1
> regression=# select * from test;
>  name  |    grades
> -------+--------------
>  user1 | {9,9,10,9,9}
> (1 row)
> regression=# select array_dims(grades) from test;
>  array_dims
> ------------
>  [-1:3]
> (1 row)
>
> Arrays default to a lower bound of 1, but you can change that by
> adding elements as shown.
>
> If you do stick with arrays, and want to do any analytics on the array
> data, take a look at PL/R. It makes array operations easy because R is
> a vector based language. For example:
>
> regression=# create or replace function array_avg(int[]) returns
> float8 as 'mean(arg1)' language 'plr';
> CREATE FUNCTION
> regression=# create table test ( name varchar(20) , grades integer[]);
> CREATE TABLE
> regression=# insert into test ( name , grades) values ( 'user1',
> '{91,87,75,96,91}');
> INSERT 2466243 1
> regression=# select array_avg(grades) from test;
>  array_avg
> -----------
>         88
> (1 row)
>
> You can download a copy at http://www.joeconway.com/ if interested,
> but you also need R compiled with the --enable-R-shlib option; for R
> see http://cran.r-project.org/
>
> HTH,
>
> Joe
>