PostgreSQL ALL Operator

Quiz

The ALL operator in PostgreSQL compares a value against all elements of an array. This operator is used to checks the strict condition. For example, consider a hiring process where a candidates scores must be above a certain level. Then use the ALL operator to filter the strong candidates.

Syntax

Following is the syntax of ALL operators in PostgreSQL −

expression operator ALL (array | subquery)

Here,

"expression" is the value to compare, operator is a comparison sign (=, <, >, <=, >=, <>), and array contains multiple values.

Example of ALL Operator in PostgreSQL

Here, we create one table name of students which stores the records of rows and columns. Our target is only to filter strong candidates.

Step 1: Start with CREATE TABLE and the put the values using INSERT INTO.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    experience INT,
    skills TEXT[],
    scores INT[]
);

INSERT INTO students (name, age, experience, skills, scores) VALUES
('Radha', 28, 5, ARRAY['Python', 'SQL'], ARRAY[85, 90, 95]),
('Sarika', 32, 7, ARRAY['Java', 'PostgreSQL'], ARRAY[88, 92, 99]),
('Revathi', 24, 2, ARRAY['Go', 'SQL'], ARRAY[60, 75, 80]),
('Raman', 30, 6, ARRAY['C++', 'Python'], ARRAY[70, 85, 88]),
('Akshita', 27, 4, ARRAY['Swift', 'SQL'], ARRAY[55, 65, 70]),
('Farhan', 35, 10, ARRAY['Ruby', 'JavaScript'], ARRAY[95, 98, 100]);

Step 2: Use the ALL Operator to filter the result.

Query I: Find students whose all scores are greater than 80.

SELECT name, scores FROM candidates WHERE 80 < ALL (scores);

The first query produces the following result −

name	scores
Radha	{85,90,95}
Sarika	{88,92,99}
Farhan	{95,98,100}

Query II: Find students who have all scores greater than or equal to 90.

SELECT name FROM candidates WHERE 90 <= ALL (scores);

The second query produces the following result −

name
Farhan

Print Page

Save or share this article