SQL query, which search all rows with specific items of array column

Question

my sql case in postgres:9.6 database

CREATE TABLE my_table (
    id serial PRIMARY KEY,
    numbers INT []
);

INSERT INTO my_table (numbers) VALUES ('{2, 3, 4}');
INSERT INTO my_table (numbers) VALUES ('{2, 1, 4}');

-- which means --
test=# select * from my_table;
 id | numbers 
----+---------
  1 | {2,3,4}
  2 | {2,1,4}
(2 rows)

I need to find all rows with numbers 1 and/or 2. According this answer I use query like this:

SELECT * FROM my_table WHERE numbers = ANY('{1,2}'::int[]);

And got following error:

LINE 1: SELECT * FROM my_table WHERE numbers = ANY('{1,2}'::int[]);
                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

How does correct sql query look?

"I need to find all rows with numbers 1 and 2" Which means the expected result is 2 | {2,1,4} ? As that is the only record where 1 and 2 are — Raymond Nijland
– Raymond Nijland, Commented Jun 17, 2019 at 12:40

Raymond Nijland · Accepted Answer · 2019-06-17 12:47:07Z

2

Using var = ANY(array) works well for finding if a single value (var) is contained in an array.

To check if an array contains parts of another array, you would use the && operator

&& -- overlap (have elements in common) -- ARRAY[1,4,3] && ARRAY[2,1] --> true

SELECT * FROM my_table WHERE numbers && '{1,2}'::int[];

To check if an array contains all members of another array, you would use the @> operator

@> -- contains -- ARRAY[1,4,3] @> ARRAY[3,1] --> true

SELECT * FROM my_table WHERE numbers @> '{1,2}'::int[];

edited Jun 17, 2019 at 12:47

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

answered Jun 17, 2019 at 12:42

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

2 Comments

Raymond Nijland Over a year ago

@YegorZaremba still iam wondering which one you needed, as next time you should also provide expected results.. To make the question more clear.

Yegor Over a year ago

@raymond-nijland case with 1 or 2 numbers

Collectives™ on Stack Overflow

SQL query, which search all rows with specific items of array column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related