Thread

1. questing using array

   Kevin Heflin <kheflin@shreve.net> — 1999-10-12T18:28:08Z

   Just trying to get a handle on how to work with an array as a datatype.
   
   For exampel I set up a table: 
   
   name	varchar (20),
   ageids  int4[] 
   
   
   Made an INSERT like:
   
   insert into TABLENAME (name, ageids) values ('test', '{1, 2, 3, 4}');
   
   
   What I haven't been able to figure out is how to do a select where one of
   the ageids = a particular number.
   
   
   I'd like to do something like 
   select * from tablename where ( any ageids = 3 )
   
   just don't know the syntax.. if this is even possible. Any suggestions
   would be appreciated.
   
   
   Kevin
    
   
   
   
   
   --------------------------------------------------------------------
   Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
   VP/Production         | 333 Texas St #175    | FAX:318.221.6612
   kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
   --------------------------------------------------------------------
   
   
   

2. Re: [GENERAL] questing using array

   Karel Zak <zakkr@zf.jcu.cz> — 1999-10-13T10:06:39Z

   
   On Tue, 12 Oct 1999, Kevin Heflin wrote:
   
   > 
   > Just trying to get a handle on how to work with an array as a datatype.
   > 
   > For exampel I set up a table: 
   > 
   > name	varchar (20),
   > ageids  int4[] 
   > 
   > 
   > Made an INSERT like:
   > 
   > insert into TABLENAME (name, ageids) values ('test', '{1, 2, 3, 4}');
   > 
   > 
   > What I haven't been able to figure out is how to do a select where one of
   > the ageids = a particular number.
   > 
   > 
   > I'd like to do something like 
   > select * from tablename where ( any ageids = 3 )
   
   See the PostgreSQL contrib and array operators in this section.
   Example *=, **= ..etc.
   
   	select * from tablename where ageids *= 3;
   
   						Zakkr
   
   
   
    
   
   
   

3. Re: [GENERAL] questing using array

   Peter Eisentraut <peter_e@gmx.net> — 1999-10-13T20:09:39Z

   You might want to look into the contrib/array directory which has _some_
   helpers with arrays.
   
   But in general, using arrays in cases like yours is a bad idea because,
   a) It has nothing to do with relational database design
   b) Arrays were not designed for this kind of stuff, so you won't get very
   far.
   
   Regarding a), redesign your table like this
   name varchar(20)
   ageid int4
   
   and make the records like this:
   test 1
   test 2
   test 3
   test 4
   
   and then you can use a simple select to find your answer. (Yes, this looks
   like you're storing more data, but that is how things are supposed to
   work.)
   
   Regarding b), arrays were created mostly for geometric objects. Of course
   you still could ask questions like "give me a polygon that has some
   coordinate that has a 20 in it", but it's unlikely and specialty functions
   exist to deal with questions you would usually have.
   
   	-Peter
   
   
   
   On Oct 12, Kevin Heflin mentioned:
   > 
   > Just trying to get a handle on how to work with an array as a datatype.
   > 
   > For exampel I set up a table: 
   > 
   > name	varchar (20),
   > ageids  int4[] 
   > 
   > 
   > Made an INSERT like:
   > 
   > insert into TABLENAME (name, ageids) values ('test', '{1, 2, 3, 4}');
   > 
   > 
   > What I haven't been able to figure out is how to do a select where one of
   > the ageids = a particular number.
   > 
   > 
   > I'd like to do something like 
   > select * from tablename where ( any ageids = 3 )
   > 
   > just don't know the syntax.. if this is even possible. Any suggestions
   > would be appreciated.
   
   
   -- 
   Peter Eisentraut                  Sernanders vaeg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden