本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/data-types。中文翻译正在分批进行中。

SQL data types

How Lance columns appear to SQL, including the types SQL has no name for.

Applies to: Embedded ❌, Enterprise ✅

There is no SQL type layer over Lance storage. The table provider hands DataFusion the dataset's Arrow schema directly, so a column is the same object to the query engine as it is to the format.

The consequence is that SQL type names here are spellings of Arrow types, not a system that maps onto them. Data types documents the storage side; this page covers what changes when you reach a column from SQL.

The Arrow types a column can be

Category	Types
Null	null
Boolean	bool
Integer	int8, int16, int32, int64 and their uint counterparts
Floating point	float16, float32, float64
String	utf8, large_utf8
Binary	binary, large_binary, fixed_size_binary(n)
Date and time	date32, date64, time32, time64, timestamp (with or without timezone), duration
Nested	struct, list, large_list, fixed_size_list, map
Dictionary	dictionary(key, value)

Two limits are worth stating because they surface as schema errors rather than as documentation:

• A map with keys_sorted = true is rejected. Sorted-key maps do not survive the round trip through the Python bindings, so they are refused rather than silently unsorted.
• Not every type is legal as a fixed_size_list child. The vector case is, which is what matters in practice.

SQL names for Arrow types

SQL	Arrow
BOOLEAN	Boolean
TINYINT SMALLINT INT BIGINT	Int8 Int16 Int32 Int64
UTINYINT … UBIGINT	the unsigned counterparts
REAL / FLOAT	Float32
DOUBLE	Float64
VARCHAR / TEXT / STRING	Utf8
BYTEA / BINARY	Binary
DATE	Date32
TIME	Time64
TIMESTAMP	Timestamp, with or without a timezone
INTERVAL	Interval
STRUCT / LIST / MAP	the nested equivalents

DataFusion's type reference describes the full mapping.

Types SQL has no name for

Some column kinds have no SQL type name.

Vectors

A dense vector column has a fixed dimension, commonly represented as FixedSizeList<Float32, dim>. Other supported vector element types are possible; inspect the column with arrow_typeof rather than assuming Float32. It is selectable and testable for nullness:

SELECT id FROM documents WHERE vector IS NOT NULL

注意

A list representation does not imply that ordinary arithmetic operators perform vector arithmetic. Use supported distance functions for similarity calculations. SQL vector search depends on the Enterprise server version and query plan; see SQL.

bfloat16

Sixteen-bit floats in the bfloat16 layout, stored as fixed_size_binary(2). Useful as a vector element type when halving the memory matters more than the precision does.

Pseudo-columns

Availability depends on the SQL table provider. The ordinary table provider exposes the stored dataset schema; naming a Lance scan metadata field does not make it available as a SQL column. The fts() provider adds _score to its schema, so _score is included in SELECT * FROM fts(...).

Column	Type
_rowid	UInt64	Lance scan row identifier; stability requires stable row ids
_rowaddr	UInt64	physical row address
_rowoffset	UInt64	offset within the fragment
_score	Float32	relevance, from fts()

_rowid, _rowaddr, and _rowoffset describe Lance scan metadata, not columns automatically registered on an ordinary SQL table. Select _score from an fts() relation explicitly when you want a projection containing it.

Casting

SELECT CAST(identifier AS VARCHAR) FROM documents
SELECT TRY_CAST(maybe_number AS BIGINT) FROM raw

TRY_CAST yields null rather than failing the query on a value that will not convert. See Operators.