# Vector functions

Source: https://www.hotdata.dev/docs/sql-functions-vector
Site index: https://www.hotdata.dev/llms.txt

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](/docs/sql#vector-indexes) on the column; the distance, arithmetic and aggregate functions work on any vector column. See the [SQL reference](/docs/sql#vector-search) 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.

```sql
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.

```sql
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](/docs/sql#distance-functions); 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.

```sql
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](/docs/sql#distance-functions) 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)
```

```sql
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`](/docs/sql-functions-array#array-distance).

```
l2_distance(vector1, vector2)
```

```sql
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`](/docs/sql-functions-array#inner-product).

```
negative_dot_product(vector1, vector2)
```

```sql
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)
```

```sql
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)
```

```sql
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)
```

```sql
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)
```

```sql
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)
```

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

## Related array functions

These [array functions](/docs/sql-functions-array) also work on vectors:

- [`array_distance`](/docs/sql-functions-array#array-distance): true (not squared) Euclidean distance.
- [`inner_product`](/docs/sql-functions-array#inner-product): dot product.
- [`array_normalize`](/docs/sql-functions-array#array-normalize): scales a vector to length 1.