DocumentationArchitectureBlogAboutBook a demo
Hotdata
Reference›Vector functions

Vector functions

Vector functions search, compare and combine embedding columns. A vector is an array of numbers, such as a FLOAT[] column of embeddings. The search table functions and vector_distance need a vector index on the column; the distance, arithmetic and aggregate functions work on any vector column. See the SQL reference for how a query uses the index, WHERE filtering, and how to choose between these functions.

Search

vector_search

Table function for semantic search. Embeds the search text with the column's embedding provider and returns the k nearest rows, plus a _distance column (Float32). Lower distance means more similar. Always uses the column's HNSW index. The indexed embedding column cannot be selected from the result; select the source text column instead.

vector_search('catalog.schema.table', 'column', 'search text', k)

Arguments

  • table: Fully-qualified table name, as a string literal.
  • column: Text column with a vector index and an embedding provider.
  • query: Search text to embed.
  • k: Number of nearest neighbors to return.
SELECT id, title, _distance
FROM vector_search('mydb.public.articles', 'description', 'machine learning', 10)
ORDER BY _distance ASC;

vector_search_vector

Table function for nearest-neighbor search with a query vector you supply. Returns the k nearest rows, plus a _distance column (Float32). Always uses the column's HNSW index. Needs no embedding provider.

vector_search_vector('catalog.schema.table', 'column', ARRAY[...], k)

Arguments

  • table: Fully-qualified table name, as a string literal.
  • column: Vector column with an HNSW index.
  • query: Query vector, as an ARRAY[...] literal.
  • k: Number of nearest neighbors to return.
SELECT id, title, _distance
FROM vector_search_vector('mydb.public.documents', 'embedding', ARRAY[0.1, -0.2, 0.5], 10)
ORDER BY _distance ASC;

vector_distance

Returns the distance between a text column and a search string. Embeds the search text with the column's embedding provider and uses the distance metric of the column's vector index. Uses the index only when the query meets the rewrite conditions; use vector_search for a search that always uses the index.

vector_distance(column, 'search text')

Arguments

  • column: Text column with a vector index and an embedding provider.
  • search text: Text to embed and compare against.
SELECT id, title, vector_distance(description, 'machine learning frameworks') AS dist
FROM mydb.public.articles
ORDER BY dist ASC
LIMIT 10;

Distance

Distance functions compare two vectors of equal length and return a Float32. Lower is closer, so sort with ORDER BY ... ASC. They scan every row unless the query meets the rewrite conditions for a vector index, and the function matches the index's metric.

cosine_distance

Returns the cosine distance, 1 - cosine similarity. Use for normalized embeddings, where only the angle between vectors matters.

cosine_distance(vector1, vector2)

SELECT id, title, cosine_distance(embedding, ARRAY[0.1, -0.2, 0.5]) AS dist
FROM mydb.public.documents
ORDER BY dist ASC
LIMIT 10;

l2_distance

Returns the squared Euclidean distance, the metric an l2 vector index uses. Ranking is the same as for the true distance, but a threshold is not: l2_distance(a, b) < 0.5 is a radius of 0.707. For the true Euclidean distance, use array_distance.

l2_distance(vector1, vector2)

SELECT id, l2_distance(embedding, ARRAY[0.1, -0.2, 0.5]) AS dist
FROM mydb.public.documents
ORDER BY dist ASC
LIMIT 10;

negative_dot_product

Returns the negated dot product, so that lower is closer like the other distance functions. Use for maximum inner product search. For the plain dot product, use inner_product.

negative_dot_product(vector1, vector2)

SELECT id, negative_dot_product(embedding, ARRAY[0.1, -0.2, 0.5]) AS score
FROM mydb.public.documents
ORDER BY score ASC
LIMIT 10;

Arithmetic

Arithmetic functions work on one row at a time, element by element. They accept any numeric array and return a DOUBLE array.

  • A NULL vector gives NULL. A NULL element gives NULL at that position only.
  • vec_add and vec_sub give an error for two vectors of different lengths.
  • The result column is named after the underlying array function, such as array_add(...). Use AS to name it.

vec_add

Adds two vectors element by element. Alias of array_add.

vec_add(vector1, vector2)

SELECT vec_add(ARRAY[1, 2, 3], ARRAY[10, 20, 30]) AS sum;
-- [11.0, 22.0, 33.0]

vec_sub

Subtracts the second vector from the first, element by element. Alias of array_subtract.

vec_sub(vector1, vector2)

SELECT id, vec_sub(embedding, ARRAY[0.1, -0.2, 0.5]) AS offset
FROM mydb.public.documents;

vec_scale

Multiplies each element of a vector by a number. Alias of array_scale.

vec_scale(vector, factor)

SELECT vec_scale(ARRAY[1, 2, 3], 0.5) AS half;
-- [0.5, 1.0, 1.5]

Aggregates

Vector aggregates combine the vectors of many rows into one vector, element by element. Use them with GROUP BY to get one centroid per group. The result is a DOUBLE array.

  • A NULL row is skipped and does not count toward an average. A group with no non-NULL rows gives NULL.
  • A NULL element inside a vector is an error that names its position.
  • Vectors of different lengths are an error that names both lengths.

vec_sum

Adds the vectors of all rows element by element.

vec_sum(vector)

SELECT vec_sum(embedding) AS total
FROM mydb.public.documents;

vec_avg

Returns the element-by-element average of the vectors of all non-NULL rows: the centroid.

vec_avg(vector)

SELECT cluster_id, vec_avg(embedding) AS centroid
FROM mydb.public.documents
GROUP BY cluster_id;

Related array functions

These array functions also work on vectors: