Often in PostgreSQL you may want to check if an array is contained in another array.
You can use the following syntax to do so:
SELECT *,
sold_products <@ all_products AS products_contained
FROM sales;
This particular example will check if the array named sold_products is contained in the array named all_products of the table named sales.
This will return either true or false as a result and save these results to a new column named products_contained.
The following example shows how to use this syntax in practice.
Example: Check if Array is Contained in Another Array in PostgreSQL
Suppose that we create the following table named sales that contains information about all of the products (all_products) sold by a particular retail store along with the actual products (sold_products) that were sold:
-- create table named 'athletes'
CREATE TABLE sales (
id INTEGER,
all_products TEXT[],
sold_products TEXT[]
);
-- add rows to the table
INSERT INTO sales VALUES (0001, '{"A", "B", "C", "D"}', '{"A", "B"}');
INSERT INTO sales VALUES (0002, '{"A", "B", "C", "D", "E"}', '{"A", "F"}');
INSERT INTO sales VALUES (0003, '{"A", "B", "C"}', '{"C", "E"}');
INSERT INTO sales VALUES (0004, '{"A", "B", "C"}', '{"A", "B", "C"}');
INSERT INTO sales VALUES (0005, '{"A", "B"}', '{"A", "F"}');
-- view all rows in table
SELECT * FROM sales;
id | all_products | sold_products
----+--------------+---------------
1 | {A,B,C,D} | {A,B}
2 | {A,B,C,D,E} | {A,F}
3 | {A,B,C} | {C,E}
4 | {A,B,C} | {A,B,C}
5 | {A,B} | {A,F}
Suppose that we would like to check if each array in the sold_products column is contained in the corresponding array in the all_products column.
We can use the following syntax to do so:
SELECT *,
sold_products <@ all_products AS products_contained
FROM sales;
id | all_products | sold_products | products_contained
----+--------------+---------------+--------------------
1 | {A,B,C,D} | {A,B} | t
2 | {A,B,C,D,E} | {A,F} | f
3 | {A,B,C} | {C,E} | f
4 | {A,B,C} | {A,B,C} | t
5 | {A,B} | {A,F} | f
The resulting table has a new column named products_contained that has either a true or false value to indicate whether or not the array in the sold_products column is contained in the corresponding array in the all_products column.
Feel free to use similar syntax to check if an array is contained in another array in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Random Value from Array
PostgreSQL: How to Slice an Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty
