searching array

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

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

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/

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

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/

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

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/

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

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/

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.

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

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/