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.

Text and pattern matching

For LIKE, % matches zero or more characters and _ matches one character. Use ESCAPE when matching those characters literally:

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.

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.