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

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