CODEWITHSUNDEEP
sqlarrayspostgresql
Yegor
Yegor

Reputation: 3950

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

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?

Upvotes: 0

Views: 6541

Answers (1)

JGH
JGH

Reputation: 17846

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[];

Upvotes: 2

Related Questions