Postgres Pro
Downloads
Home   >   mailing lists

Thread: questing using array

questing using array

From
Kevin Heflin
Date:
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
--------------------------------------------------------------------

Re: [GENERAL] questing using array

From
Zakkr
Date:

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

Re: [GENERAL] questing using array

From
Peter Eisentraut
Date:
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