Operators ​

Operators combine values in expressions. Use parentheses when arithmetic, comparisons and search conditions should be grouped explicitly.

Arithmetic and bits ​

Ordinary integer division truncates toward zero. Ordinary numeric division and remainder by zero return NULL; Decimal operations raise INVALID_ARGUMENT instead. Native exact numeric overflow raises an error.

SELECT 7 / 2, 7 % 2, 6 & 3, 'ab' || 'cd'
FORMAT csv;

3,1,2,"abcd"

Comparisons and missing values ​

Comparisons include =, ==, !=, <>, <, <=, > and >=. Ordinary comparisons with NULL generally return NULL.

IS and IS NOT provide NULL-safe comparisons. IS NOT DISTINCT FROM is the NULL-safe equality form; IS DISTINCT FROM is its inverse. Postfix ISNULL, NOTNULL and NOT NULL test nullness.

SELECT NULL = NULL,
       NULL IS NULL,
       NULL IS NOT DISTINCT FROM NULL,
       1 IS DISTINCT FROM NULL
FORMAT csv;

\N,1,1,1

Logic and membership ​

AND, OR and NOT use SQL three-valued logic. BETWEEN includes both endpoints. IN tests membership in a list, subquery or table; EXISTS tests whether a subquery returns a row.

An empty right-hand side makes IN false and NOT IN true, including for a NULL left operand. Nonempty membership tests follow the documented NULL rules.

See logical expressions and IN.

Text and structured search ​

LIKE supports SQL wildcards and an optional ESCAPE character. GLOB uses shell-style matching. The full-text @@ operator matches a tsvector against a tsquery.

JSON -> returns JSON text and ->> returns an SQL scalar. Native inet/cidr values also support documented subnet operators.

SELECT 'hello' LIKE 'he%',
       json('{"a":2}') ->> 'a'
FORMAT csv;

1,2

See string search, JSON functions and network functions for their specific operand contracts.