Re: searching array

On Mon, 17 Oct 2005, Matthew Peter wrote:

> Thanks for the reply. I'm using 8.0.3. I'm using
> something similiar to the example you gave. My
> postgresql install is on offline developement box and
> I would have to type it all out longhand.
>
> Shouldn't >= also return Carols records since she
> contains records GREATER THAN 10000? This is the
> problem I'm having, there's no errors, just no records
> matching the > (gt) part. Only exact matches.

Shouldn't 10000 >= ALL(foo) mean that 10000 is greater equal all elements
in foo, not all elements in foo are greater equal 10000? I'd think Carol
shouldn't be returned because 10000 is not greater equal 20000 or 25000.

> --- Joe Conway <mail(at)joeconway(dot)com> wrote:
>
> > Matthew Peter wrote:
> > > I was trying to search a array with a GTE to value
> > and
> > > it doesn't work. Is this supported? Or am I
> > missing
> > > something?
> > >
> > > SELECT * FROM sal_emp WHERE 10000 >= ALL
> > (pay_by_quarter);
> >
> > Works for me:
> >
> > CREATE TABLE sal_emp (
> > name text,
> > pay_by_quarter integer[],
> > schedule text[][]
> > );
> > CREATE TABLE
> >
> > INSERT INTO sal_emp VALUES (
> > 'Bill',
> > '{10000, 10000, 10000, 10000}',
> > '{{"meeting", "lunch"}, {"training",
> > "presentation"}}'
> > );
> > INSERT 164825 1
> >
> > INSERT INTO sal_emp VALUES (
> > 'Carol',
> > '{20000, 25000, 25000, 25000}',
> > '{{"breakfast", "consulting"}, {"meeting",
> > "lunch"}}'
> > );
> > INSERT 164826 1
> >
> > SELECT * FROM sal_emp WHERE 10000 >= ALL
> > (pay_by_quarter);
> > name | pay_by_quarter |
> > schedule
> >
> ------+---------------------------+-------------------------------------------
> > Bill | {10000,10000,10000,10000} |
> > {{meeting,lunch},{training,presentation}}
> > (1 row)
> >
> > Care to provide some more info? What version of
> > Postgres are you using,
> > what is the exact SQL that is failing, and what is
> > the error message
> > you're getting?
> >
> > Joe
> >
>
>
>
>
> __________________________________
> Yahoo! Music Unlimited
> Access over 1 million songs. Try it free.
> http://music.yahoo.com/unlimited/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>