Distance operators and functions

Every supported vector distance operator and function — meaning, ordering, and verified examples.

Version
Latest
v0.1.0 · latest 2 min read
On this page
  1. Operators
  2. Scalar functions
  3. Rules

All examples below use this setup (from Quickstart):

sqlsource
CREATE TABLE docs (
  id        BIGINT PRIMARY KEY,
  label     TEXT NOT NULL,
  embedding VECTOR(FLOAT32, 3)
);
INSERT INTO docs VALUES
  (1, 'alpha', [1.0, 0.0, 0.0]),
  (2, 'beta',  [0.0, 1.0, 0.0]),
  (3, 'gamma', [0.9, 0.1, 0.0]);

Operators#

Operator Name Meaning Lower means… Use with
<-> L2 (Euclidean) Straight-line distance Closer Numeric vectors
<=> Cosine distance 1 − cosine similarity; angle only, ignores magnitude More similar direction Numeric vectors
<#> Negative inner product −(a·b); negated so ascending order = most similar first More similar (ascending) Numeric vectors
<~> Hamming distance Count of differing bits More alike BINARY vectors
<%> Jaccard distance 1 − |intersection| / |union| over set bits More overlap BINARY vectors

Every operator is used the same way — in SELECT for the score, in ORDER BY for ranking, with LIMIT for top-k:

sqlsource
-- L2: alpha 0, gamma ~0.141, beta ~1.414
SELECT id, embedding <-> [1.0, 0.0, 0.0] AS dist FROM docs
ORDER BY embedding <-> [1.0, 0.0, 0.0] LIMIT 3;
 
-- Cosine: alpha 0, gamma ~0.006, beta 1
SELECT id, embedding <=> [1.0, 0.0, 0.0] AS dist FROM docs
ORDER BY embedding <=> [1.0, 0.0, 0.0] LIMIT 3;
 
-- Negative inner product, ascending: most similar first
SELECT id, embedding <#> [1.0, 0.0, 0.0] AS score FROM docs
ORDER BY embedding <#> [1.0, 0.0, 0.0] LIMIT 3;
-- order: 1 (score −1), 3 (score −0.9), 2 (score 0)

Binary examples:

sqlsource
SELECT [1, 0, 1] <~> [1, 1, 1];  -- 1 (one differing bit)
SELECT [1, 0, 1] <%> [1, 1, 1];  -- 0.333… (Jaccard distance)

Scalar functions#

Each operator has function equivalents, useful when the operator form is awkward (dynamic SQL builders, expression nesting):

Function Equivalent Notes
l2_distance(a, b) a <-> b
cosine_distance(a, b) a <=> b Zero-norm input → NULL
neg_inner_product(a, b) a <#> b
dot_product(a, b) −(a <#> b) Plain dot product (higher = more similar)
cosine_similarity(a, b) 1 − (a <=> b) Higher = more similar
l2_similarity(a, b) 1 / (1 + (a <-> b)) Higher = more similar; identical vectors → 1
hamming_distance(a, b) a <~> b Binary vectors
jaccard_distance(a, b) a <%> b Binary vectors
sqlsource
SELECT l2_distance([1.0, 0.0], [1.0, 1.0]);        -- 1
SELECT cosine_distance([1.0, 0.0], [0.0, 1.0]);    -- 1
SELECT cosine_similarity([1.0, 0.0], [0.0, 1.0]);  -- 0
SELECT dot_product([1.0, 2.0], [3.0, 4.0]);        -- 11
Note

There is no inner_product function — use dot_product (plain) or neg_inner_product / <#> (negated, orders ascending). Similarity functions return higher-is-better scores; distance functions and operators return lower-is-better scores. Do not mix the two orderings in one query.

Rules#

  • Both sides must have the same dimensions; mismatches are hard errors.
  • NULL input → NULL result; zero-norm cosine → NULL.
  • Numeric operators on BINARY columns (and bit operators on numeric columns) are rejected — the error names the mismatch.
  • Operators work on bare literals as well as columns, so you can sanity-check math without a table.
Was this page helpful?