I can use some clarification on Array usage....
Given the following definition....
create table test ( name varchar(20) , grades integer[]);
How do I insert a new record, I see multiple ways of doing it but if one
does
not do this right, then updates will fail....
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
Method-2
insert into test (name, grades) values ('user2', '{0}');
select * from test where name = 'user2';
user2 | {0}
update test set grades[0] = 10 where name = 'user2';
// checking the result
user2 | {10,0}
I thought Arrays are indexed from 1 (and not zero), but ....
update test set grades[1] = 20 where name = 'user2';
// check ing the output...
user2 | {10,20}
Method-3:
insert into test (name, id[0]) values ('user3', '{0}');
So which way is the correct way....also note that I need to do some
avg(), min(), max()
on these grades, so I hope setting first element to zero is not going to
mess my statistics.
It would fee more natural to be able to say
insert into test ( name, grades ) values ( 'joe', '{}');
That is Joe gets an empty set, instead of saying
insert into test ( name, grades[0]) values ( .....
But note that if one does this, it will fail in the update.....check
this....
insert into test (name, grades) values ('foo', '{}');
select * from test where name = 'foo';
foo | {}
update test set grades[1] = 10 where name = 'foo';
ERROR: Invalid array subscripts
Thanks