Operators

An operator is a symbol that combines or compares values — = for equality, + for addition, and so on. Because PostgreSQL's type system is extensible, its operator set is unusually rich compared to many databases: beyond the standard comparison and logical operators, it defines dedicated operators for pattern matching, arrays, JSONB, and range types, several of which have no real equivalent in more conservative SQL engines. This page is a map of the major categories, each linking conceptually to a deeper page where the type-specific ones are covered in full.

Comparison operators

Operator	Meaning
=	Equal to
<> or !=	Not equal to
<	Less than
	Greater than
<=	Less than or equal to
=	Greater than or equal to
BETWEEN a AND b	Value falls within an inclusive range
IS NULL / IS NOT NULL	Tests for NULL — = NULL never matches anything

Comparison operators in WHERE

SQL

SELECT sku, unit_price
FROM products
WHERE unit_price BETWEEN 20 AND 100
  AND stock_qty <> 0;

Logical operators

Operator	Meaning
AND	True only if both sides are true
OR	True if either side is true
NOT	Inverts a boolean condition

Pattern-matching operators

PostgreSQL supports both the standard SQL LIKE family and full POSIX regular expressions as first-class operators. The pattern-matching page covers wildcards, escaping, and regex syntax in depth.

Operator	Meaning
LIKE	Case-sensitive pattern match using % and _ wildcards
ILIKE	Case-insensitive version of LIKE (a PostgreSQL extension)
~	Matches a POSIX regular expression, case-sensitive
~*	Matches a POSIX regular expression, case-insensitive
!~ / !~*	Negated regular expression match

Pattern matching

SQL

SELECT name FROM products WHERE name ILIKE '%keyboard%';
SELECT name FROM products WHERE name ~ '^USB-';

Array operators

These operate on PostgreSQL's native array type, detailed on the array-functions page.

Operator	Meaning
@>	Left array contains right array
<@	Left array is contained by right array
&&	Arrays overlap — share at least one element
\|\|	Concatenates two arrays, or appends an element

Array containment

SQL

SELECT * FROM products WHERE tags @> ARRAY['electronics'];

JSONB operators

JSONB has its own dedicated operator set for reaching into semi-structured data, covered fully on the jsonb-operations page.

Operator	Meaning
->	Get a JSON object field or array element, as JSONB
->>	Get a JSON object field or array element, as text
#>	Get a JSON value at a specified path, as JSONB
@>	Left JSONB contains right JSONB
?	Does a top-level key or array element exist?

Reaching into a JSONB column

SQL

SELECT metadata ->> 'brand' AS brand
FROM products
WHERE metadata @> '{"category": "electronics"}';

Range operators

Range types (like int4range or tstzrange) reuse the @> / <@ / && operators with range-specific meaning, plus a few of their own. See the range-types page for details.

Operator	Meaning
@>	Range contains an element or another range
&&	Ranges overlap
<< / >>	Strictly left of / strictly right of
-\|-	Ranges are adjacent, with no gap between them

The same symbol can mean different things for different types

@> means "contains" for arrays, JSONB, and ranges alike, but the details of what "contains" means differ by type — array containment checks elements, JSONB containment checks structure, and range containment checks whether a value or sub-range falls inside the range. PostgreSQL's extensible type system is what allows the same operator symbol to be overloaded sensibly across such different kinds of data.

Comparison and logical operators (=, <>, AND, OR, NOT) work the same as in any SQL database.
LIKE / ILIKE / ~ / ~* cover wildcard and regular-expression pattern matching — see the pattern-matching page.
Array operators like @>, &&, and || are covered on the array-functions page.
JSONB operators like ->, ->>, and @> are covered on the jsonb-operations page.
Range operators reuse @> and && with range-specific meaning — see the range-types page.