CODEWITHSUNDEEP
postgresql
billdoor
billdoor

Reputation: 2043

postgres ANY() with BETWEEN Condition

in case someone is wondering, i am recycling a different question i answered myself, because is realized that my problem has a different root-cause than i thought:

My question actually seems pretty simple, but i cannot find a way.

How do is query postgres if any element of an array is between two values?

The Documentation states that a BETWEEN b and c is equivalent to a > b and a < c

This however does not work on arrays, as

ANY({1, 101}) BETWEEN 10 and 20 has to be false

while

ANY({1,101}) > 10 AND ANY({1,101}) < 20 has to be true.

{1,101} meaning an array containing the two elements 1 and 101.

how can i solve this problem, without resorting to workarounds?

regards,

BillDoor

EDIT: for clarity:

The scenario i have is, i am querying an xml document via xpath(), but for this problem a column containing an array of type int[] does the job.

id::int | numbers::int[]  | name::text
1       | {1,3,200}       | Alice
2       | {21,100}        | Bob

I want all Names, where there is a number that is between 20 and 30 - so i want Bob

The query 

SELECT name from table where  ANY(numbers) > 20 AND ANY(numbers) < 30

will return Alice and Bob, showing that alice has numbers > 20 as well as other numbers < 30.

A BETWEEN syntax is not allowed in this case, however between only gets mapped to > 20 AND < 30 internally anyways

Quoting the docs on the Between Operators' mapping to > and < documentation:

There is no difference between the two respective forms apart from the CPU cycles required to rewrite the first one into the second one internally.

PS.:

Just to avoid adding a new question for this: how can i solve

id::int | numbers::int[]  | name::text
1       | {1,3,200}       | Alice
2       | {21,100}        | Alicia

SELECT id FROM table WHERE ANY(name) LIKE 'Alic%'
result: 1, 2

i can only find examples of matching one value to multiple regex, but not matching one regex against a set of values :/. Besides the shown syntax is invalid, ANY has to be the second operand, but the second operand of LIKE has to be the regex.

Upvotes: 3

Views: 4903

Answers (2)

Dror Dromi
Dror Dromi

Reputation: 163

Instead of using ANY you can check the min and max val

100 > MIN(t2.ages) AND 100 < MAX(t2.ages)

Upvotes: 0

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

 exists (select * from (select unnest(array[1,101]) x ) q1 where x between 10 and 20 )

you can create a function based on on this query

second approach:

 select  int4range(10,20,'[]') @> any(array[1, 101])

for timestamps and dates its like: 

 select tsrange( '2015-01-01'::timestamp,'2015-05-01'::timestamp,'[]') @> any(array['2015-05-01', '2015-05-02']::timestamp[])

for more info read: range operators

Upvotes: 11

Related Questions