From: | Medi Montaseri <medi(dot)montaseri(at)intransa(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Arrays ... need clarification.... |
Date: | 2003-04-08 00:21:28 |
Message-ID: | 3E921608.20907@intransa.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Stover | 2003-04-08 00:39:34 | Re: vacuum by non-owner |
Previous Message | Tom Lane | 2003-04-08 00:02:07 | Re: Backpatch FK changes to 7.3 and 7.2? |