Postgres Pro
Downloads
Home   >   mailing lists

Thread: searching array

searching array

From
Matthew Peter
Date:
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);



__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: searching array

From
Joe Conway
Date:
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

Re: searching array

From
Tom Lane
Date:
Matthew Peter <survivedsushi@yahoo.com> writes:
> 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);

Define "doesn't work".  Do you get an error --- if so, what exactly?
Do you get unexpected results --- if so, what?  And what version of
Postgres are you using?

AFAIK it works in 7.4 and up:

regression=# select 100 >= all(array[1,2,4]);
 ?column?
----------
 t
(1 row)

regression=# select 100 >= all(array[1,2,101]);
 ?column?
----------
 f
(1 row)


            regards, tom lane

Re: searching array

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

How about using > (gt) instead of >= (gte)?

It doesn't return any records right? Shouldn't it
match Carols also since sal_emp is _int?


--- Joe Conway <mail@joeconway.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/

Re: searching array

From
Matthew Peter
Date:
Alright, what about with ANY?

--- Joe Conway <mail@joeconway.com> wrote:

> Matthew Peter wrote:
> > 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.
>
> Look again at your query:
>
> SELECT * FROM sal_emp WHERE 10000 >= ALL
> (pay_by_quarter);
>
> You are asking for matches where 10000 is greater
> than or equal to all
> the elements in the array. In Carol's case 10000 is
> less than all the
> elements -- hence no match.
>
> Joe
>




__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: searching array

From
Joe Conway
Date:
Matthew Peter wrote:
> 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.

Look again at your query:

SELECT * FROM sal_emp WHERE 10000 >= ALL (pay_by_quarter);

You are asking for matches where 10000 is greater than or equal to all
the elements in the array. In Carol's case 10000 is less than all the
elements -- hence no match.

Joe

Re: searching array

From
Tom Lane
Date:
Matthew Peter <survivedsushi@yahoo.com> writes:
> Shouldn't >= also return Carols records since she
> contains records GREATER THAN 10000?

You seem to be reading the construct backwards.

    x >= ALL (array)

is true if x >= every member of the array.  This is clearly false
for x = 10000 and array = {20000, 25000, 25000, 25000} ... in fact,
x isn't >= any of those members.

            regards, tom lane

Re: searching array

From
Matthew Peter
Date:
Thanks. That syntax didn't look right to find values
gte 10000. But thanks everyone!



--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Matthew Peter <survivedsushi@yahoo.com> writes:
> > Yes. I did read it wrong. I wanted to find all
> records
> > that contained x where x >= 10000
>
> Then flip it around:
>
>     contain x where 10000 <= x
>
>     10000 <= ANY (array)
>
> For syntactic reasons, there's no "ANY(array) >= x"
> construct,
> so you have to write it this way.
>
>             regards, tom lane
>





__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

Re: searching array

From
Stephan Szabo
Date:
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@joeconway.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
>

Re: searching array

From
Matthew Peter
Date:
Yes. I did read it wrong. I wanted to find all records
that contained x where x >= 10000

I am using ANY in my query on my test box I copied the
ALL from example query in the docs, which still isn't
working for me.

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Matthew Peter <survivedsushi@yahoo.com> writes:
> > Shouldn't >= also return Carols records since she
> > contains records GREATER THAN 10000?
>
> You seem to be reading the construct backwards.
>
>     x >= ALL (array)
>
> is true if x >= every member of the array.  This is
> clearly false
> for x = 10000 and array = {20000, 25000, 25000,
> 25000} ... in fact,
> x isn't >= any of those members.
>
>             regards, tom lane
>




__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: searching array

From
Matthew Peter
Date:
I have it backwards huh? Since the variables are
switched around in a ANY search

I want a SELECT * FROM table WHERE arrcol >= 10000

How do I write it to get those results?



--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Matthew Peter <survivedsushi@yahoo.com> writes:
> > Shouldn't >= also return Carols records since she
> > contains records GREATER THAN 10000?
>
> You seem to be reading the construct backwards.
>
>     x >= ALL (array)
>
> is true if x >= every member of the array.  This is
> clearly false
> for x = 10000 and array = {20000, 25000, 25000,
> 25000} ... in fact,
> x isn't >= any of those members.
>
>             regards, tom lane
>




__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: searching array

From
Tom Lane
Date:
Matthew Peter <survivedsushi@yahoo.com> writes:
> Yes. I did read it wrong. I wanted to find all records
> that contained x where x >= 10000

Then flip it around:

    contain x where 10000 <= x

    10000 <= ANY (array)

For syntactic reasons, there's no "ANY(array) >= x" construct,
so you have to write it this way.

            regards, tom lane

Re: searching array

From
Matthew Peter
Date:
Let me also say that I'm retarded. No excuses from me.
I'm officially retarded.



--- Matthew Peter <survivedsushi@yahoo.com> wrote:

> Thanks. That syntax didn't look right to find values
> gte 10000. But thanks everyone!
>
>
>
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Matthew Peter <survivedsushi@yahoo.com> writes:
> > > Yes. I did read it wrong. I wanted to find all
> > records
> > > that contained x where x >= 10000
> >
> > Then flip it around:
> >
> >     contain x where 10000 <= x
> >
> >     10000 <= ANY (array)
> >
> > For syntactic reasons, there's no "ANY(array) >=
> x"
> > construct,
> > so you have to write it this way.
> >
> >             regards, tom lane
> >
>
>
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>




__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/