Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP HOW TO W3.CSS C C++ C# BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS R TYPESCRIPT ANGULAR GIT POSTGRESQL MONGODB ASP AI GO KOTLIN SASS VUE DSA GEN AI SCIPY AWS CYBERSECURITY DATA SCIENCE
     ❯   

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
LIKECheck if a value matches a pattern (case sensitive)
ILIKECheck if a value matches a pattern (case insensitive)
ANDLogical AND
ORLogical OR
INCheck if a value is between a range of values
BETWEENCheck if a value is between a range of values
IS NULLCheck if a value is NULL
NOTMakes 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 year < 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 year > 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 year <= 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 year >= 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.


PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select all records where the value of the field year is less than 1970:

SELECT * FROM cars
WHERE ;
        

Start the Exercise


×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Copyright 1999-2024 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.