> ## 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.

**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](https://docs.lancedb.com/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/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/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/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/sql/dql/operators.md#casting).


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