Comparison operators

A comparison operator tests the relationship between two values and returns a corresponding value of true, false, or NULL.

Table of contents

Basic operators

For simple data types, the following basic operators can be used:

Operator Description
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
= Equal
<> Not equal
!= Not equal (same as <>)

When comparing strings, a lexicographical comparison is performed:

cr> select name from locations where name > 'Argabuthon' order by name;
| name                               |
| Arkintoofle Minor                  |
| Bartledan                          |
| Galactic Sector QQ7 Active J Gamma |
| North West Ripple                  |
| Outer Eastern Rim                  |
SELECT 5 rows in set (... sec)

When comparing dates, ISO date formats can be used:

cr> select date, position from locations where date <= '1979-10-12' and
... position < 3 order by position;
| date         | position |
| 308534400000 |        1 |
| 308534400000 |        2 |
SELECT 2 rows in set (... sec)


Comparison operators are commonly used to filter rows (e.g., in the WHERE and HAVING clauses of a SELECT statement). However, basic comparison operators can be used as value expressions in any context. For example:

cr> SELECT 1 < 10 as my_column;
| my_column    |
| true         |
SELECT 1 rows in set (... sec)

WHERE clause operators

Within a WHERE clause, the following operators can also be used:

Operator Description
~ Matches regular expression (case sensitive)
!~ Matches regular expression (case insensitive)
!~ Does not match regular expression (case sensitive)
!~* Does not match regular expression (case insensitive)
LIKE (ILIKE) Matches a part of the given value
NOT Negates a condition
IS NULL Matches a null value
IS NOT NULL Matches a non-null value
ip << range True if IP is within the given IP range (using CIDR notation)
x BETWEEN y AND z Shortcut for x >= y AND x <= z


How helpful was this page?