array in sql query code example

Example: get a row where have list of array in SQL

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)


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