Skip to content

1. Searching Array include ANY(), ALL() and SOME.

2. Can use them with subquery

3. Can use for compersion

To search for an element in an array in PostgreSQL, you can use the ANY, 'ALL', 'SOME' operators

in combination with the WHERE clause. Here's an example of how to do this:

SELECT * FROM table_name
WHERE array_column_name = ANY (ARRAY['element1', 'element2', 'element3']);

In this example, table_name is the name of the table, array_column_name is the name of the array column, and 'element1', 'element2', 'element3' are the elements you want to search for in the array column.

Spinner
DataFrameas
df1
variable

SELECT * FROM dvdrentals.address
LIMIT 5;
Spinner
DataFrameas
df
variable
-- Use ANY opertor to searching city names ['saselo', 'London', 'Sydney']
SELECT * FROM dvdrentals.address
WHERE city = ANY (ARRAY['Sasebo', 'London', 'Sydney']);

Here All() array opretors check if element in data if It use with Compersion opretors give True if element exist and FALSE is element not found

Spinner
DataFrameas
df2
variable
-- Use All operator to extract the address of '1566 Inegl Monor' 
SELECT * FROM dvdrentals.address
WHERE '1566 Inegl Manor' = ALL(array[address]);
Spinner
DataFrameas
df3
variable
-- Like statement give us the same result
SELECT * FROM dvdrentals.address
WHERE address LIKE '1566 Inegl Manor'
Spinner
DataFrameas
df4
variable
SELECT * FROM dvdrentals.address
-- Create subquery
WHERE address > ANY(SELECT dvdrentals.address.address FROM dvdrentals.address WHERE city <> 'London')
LIMIT 5;

Can see in previous query when we use subquery we get the same result in query below

Spinner
DataFrameas
df5
variable
SELECT * FROM dvdrentals.address
WHERE city NOT LIKE 'London'
LIMIT 5;

ANY() and SOME() opretors has same behavior and give same result.

Spinner
DataFrameas
df6
variable
-- Use SOME poretor
SELECT *
FROM dvdrentals.address
WHERE phone = SOME(ARRAY['14033335568', '28303384290']);
Spinner
DataFrameas
df7
variable
-- When we use WHERE clause with IN give me the same result
SELECT * FROM dvdrentals.address
WHERE phone IN ('14033335568', '28303384290');