SQL测试ARRAY是否包含整数 [英] SQL test if ARRAY contains integer

问题描述

我有一个SQL表，例如

I have a SQL TABLE like

CREATE TABLE Test ( ContentArray INTEGER ARRAY )`.
I can insert an column with `INSERT INTO Test ( ContentArray ) VALUES ( ARRAY[1,2,3,4] )

但是我不知道如何获取 ContentArray 包含整数3的所有列。

But I don't know how to get all columns where ContentArray contains integer 3.

例如：

INSERT INTO Test ( ContentArray ) VALUES ( ARRAY[1,2] )

INSERT INTO Test ( ContentArray ) VALUES ( ARRAY[3,4] )

INSERT INTO Test ( ContentArray ) VALUES ( ARRAY[4,5] )

INSERT INTO Test ( ContentArray ) VALUES ( ARRAY[5,6] )

SELECT ContentArray FROM Test WHERE /* ContentArray contains 5 and 6 */

应返回 ARRAY [5,6] 。

推荐答案

鉴于DBMS和一些快速研究，看起来SQL2008支持ANY关键字以允许对数组数据类型进行这种类型的查询：

Given the DBMS and some quick research, it looks like SQL2008 supports the ANY keyword to allow this type of query for array datatypes:

SELECT * FROM Test WHERE 3 = ANY (ContentArray);

我仍然不建议将数组数据类型用于不重要的事情，我不是确定以上查询的性能如何，但我确定它不如带有孩子的主表好。

I still wouldn't recommend using the array data type for anything non-trivial, I'm not sure what the performance of the above query would be like, but I'm sure it's not as good as a master table with children.

这篇关于SQL测试ARRAY是否包含整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！