SQL test if ARRAY contains integer

Question

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] )

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

For example:

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 */

should return ARRAY[5,6].

Not sure what DBMS you are using, but it's almost always a bad idea to use array data types for fields in a relational database. The better choice is to store these values as children of the main table, then your query becomes simple. — John Kuhns
– John Kuhns, Commented Jul 2, 2015 at 15:40
CREATE TABLE master (somekey INTEGER) CREATE TABLE child (somekey INTEGER, value INTEGER) INSERT INTO master (1) INSERT INTO child (1,1) INSERT INTO child (1,3) INSERT INTO master (2) INSERT INTO child (2,1) INSERT INTO child (2,2) INSERT INTO master (3) INSERT INTO child (3,3) SELECT * FROM master INNER JOIN child ON master.somekey = child.somekey where child.value = 3 — John Kuhns
– John Kuhns, Commented Jul 2, 2015 at 15:44

John Kuhns · Accepted Answer · 2015-07-02 15:58:54Z

2

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.

answered Jul 2, 2015 at 15:58

John Kuhns

5064 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fredt Over a year ago

HSQLDB does not yet support 3 = ANY(ContentArray). It supports 3 IN(UNNEST(ContentsArray)) as well as POSITION_ARRAY(3 IN ContentArray) > 0 which performs better

mohe2015 Over a year ago

@fredt Thank you! I'll test what is faster ( and better for me ).

mohe2015 Over a year ago

SELECT * FROM Test WHERE POSITION_ARRAY(3 IN ContentArray) > 0 (617 ms) is faster than SELECT * FROM Test WHERE 3 IN(UNNEST(ContentArray)) (2118 ms). (1,220,608 rows retrieved, total rows: 4,882,432). That is fast enough for me. :)

Collectives™ on Stack Overflow

SQL test if ARRAY contains integer

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related