Arrays ... need clarification....
От | Medi Montaseri |
---|---|
Тема | Arrays ... need clarification.... |
Дата | |
Msg-id | 3E921608.20907@intransa.com обсуждение исходный текст |
Ответы |
Re: Arrays ... need clarification....
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: