SQL Vector Type
Columns of type VECTOR store a fixed number of elements of the same type as a single vector value.
A vector can contain up to 65,535 elements.
| Name | Description | Syntax |
|---|---|---|
VECTOR |
A vector type for storing |
|
Columns of type VECTOR with different dimensions (number of elements) cannot be compared using comparison operators, cast to each other, or used as operands in VECTOR functions.
A vector value can be created from a string representation in the format '[<val_1>,<val_2>,…,<val_n>]'.
For example, the string '[-1,2,3.4]' can be converted to VECTOR(3,DOUBLE).
This conversion will be executed implicitly during INSERT INTO <table-name> and when an operation can deduce the required dimension from another operand.
Otherwise, an explicit cast of at least one operand is required.
For example, VECTOR_L1_DISTANCE(cast('[0,0,0]' as VECTOR(3,DOUBLE)), '[-1,2,3]').
Examples
CREATE TABLE VECTOR_TABLE(ID INT, EMBEDDINGS VECTOR(5,DOUBLE));INSERT INTO VECTOR_TABLE
VALUES (1, '[1,2,3,-4,-5]'), (2, '[-1,2,1,-2,3]');SELECT EMBEDDINGS FROM VECTOR_TABLE WHERE ID=1; EMBEDDINGS
-----------
[1,2,3,-4,-5]The following query returns all rows where EMBEDDINGS is greater than or equal to [0,0,0,0].
SELECT * FROM VECTOR_TABLE WHERE EMBEDDINGS >= '[0,0,0,0,0]'; ID EMBEDDINGS
--- -------------
1 [1,2,3,-4,-5]The query returns the row with EMBEDDINGS=[1,2,3,-4,-5] because vector values are compared with [0,0,0,0,0] lexicographically (from left to right), and the result is determined by the first difference.
During the comparison, [1,2,3,-4,-5] is compared with [0,0,0,0,0]. Since the first element 1>0, it is determined that [1,2,3,-4,-5] is greater.
Next, [-1,2,1,-2,3] is compared with [0,0,0,0,0].
Since the first element -1<0 it is determined that [-1,2,1,-2,3] is smaller.
The comparison stops immediately after the first different element is found and the remaining elements are not compared.
Subsequent elements are compared only if all the preceding elements are equal.