Arrays ... need clarification....

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

Responses

Browse pgsql-general by date

  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?