Operators

Comparison, arithmetic, string, bitwise, JSON, and array operators with NULL rules.

Version
Latest
v0.1.0 · latest 2 min read
On this page
  1. Comparison and logic
  2. Arithmetic and string
  3. Pattern
  4. Casts, vectors, ranges
  5. JSON (at multiplicative level)
  6. NULL rules

Comparison and logic#

= != <> < <= > >=, AND OR NOT (implemented as is-true checks — NULL AND TRUE → false here, differing from strict 3VL; see NULL semantics), quantified op ANY|ALL|SOME.

Arithmetic and string#

+ - * / %, ^ (power, right-assoc), unary -x, || concat, & | # << >> bitwise. Temporal overloads: date ± int → date, ts ± interval → ts, date − date → int, ts − ts → interval.

Pattern#

LIKE [ESCAPE], ILIKE (parses but behaves as case-sensitive LIKE — verified; use lower(x) LIKE lower(y)), SIMILAR TO, ~ ~* !~ !~* (→ regex_match* functions).

Casts, vectors, ranges#

  • expr::type casts (binds tightly; see Data types).
  • Vector distance operators <-> (L2), <=> (cosine), <#> (negative inner product), <~> (Hamming), <%> (Jaccard) — full semantics, ordering, and functions in Distance operators.
  • Range containment and overlap (<@, @>, && on ranges) — see Ranges.

JSON (at multiplicative level)#

Op Meaning
-> / ->> field/index as JSON / as text
#> / #>> path (text[]) as JSON / text
#- delete path
@> <@ ? ?| ?& @? @@ contains / contained / exists / path predicates
- on jsonb `jsonb - key
`
arr[i], jsonb['k'], (r).f 1-based subscript / row field

NULL rules#

Any comparison/arithmetic/BETWEEN/LIKE with NULL → NULL; WHERE keeps only true. IS NULL, IS DISTINCT FROM are NULL-aware. Most scalar functions return NULL on NULL input (concat skips NULLs, concat_ws NULL sep → NULL).

Was this page helpful?