get a row where have list of array in sql

Solutions on MaxInterview for get a row where have list of array in sql by the best coders in the world

showing results for - "get a row where have list of array in sql"
Louna
20 Jan 2016
1CREATE TABLE my_table (
2    id serial PRIMARY KEY,
3    numbers INT []
4);
5
6INSERT INTO my_table (numbers) VALUES ('{2, 3, 4}');
7INSERT INTO my_table (numbers) VALUES ('{2, 1, 4}');
8
9-- which means --
10test=# select * from my_table;
11 id | numbers 
12----+---------
13  1 | {2,3,4}
14  2 | {2,1,4}
15(2 rows)
16
17
18To check if an array contains parts of another array, you would use the && operator
19
20&& -- overlap (have elements in common) -- ARRAY[1,4,3] && ARRAY[2,1] --> true
21
22SELECT * FROM my_table WHERE numbers && '{1,2}'::int[];
23To check if an array contains all members of another array, you would use the @> operator
24
25@> -- contains -- ARRAY[1,4,3] @> ARRAY[3,1] --> true
26
27SELECT * FROM my_table WHERE numbers @> '{1,2}'::int[];