有条件之间的postgres ANY（） [英] postgres ANY() with BETWEEN Condition

问题描述

万一有人想知道，我正在回答一个我自己回答的问题，因为意识到我的问题的根本原因与我想像的不同：

我的问题实际上看起来很简单，但我找不到方法。

如果数组的任何元素在两个值之间，如何查询postgres？

文档指出b和c之间的 a等同于 a> b和a< c

但这不适用于数组，因为

在10和20之间的任何（{1，101}）必须 false

而

ANY（{1,101}）> 10 AND ANY（{1,101}）< 20 必须为 true 。

{1,101}表示包含两个元素1和101的数组。 / p>

我该如何解决此问题，而无需采取解决方法？

致谢，

BillDoor

编辑：为了清楚起见：

我遇到的情况是，我正在通过xpath（）查询xml文档，但是对于这个问题，包含int []类型数组的列可以完成这项工作。

  id :: int |数字:: int [] |名称：：文本
 1 | {1,3,200} |爱丽丝
 2 | {21,100} | Bob 
  

我想要所有带有编号的名字在 20 和 30 之间-所以我想要 Bob

查询

 从表中选择名称（数字）> 20和任何（数字）< 30 
  

将返回 Alice 和 Bob ，表明爱丽丝的数字> 20以及其他数字<

在这种情况下，不允许使用BETWEEN语法，但是之间只能映射到> 20 AND<内部总共30个

引用运营商之间映射到>和< 文档：

除了将内部第一个重写为第二个
所需的
CPU周期外，两种形式之间没有区别。

PS。

只是要避免为此添加一个新问题：我该如何解决

  id :: int |数字:: int [] |名称：：文本
 1 | {1,3,200} |爱丽丝
 2 | {21,100} | Alicia 
 
从表中的SELECT ID WHERE ANY（name）像'Alic％'
结果：1，2 
  

我只能找到将一个值与多个正则表达式匹配的示例，而不能将一个正则表达式与一组值：/匹配。除了显示的语法无效之外，ANY还必须是第二个操作数，而LIKE的第二个操作数必须是正则表达式。

解决方案

 存在（从（选择* unnest（array [1,101]）x）中选择*）q1其中x在10到20之间）
  

您可以基于此查询创建函数

第二种方法：

 选择int4range（10,20，'[]'）@> any（array [1，101]）$ ​​b $ b  

用于时间戳和日期，如：

 选择tsrange（'2015-01-01':: timestamp，'2015-05-01':: timestamp，'[]'）@ > any（array ['2015-05-01'，'2015-05-02'] :: timestamp []）
  

有关更多信息，请阅读：范围运算符

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.

解决方案

 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

这篇关于有条件之间的postgres ANY（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！