SQL operators and expressions

Arithmetic

Integer division truncates toward zero. Cast a numerator or denominator to DOUBLE, or use a suitable decimal expression, when calculating fractional rates. Guard a potentially zero denominator with NULLIF:

SELECT
  100.0 * COUNT(*) FILTER (WHERE outcome = 'error')
    / NULLIF(COUNT(*), 0) AS error_rate_pct
FROM product_events;

Timestamp and interval arithmetic is type-aware; see data types. Do not subtract a raw numeric epoch from a typed timestamp.

Comparison and null-safe comparison

Ordinary comparison with a null produces unknown. NULL = NULL is not true. Null-safe comparison always produces a boolean: NULL IS NOT DISTINCT FROM NULL is true, while 1 IS DISTINCT FROM NULL is true.

Boolean logic

AND, OR, and NOT combine predicates using three-valued logic. WHERE keeps only true results.

Parenthesize mixed conditions to make intent clear:

WHERE timestamp_utc >= now() - INTERVAL '1 day'
  AND (outcome = 'error' OR duration_ms > 1000)

Do not rely on a particular order of predicate evaluation to prevent an invalid cast or division. Use TRY_CAST, NULLIF, or an appropriate conditional expression.

Range and membership

value BETWEEN low AND high includes both boundaries. NOT BETWEEN negates it. For adjacent time windows, use timestamp_utc >= start AND timestamp_utc < end instead.

IN compares against a list or a single-column subquery:

WHERE outcome IN ('error', 'timeout')

NOT IN is not null-safe. For example, 2 NOT IN (1, NULL) is unknown. Exclude nulls from the membership input or use NOT EXISTS when checking that no matching row exists. EXISTS and NOT EXISTS test whether a subquery returns rows.

Quantified comparisons

ANY (also spelled SOME) tests whether a comparison is true for at least one value; ALL tests every value. The input can be an array or a single-column subquery.

SELECT 2 = ANY ([1, 2, 3]) AS has_two;

SELECT value
FROM (VALUES (4)) AS candidates(value)
WHERE value > ALL (
  SELECT threshold FROM (VALUES (1), (2), (3)) AS limits(threshold)
);

The first query returns true; the second returns the row 4. Use quantified subqueries as filtering predicates: placing these subquery comparisons directly in the select list can fail physical planning. Supported comparisons are =, !=/<>, <, <=, >, and >=. For subqueries, an empty input makes ANY false and ALL true; null comparisons can make a result unknown when no true ANY comparison or false ALL comparison decides it.

Array ANY uses array membership or minimum/maximum helpers in this fork, whose treatment of null elements can differ from the subquery form. Use a single-column subquery when SQL three-valued null semantics matter, or filter null array elements explicitly when absence should not participate.

Text and pattern matching

For LIKE, % matches zero or more characters and _ matches one character. Backslash escapes those characters when matching them literally. The engine supports only backslash as an explicit ESCAPE character; another character such as ESCAPE '!' is rejected.

SELECT 'api_request' LIKE 'api\_%' ESCAPE '\' AS matches_prefix;

Regular expressions use their own syntax rather than SQL wildcards. ~ '^api_' anchors a match to the start of the string. The spellings ~~, ~~*, !~~, and !~~* are aliases for LIKE, ILIKE, NOT LIKE, and NOT ILIKE.

SIMILAR TO and NOT SIMILAR TO are also available. In this fork they use regular-expression matching, like ~ and !~; do not assume SQL-standard whole-string matching or LIKE wildcard syntax. Use explicit anchors for a whole-string match. SIMILAR TO does not support an ESCAPE clause.

SELECT 'api_12' SIMILAR TO '^api_[0-9]+$' AS matches;

Concatenation with || propagates null; concat has different null handling. See string functions when choosing an expression for optional fields.

Conditional expressions

Searched CASE selects the first true condition. Conditions that are false or unknown do not match. Omitting ELSE produces null when no branch matches.

SELECT
  CASE
    WHEN duration_ms IS NULL THEN 'unreported'
    WHEN duration_ms >= 1000 THEN 'slow'
    ELSE 'normal'
  END AS latency_class
FROM product_events;

Simple CASE compares one expression with alternatives:

CASE outcome WHEN 'error' THEN 1 WHEN 'timeout' THEN 1 ELSE 0 END

Result branches must have compatible types. COALESCE(a, b, ...) returns the first non-null argument. NULLIF(a, b) returns null when the inputs are equal, otherwise a. See scalar functions for further conditional functions.

Bitwise and array operators

SELECT [1, 2, 3] @> [1, 3] AS contains_values;

Array containment is not PostgreSQL JSON containment. Use nested data functions for array and struct operations.

Literals and comments

A double-quoted value such as "event" is an identifier, not a string. Cast null explicitly when a stable type is needed, for example CAST(NULL AS BIGINT).

Use -- for a comment to the end of the line and /* ... */ for a block comment. Parentheses control expression grouping; use them for mixed arithmetic, boolean predicates, and combined set operations rather than depending on remembered precedence from another SQL dialect.