Other types

Table of Content

• ROW

• FLOAT_VECTOR

---

ROW

A row type is a composite data type made up of multiple fields, each with its own type—similar to a tuple in programming languages like Python.

Currently, CrateDB does not provide a row type literal for directly creating such values. However, the type is used implicitly as the result of table functions in the SELECT list when those functions return more than one column.

Example:

sqlCopierModifier-- The unnest() function returns two columns per row,
-- which are combined into a ROW type.
cr> SELECT unnest([1, 2], ['Arthur', 'Trillian']);
+----------------------------------------+
| unnest([1, 2], ['Arthur', 'Trillian']) |
+----------------------------------------+
| [1, "Arthur"]                          |
| [2, "Trillian"]                        |
+----------------------------------------+
SELECT 2 rows in set (... sec)

---

FLOAT_VECTOR

A FLOAT_VECTOR is a data type for storing dense, fixed-length vectors of FLOAT values.

It is primarily used for vector similarity search, supporting the KNN_MATCH function for k-nearest neighbor queries. This allows you to find the most similar vectors in a dataset to a given query vector.

Key Characteristics

• Fixed length: The dimension (number of elements) must be specified when creating the column.

• Specialized use: Cannot be used as the element type of a regular array.

• Definition syntax is similar to float arrays, but with a dimension declared.

---

Example Usage

-- Create a table with a 2-dimensional FLOAT_VECTOR column
cr> CREATE TABLE my_vectors (
...     xs FLOAT_VECTOR(2)
... );
CREATE OK, 1 row affected (... sec)

-- Insert a matching-dimension vector
cr> INSERT INTO my_vectors (xs) VALUES ([3.14, 27.34]);
INSERT OK, 1 row affected (... sec)

-- Attempt to insert a vector with the wrong dimension
cr> INSERT INTO my_vectors (xs) VALUES ([3.14, 27.34, 38.4]);
SQLParseException[The number of vector dimensions does not match the field type]

-- Query stored vectors
cr> SELECT * FROM my_vectors;
+---------------+
| xs            |
+---------------+
| [3.14, 27.34] |
+---------------+
SELECT 1 row in set (... sec)