PostgreSQL Operators
Operators in the WHERE clause
We can operate with different operators in the WHERE clause:
= | Equal to |
|---|---|
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
<> | Not equal to |
!= | Not equal to |
LIKE | Check if a value matches a pattern (case sensitive) |
ILIKE | Check if a value matches a pattern (case insensitive) |
AND | Logical AND |
OR | Logical OR |
IN | Check if a value is between a range of values |
BETWEEN | Check if a value is between a range of values |
IS NULL | Check if a value is NULL |
NOT | Makes a negative result e.g.
NOT LIKE, NOT IN,
NOT BETWEEN |
Equal To
The = operator is used when you want to return all records where a
column is equal to a specified value:
Example
Return all records where the brand is 'Volvo':
SELECT * FROM cars
WHERE brand = 'Volvo';
Run Example »
Less Than
The < operator is used when you want to return all records where a
column is less than a specified value.
Example
Return all records where the year is less than 1975:
SELECT * FROM cars
WHERE model < 1975;
Run Example »
Greater Than
The > operator is used when you want to return all records where a
columns is greater than a specified value.
Example
Return all records where the year is greater than 1975:
SELECT * FROM cars
WHERE model > 1975;
Run Example »
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
Return all records where the year is less than or equal to 1975:
SELECT * FROM cars
WHERE model <= 1975;
Run Example »
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
Return all records where the year is greater than or equal 1975:
SELECT * FROM cars
WHERE model >= 1975;
Run Example »
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
Return all records where the brand is NOT 'Volvo':
SELECT * FROM cars
WHERE brand <> 'Volvo';
Run Example »
You will get the same result with the != operator:
Example
Return all records where the brand is NOT 'Volvo':
SELECT * FROM cars
WHERE brand != 'Volvo';
Run Example »
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
Return all records where the model STARTS with a capital 'M':
SELECT * FROM cars
WHERE model LIKE 'M%';
Run Example »
The LIKE operator is case sensitive.
ILIKE
Same as the LIKE operator, but
ILIKE is case insensitive.
Example
Return all records where the model start with a 'm':
SELECT * FROM cars
WHERE model ILIKE 'm%';
Run Example »
AND
The logical AND operator is used when you want to
check more that one condition:
Example
Return all records where the brand is 'Volvo' and the year is 1968:
SELECT * FROM cars
WHERE brand = 'Volvo' AND year = 1968;
Run Example »
OR
The logical OR operator is used when you can accept that only one of many
conditions is true:
Example
Return all records where the brand is 'Volvo' OR the year is 1975:
SELECT * FROM cars
WHERE brand = 'Volvo' OR year = 1975;
Run Example »
IN
The IN operator is used when a column's value matches any of the values in a list:
Example
Return all records where the brand is present in this list: ('Volvo', 'Mercedes', 'Ford'):
SELECT * FROM cars
WHERE brand IN ('Volvo', 'Mercedes', 'Ford');
Run Example »
BETWEEN
The BETWEEN operator is used to check if a column's value
is between a specified range of values:
Example
Return all records where the year is between 1970 and 1980:
SELECT * FROM cars
WHERE year BETWEEN 1970 AND 1980;
Run Example »
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
Return all records where the model is NULL:
SELECT * FROM cars
WHERE model IS NULL;
Run Example »
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
Return all records where the brand does NOT start with a capital 'B' (case sensitive):
SELECT * FROM cars
WHERE brand
NOT LIKE 'B%';
Run Example »
Example: NOT ILIKE
Return all records where the brand does NOT start with a 'b' (case insensitive):
SELECT * FROM cars
WHERE brand
NOT ILIKE 'b%';
Run Example »
Example: NOT IN
Return all records where the brand is NOT present in this list: ('Volvo', 'Mercedes', 'Ford'):
SELECT * FROM cars
WHERE brand NOT IN ('Volvo', 'Mercedes', 'Ford');
Run Example »
Example: NOT BETWEEN
Return all records where the year is NOT between 1970 and 1980:
SELECT * FROM cars
WHERE year NOT BETWEEN 1970 AND 1980;
Run Example »
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
Return all records where the model is NOT null:
SELECT * FROM cars
WHERE model IS NOT NULL;
Run Example »
The cars table has no columns with NULL values,
so the example above will return all 4 rows.
