PostgreSQL Operators

Operators in the WHERE clause

We can operate with different operators in the WHERE clause:

Equal To

The = operator is used when you want to return all records where a column is equal to a specified value:

Example

SELECT * FROM cars
WHERE brand = 'Volvo';

Less Than

The < operator is used when you want to return all records where a column is less than a specified value.

Example

SELECT * FROM cars
WHERE year < 1975;

Greater Than

The > operator is used when you want to return all records where a columns is greater than a specified value.

Example

SELECT * FROM cars
WHERE year > 1975;

Less Than or Equal To

The <= operator is used when you want to return all records where a column is less than, or equal to, a specified value.

Example

SELECT * FROM cars
WHERE year <= 1975;

Greater Than or Equal to

The >= operator is used when you want to return all records where a columns is greater than, or equal to, a specified value.

Example

SELECT * FROM cars
WHERE year >= 1975;

Not Equal To

The <> operator is used when you want to return all records where a column is NOT equal to a specified value:

Example

SELECT * FROM cars
WHERE brand <> 'Volvo';

You will get the same result with the != operator:

Example

SELECT * FROM cars
WHERE brand != 'Volvo';

LIKE

The LIKE operator is used when you want to return all records where a column is equal to a specified pattern.

The pattern can be an absolute value like 'Volvo', or with a wildcard that has a special meaning.

There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign %, represents zero, one, or multiple characters.
• The underscore sign _, represents one single character.

Example

SELECT * FROM cars
WHERE model LIKE 'M%';

Note: The LIKE operator is case sensitive.

ILIKE

Same as the LIKE operator, but ILIKE is case insensitive.

Example

SELECT * FROM cars
WHERE model ILIKE 'm%';

AND

The logical AND operator is used when you want to check more that one condition:

Example

SELECT * FROM cars
WHERE brand = 'Volvo' AND year = 1968;

OR

The logical OR operator is used when you can accept that only one of many conditions is true:

Example

SELECT * FROM cars
WHERE brand = 'Volvo' OR year = 1975;

IN

The IN operator is used when a column's value matches any of the values in a list:

Example

SELECT * FROM cars
WHERE brand IN ('Volvo', 'Mercedes', 'Ford');

BETWEEN

The BETWEEN operator is used to check if a column's value is between a specified range of values:

Example

SELECT * FROM cars
WHERE year BETWEEN 1970 AND 1980;

Note: The BETWEEN operator includes the from and to values, meaning that in the above example, the result would include cars made in 1970 and 1980 as well.

IS NULL

The IS NULL operator is used to check if a column's value is NULL:

Example

SELECT * FROM cars
WHERE model IS NULL;

NOT

The NOT operator can be used together with LIKE, ILIKE, IN, BETWEEN, and NULL operators to reverse the truth of the operator.

Example: NOT LIKE

SELECT * FROM cars
WHERE brand
NOT LIKE 'B%';

Example: NOT ILIKE

SELECT * FROM cars
WHERE brand
NOT ILIKE 'b%';

Example: NOT IN

SELECT * FROM cars
WHERE brand NOT IN ('Volvo', 'Mercedes', 'Ford');

Example: NOT BETWEEN

SELECT * FROM cars
WHERE year NOT BETWEEN 1970 AND 1980;

Note: The NOT BETWEEN operator excludes the from and to values, meaning that in the above example, the result would not include cars made in 1970 and 1980.

Example: IS NOT NULL

SELECT * FROM cars
WHERE model IS NOT NULL;

Note: The cars table has no columns with NULL values, so the example above will return all 4 rows.