> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL data types

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

Enterprise only

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](https://docs.lancedb.com/enterprise/v1-13-0/sql/data-types.md) 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](https://datafusion.apache.org/user-guide/sql/data_types.html)
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`](https://docs.lancedb.com/enterprise/v1-13-0/sql/dql/scalar-functions#type) rather than assuming Float32.
It is selectable and testable for nullness:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id FROM documents WHERE vector IS NOT NULL
```

> [!WARNING]
>
> 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](https://docs.lancedb.com/enterprise/v1-13-0/basics/sql.md#no-vector-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()`](https://docs.lancedb.com/enterprise/v1-13-0/sql/dql/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

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](https://docs.lancedb.com/enterprise/v1-13-0/sql/dql/operators.md#casting).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.