SQL Reference¶
This document lists the SQL surface currently supported by the lance DuckDB extension, with short examples.
Loading¶
For local development builds, start the matching DuckDB binary with -unsigned
(e.g. ./build/release/duckdb -unsigned) and load the extension artifact directly:
Scan¶
Query a dataset by selecting from its URI directly:
Float16 columns are exposed as FLOAT, including values nested in arrays, lists,
and structs. To avoid silently changing their on-disk types, INSERT rejects
tables with coerced columns, and UPDATE and MERGE reject statements that
write one; DELETE, and UPDATE or MERGE of other columns, leave them
unchanged. For a scalar float16 column,
ALTER TABLE ... ALTER COLUMN ... TYPE FLOAT explicitly migrates it to float32
and makes it writable.
INSERT, UPDATE, MERGE, and COPY ... (mode 'append') write values as the
Arrow types the table stores, so tables written by other Lance writers (for
example with large_string, dictionary, date64, or millisecond timestamp
columns) accept DuckDB values. A value the stored type cannot hold exactly,
such as a fractional second in a seconds column, an INTERVAL with days in a
duration column, or more distinct strings in a batch than a dictionary's key
type can index, fails the statement instead of being truncated or stored as
NULL.
Row address and row lineage¶
A scan exposes Lance's row address and row lineage as virtual columns. They are not part of SELECT *
and are read only when named:
_rowid: the row id. With stable row ids it survives updates and compaction; without them it is the physical row address._rowaddr(UBIGINT): the physical row address,(fragment id << 32) | row offset. It changes when compaction or an update moves a row._row_created_at_versionand_row_last_updated_at_version(UBIGINT): the dataset version that inserted the row and the version that last wrote it. They need a dataset created withenable_stable_row_ids; on any other dataset reading them is an error, because Lance cannot tell when a row changed.
SELECT id, _row_last_updated_at_version
FROM '/data/events.lance'
WHERE _row_last_updated_at_version > 12;
Predicates on these columns are evaluated by DuckDB after the scan; they are not pushed to Lance. They
are available when scanning a dataset path, including the <name>.lance directory of a table in an
attached directory namespace, but not on a name bound through ATTACH (_rowid is also path-only;
rowid is the attached-table equivalent) or on tables read through a REST namespace query_table
endpoint.
Aggregate memory¶
Pushed aggregates account their tracked intermediate state in DuckDB's shared
memory_limit budget and use the connection's operator_memory_limit at
execution time, including prepared statements. Reservations appear under
EXTENSION in duckdb_memory().
Pushed aggregates fail with a memory error when admission fails; they do not spill to disk. Scan batches and Lance caches are outside this accounting, so these settings are not a limit on total process memory.
JSON columns¶
Lance JSON extension fields scan as JSON text with SQL type VARCHAR, including
JSON fields nested in structs or lists. SQL predicates and aggregates use that
text representation. Search predicates on JSON columns require
prefilter := false when they cannot be pushed to Lance; they then filter the
top-k results.
INSERT and MERGE INSERT do not support tables containing JSON columns.
UPDATE and MERGE UPDATE can change other columns, but cannot assign a
JSON-containing column. DELETE remains supported. JSON columns can be renamed
or dropped, but ALTER COLUMN ... TYPE rejects a JSON source. To populate a
derived text column, add the column and then use a separate UPDATE statement.
COPY creates datasets with its input Arrow types. To create or append native JSON, use DuckDB
JSON input values with arrow_lossless_conversion = true. Appending ordinary
VARCHAR or bytes to an existing JSON field fails Lance's schema check.
Overwrite mode can replace the dataset schema.
Blob columns and write inputs¶
SQL BLOB columns read and write real bytes. In addition, a write can carry
an explicit external-reference or managed-ingest description through the
LANCE_BLOB_INPUT value type:
For partial reads without materializing whole values, see Blob range reads.
Encoding defaults¶
- New top-level
BLOBfields in datasets written with an effective file version>= 2.2are stored as Lance Blob v2. Lance chooses inline, packed, or dedicated storage; the SQL surface does not expose layout knobs.ALTER TABLE ... ADD COLUMNapplies the same default: a new top-level BLOB column becomes Blob v2 at file version>= 2.2and stays plain binary below it. Non-BLOB binary-typed columns such asBITare never re-encoded. ALTER TABLE ... ADD COLUMN ... DEFAULTback-fills every existing row. BLOB defaults accept constant-foldable bytes andNULL; URI inputs are a DML value contract and are rejected as stored defaults, and non-foldable expressions such as column references or volatile functions fail with an explicit error. The storedduckdb_default_exprmetadata is persisted by a separate best-effort commit after the schema evolution; this does not create a new atomic DDL guarantee.- Datasets written with an older file version keep their existing binary encoding.
- Existing plain binary and legacy Blob columns keep their encoding. They still accept ordinary bytes, but a URI input is rejected instead of silently migrating the column.
- URI inputs are only supported for top-level
BLOBcolumns. NestedLANCE_BLOB_INPUTvalues (insideSTRUCT,LIST, orMAP) are rejected; ordinary nestedBLOBvalues keep their existing behavior.
Constructors¶
lance_blob_ref(uri)
lance_blob_ref(uri, offset, length)
lance_blob_ingest(uri)
lance_blob_ingest(uri, offset, length)
uriisVARCHARand must be absolute with a scheme, for examplefile:///path/object.binors3://bucket/key. Relative paths are rejected; they are never resolved against the process working directory.- Without
offset/length, the whole object is referenced. With a range,offsetis a zero-basedBIGINT >= 0andlengthis aBIGINT > 0. Both range arguments must be non-NULL and the range must not overflow. - A NULL URI produces a NULL blob. NULL is distinct from an empty BLOB; an empty object can be ingested or referenced as a whole object.
- Constructors only build and validate the value. They perform no HEAD, GET, or ingest I/O; I/O happens only when a row reaches the writer.
Reference vs. ingest¶
| Variant | Source I/O at write time | Ownership after commit |
|---|---|---|
lance_blob_ref |
None | The external owner keeps the object. Later reads follow the URI; deleting or changing the source affects those reads. |
lance_blob_ingest |
Reads the selected object or range | Lance manages the stored bytes. The source object can be deleted after a successful commit. |
Source storage is resolved per source URI through DuckDB secrets, separately from the target dataset. Creating a reference does not validate that the external object exists; reference reads and ingest writes report missing objects or invalid ranges as errors.
INSERT, COPY, and CTAS¶
Ordinary BLOB values and both URI constructors share one value contract.
Each top-level value keeps its own mode, including values selected by CASE.
ATTACH 'path/to/namespace' AS ns (TYPE LANCE);
INSERT INTO ns.main.media (id, payload)
VALUES (1, 'plain bytes'::BLOB),
(2, lance_blob_ref('s3://source/a.jpg')),
(3, lance_blob_ingest('s3://source/b.jpg', 1024, 4096)),
(4, NULL);
COPY (
SELECT id, lance_blob_ingest(uri) AS payload
FROM incoming_media
) TO 'path/to/media.lance' (FORMAT lance);
CREATE TABLE ns.main.media_copy AS
SELECT id,
CASE WHEN keep_reference THEN lance_blob_ref(uri)
ELSE lance_blob_ingest(uri) END AS payload
FROM incoming_media;
UPDATE and MERGE accept the same values:
UPDATE ns.main.media
SET payload = lance_blob_ingest('s3://source/replacement.jpg')
WHERE id = 1;
MERGE INTO ns.main.media AS target
USING incoming_media AS source ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET payload = lance_blob_ingest(source.uri)
WHEN NOT MATCHED THEN
INSERT (id, payload) VALUES (source.id, lance_blob_ref(source.uri));
Ingest is materialized with a per-batch budget derived from DuckDB's
memory_limit (memory_limit / 8, shared by all blob columns and all ingest
rows of one batch). An object or range larger than that budget fails with an
explicit error; use lance_blob_ref for large external objects or raise
memory_limit. The budget bounds one batch conversion, not the total query
or process RSS: buffered batches and downstream operators can hold additional
memory.
INSERT, UPDATE, DELETE, and MERGE support RETURNING. Returned BLOB
columns contain the row's bytes. Unreferenced external payloads are not read
solely to construct the returned row. Reading a returned reference uses the
connection's source access policy and scoped Lance secrets.
Blob range reads¶
lance_read_blob_ranges reads byte ranges inside top-level blob values without
materializing whole blobs in SQL:
SELECT request_id, version, row_address, data
FROM lance_read_blob_ranges(
'path/to/dataset.lance',
'payload',
7, -- dataset version, NULL selects the latest version
'row_address', -- or 'row_id'
(
SELECT request_id, row_value, offset, length
FROM requests
)
);
Positional arguments:
dataset(VARCHAR): Dataset root path or object store URI. Storage credentials resolve through the same secrets and session as scans.column(VARCHAR): A top-level blob column.version(BIGINT): Dataset version to pin.NULLmeans the latest version and is only allowed forrow_idrequests against a dataset whose manifest enables stable row ids. Without stable row ids_rowidis the physical row address, so both selectors require an explicit version there.selector(VARCHAR): One selector kind for the whole call.row_addressaddresses the physicalrow_addressvalue reported by a previous call at the same version.row_idaddresses the dataset row id exposed as_rowid: stable row ids when the dataset has them enabled, the physical row address otherwise.requests(TABLE): A relation with one row per request. It must providerequest_id(any type; echoed back),row_value,offset, andlength(integer types). Extra columns are ignored.
The function returns one row per request:
request_index(UBIGINT): A processing ordinal unique within one call, in no guaranteed relation order. The request relation has no intrinsic order and independent pipelines (for exampleUNION ALLbranches) may process requests in any order, so userequest_idto correlate results andORDER BYto choose display order. Repeated executions are not promised to assign the same ordinals.request_id: The caller'srequest_id, unchanged. Duplicate ids stay duplicated.row_address(UBIGINT): Physical row address of the selected blob at the pinned version.data(BLOB): The requested bytes.version(UBIGINT): The dataset version the handle was actually pinned to.
offset and length are relative to the logical blob value, and ranges of
blobs that reference an external source are translated by Lance. A NULL blob
returns NULL data; a zero-length range on a non-NULL blob returns an empty
BLOB. Out-of-bounds ranges, offset/length overflow, missing rows, missing
physical fragments and source I/O failures raise errors instead of returning
NULL or truncated bytes.
Lance serves the requested ranges directly and may combine nearby requests
into fewer physical reads; the extension never reads a whole blob and slices
it afterwards. Requests are processed in windows sized by DuckDB's current
memory_limit (window budget = memory_limit / 8), bounding each native read
call: a single range larger than the budget fails with an explicit error, so
raise memory_limit or split the range. The budget bounds a single call, not
the whole query - concurrent request pipelines and downstream SQL operators
can still hold more materialized bytes in total. The function does not accept
nested blob columns.
Search¶
Vector search: lance_vector_search¶
-- Search a vector column, returning distances in `_distance` (smaller is closer)
SELECT id, label, _distance
FROM lance_vector_search(
'path/to/dataset.lance',
'vec',
[0.1, 0.2, 0.3, 0.4]::FLOAT[4],
k = 5,
use_index = true,
nprobs = 4,
refine_factor = 2,
prefilter = true
)
ORDER BY _distance ASC;
Signature: lance_vector_search(uri, vector_column, query_vector, ...)
Positional arguments:
- uri (VARCHAR): Dataset root path, object store URI (e.g. s3://...), or an attached Lance table name.
- vector_column (VARCHAR): Vector column name.
- query_vector: Non-empty query vector. FLOAT[dim] / DOUBLE[dim] are preferred; FLOAT[] / DOUBLE[] are also accepted and cast to float32. For a direct local dataset with a binary vector column, UTINYINT[] / UTINYINT[dim] supplies a native binary query and uses Hamming distance. On floating-point columns, UINT8 follows the ordinary numeric cast to float32. Namespace-backed tables reject UINT8 queries.
Named parameters:
- k (BIGINT, default 10): Number of results to return. Must be > 0.
- use_index (BOOLEAN, default true): If true, allow ANN index usage when available.
- nprobs (BIGINT, optional): Number of IVF partitions to probe when using a vector index. Must be > 0. Only affects IVF-based vector indices.
- refine_factor (BIGINT, optional): Over-fetch factor for re-ranking using original vectors. Must be > 0. A value of 1 still enables re-ranking.
- ef (BIGINT, optional): Search-time candidate list size for HNSW sub-indexes (for example IVF_HNSW_SQ). Must be > 0 and at least k * refine_factor, otherwise Lance fails the query with ef must be greater than or equal to k. The Lance default is 1.5 * k * refine_factor, which can give poor recall on large partitions; raise it (for example ef = 200) to trade latency for recall. Ignored by index types without an HNSW graph.
- distance_type (VARCHAR, optional): Distance metric of _distance: l2 (or euclidean), cosine, dot, or hamming, case-insensitive. If unset, a search that uses a vector index ranks by the index's metric, while an exact search (use_index = false, or no index) uses Lance's default for the column type (l2 for floating-point vectors, hamming for binary vectors). Set it to the index's metric_type to compare ANN results with exact ones. Lance does not use an index built with a different metric; it runs an exact search instead.
- prefilter (BOOLEAN, default false): If true, filters are applied before top-k selection.
- explain_verbose (BOOLEAN, default false): Emit a more verbose Lance plan in EXPLAIN output.
- filter (VARCHAR, optional): Namespace query_table filter expression. Only supported when uri resolves to an attached Lance namespace table. Combined with pushed WHERE predicates using AND.
Output:
- Dataset columns plus _distance (smaller is closer).
- _rowid, a virtual column as in a table scan: the row id of each hit, equal to the row id a
scan of the same dataset version returns (_rowid on a dataset path, rowid on an attached
table; the physical row address unless the dataset enables stable row ids). Select or filter it
by name; SELECT * leaves it out, so alias it to keep it in written results, for example
SELECT *, _rowid AS hit_rowid. Inside a DELETE, a search of the attached target table reads
the version the DELETE scans, so
DELETE FROM ns.main.t WHERE rowid IN (SELECT _rowid FROM lance_fts('ns.main.t', ...)) deletes
the hits. This does not hold inside UPDATE or MERGE yet.
Filter semantics:
- If prefilter=false, filter pushdown is best-effort. If pushdown fails, the query is retried without pushed filters and DuckDB applies filters for correctness.
- If prefilter=true, prefilterable filters must be pushed down, otherwise the query fails with an error.
- WHERE predicates push down for both dataset paths and attached namespace tables. For namespace tables, pushed predicates are translated to a Lance Namespace query_table filter; a pushed predicate and an explicit filter combine with AND.
Full-text search: lance_fts¶
-- Search a text column, returning BM25-like scores in `_score` (larger is better)
SELECT id, text, _score
FROM lance_fts('path/to/dataset.lance', 'text', 'puppy', k = 10, prefilter = true)
ORDER BY _score DESC;
Signatures:
- lance_fts(uri, text_column, query, ...): matches any term of query against text_column.
- lance_fts(uri, query, ...): query is a structured query (see below).
Positional arguments:
- uri (VARCHAR): Dataset root path, object store URI (e.g. s3://...), or an attached Lance table name.
- text_column (VARCHAR): Text column name.
- query (VARCHAR): Query string, or a structured query in the two-argument form.
Named parameters:
- k (BIGINT, default 10): Number of results to return. Must be > 0.
- prefilter (BOOLEAN, default false): If true, filters are applied before top-k selection.
- filter (VARCHAR, optional): Namespace query_table filter expression. Only supported when uri resolves to an attached Lance namespace table. Combined with pushed WHERE predicates using AND.
Output:
- Dataset columns plus _score (larger is better).
- _rowid, a virtual column, as for lance_vector_search.
Filter semantics:
- If prefilter=false, filter pushdown is best-effort. If pushdown fails, the query is retried without pushed filters and DuckDB applies filters for correctness.
- If prefilter=true, prefilterable filters must be pushed down, otherwise the query fails with an error.
- WHERE predicates push down for both dataset paths and attached namespace tables. For namespace tables, pushed predicates are translated to a Lance Namespace query_table filter; a pushed predicate and an explicit filter combine with AND.
Structured queries:
-- Exact phrase; the INVERTED index must be built WITH (with_position = true)
SELECT id, text, _score
FROM lance_fts('path/to/dataset.lance', '{"phrase": {"column": "text", "terms": "lazy dog"}}');
-- Every term, allowing one edit per term
SELECT id, text, _score
FROM lance_fts('path/to/dataset.lance',
'{"match": {"column": "text", "terms": "lazy dgo", "operator": "And", "fuzziness": 1}}');
- A structured query is a Lance
FtsQueryin its JSON form, the format of LanceDB'sftstable function:match(terms,operator"And"or"Or",fuzziness,max_expansions,prefix_length,boost),phrase(terms,slop),multi_match(match_queries),boolean(should,must,must_not),boost(positive,negative,negative_boost) andcombined_fields. Defaults are Lance's; Lance currently treats an omittedfuzzinessas0(exact terms), so set a positivefuzzinessto match misspellings. - Fuzzy terms are expanded from the INVERTED index, so rows appended after the index was built match them only once the index covers those rows (
ALTER INDEX ... OPTIMIZE). - A query without
columnsearches every column with an INVERTED index. - Invalid JSON, an unknown column, or a phrase query on an index without positions fails the query.
- On attached namespace tables the query is sent as the
query_tablestructured_query. That API cannot expresscombined_fields,document_granularity, or amatchwithoutfuzziness, so these fail; setfuzziness(0for exact terms).
Hybrid search: lance_hybrid_search¶
-- Combine vector and text scores, returning `_hybrid_score` (larger is better)
SELECT id, _hybrid_score, _distance, _score
FROM lance_hybrid_search(
'path/to/dataset.lance',
'vec',
[0.1, 0.2, 0.3, 0.4]::FLOAT[4],
'text',
'puppy',
k = 10,
prefilter = false,
alpha = 0.5,
oversample_factor = 4
)
ORDER BY _hybrid_score DESC;
Signatures:
- lance_hybrid_search(uri, vector_column, query_vector, text_column, query, ...)
- lance_hybrid_search(uri, vector_column, query_vector, query, ...): query is a structured query, as for lance_fts.
Positional arguments:
- uri (VARCHAR): Dataset root path, object store URI (e.g. s3://...), or an attached Lance table name.
- vector_column (VARCHAR): Vector column name.
- query_vector: Non-empty query vector. FLOAT[dim] / DOUBLE[dim] are preferred; FLOAT[] / DOUBLE[] are also accepted and cast to float32. For a direct local dataset with a binary vector column, UTINYINT[] / UTINYINT[dim] supplies a native binary query and uses Hamming distance. On floating-point columns, UINT8 follows the ordinary numeric cast to float32. Namespace-backed tables reject UINT8 queries.
- text_column (VARCHAR): Text column name.
- query (VARCHAR): Query string, or a structured query in the four-argument form.
Named parameters:
- k (BIGINT, default 10): Number of results to return. Must be > 0.
- nprobs (BIGINT, optional): Number of IVF partitions to probe when using a vector index. Must be > 0. Only affects IVF-based vector indices.
- refine_factor (BIGINT, optional): Refine factor for the vector branch. Must be > 0.
- ef (BIGINT, optional): HNSW search-time candidate list size for the vector branch. Must be > 0 and at least k * oversample_factor (times refine_factor if set), otherwise Lance fails the query with ef must be greater than or equal to k.
- distance_type (VARCHAR, optional): Distance metric of the vector branch, as for lance_vector_search.
- prefilter (BOOLEAN, default false): If true, filters are applied before top-k selection.
- use_index (BOOLEAN, default true): If true, allow ANN index usage for the vector branch when available. If false, the vector branch runs exact KNN.
- alpha (FLOAT, default 0.5): Vector/text mixing weight. Larger values weigh vector similarity more heavily.
- oversample_factor (INTEGER, default 4): Oversample factor for candidate generation. If provided, must be > 0.
Output:
- Dataset columns plus _hybrid_score (larger is better), _distance, and _score.
- _rowid, a virtual column, as for lance_vector_search.
- Among the candidates, rows that tie on _hybrid_score at the k boundary are ranked by _rowid (smaller first). Ties in the vector or text score at the k * oversample_factor candidate cut are decided by Lance.
Filter semantics:
- If prefilter=false, filter pushdown is best-effort. If pushdown fails, the query is retried without pushed filters and DuckDB applies filters for correctness.
- If prefilter=true, prefilterable filters must be pushed down, otherwise the query fails with an error.
- WHERE predicates push down for both dataset paths and attached namespace tables.
Filter pushdown notes¶
These notes apply to all three search functions.
- Comparison predicates (
=,>, numericIN, ...) and string predicates (starts_with,LIKE,regexp_matches) push down into Lance. ILIKE,lower/upper, and regular expressions that use\d,\w,\sor\bare evaluated by DuckDB, because Lance applies Unicode rules to case mapping and to these classes, so its results would differ from DuckDB's. Withprefilter=truethey raise an error; apply them after the search withprefilter := false.- Containment predicates over list columns —
array_has_anyandarray_has_all— push down. Withprefilter=trueand aLABEL_LISTindex on the list column, Lance answers them from the index; without one, Lance evaluates them with a scan. The index is used forVARCHAR[]andBIGINT[]element types; narrower integer widths still push and prefilter, but scan. IS NULL/IS NOT NULLon list columns push down. No scalar index answers them.- Containment forms that cannot push — empty or non-constant needles, needles containing
NULL,array_has/array_contains, and the&&/@>/<@aliases — are evaluated by DuckDB after the search. - Datasets whose list columns have a child field named
l(written by earlier builds of this extension) push and prefilter correctly, but theLABEL_LISTindex is not used. A metadata-only rename fixes this without rewriting data:ALTER TABLE ns.main.t RENAME COLUMN "tags.l" TO item; INlists andORof equalities on one column prefilter like any other pushed predicate. A join orIN (SELECT ...)is applied after the search, whateverprefilteris.
Namespaces¶
Namespaces let you treat a directory (or a remote namespace service) as a database catalog and access datasets as tables.
Directory namespace¶
SQL identifiers follow DuckDB's case-insensitive matching while dataset names
retain their physical spelling. CREATE OR REPLACE, CTAS, and DROP TABLE use
the resolved dataset name. Discovery reports an ambiguity error if physical
names such as Foo.lance and foo.lance have the same DuckDB identifier.
Rename one dataset outside DuckDB to resolve that ambiguity.
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
-- A dataset stored at path/to/dir/my_table.lance becomes ns.main.my_table
SELECT count(*) FROM ns.main.my_table;
SHOW TABLES FROM ns.main;
DETACH ns;
REST namespace¶
ATTACH 'namespace_id' AS ns (TYPE LANCE, ENDPOINT 'http://127.0.0.1:2333');
SHOW TABLES FROM ns.main;
SELECT count(*) FROM ns.main.some_table;
DETACH ns;
Scans of REST namespace tables use the Lance Namespace query_table API.
Projection, supported filters, and LIMIT/OFFSET pairs are pushed into the
request; unsupported filters and standalone OFFSET operations remain in
DuckDB. Directory namespace scans continue to read the Lance dataset directly.
Set lance_namespace_query_table = false to have REST namespace scans open
the underlying dataset directly as well, e.g. when the namespace server does
not implement query_table.
Time travel¶
Attached tables accept DuckDB's AT clause to read an earlier version of the
dataset. AT (VERSION => n) reads Lance version n. AT (TIMESTAMP => ts)
reads the newest version committed at or before ts, and fails if the first
version is newer. The scan, projection, filter, and aggregate pushdown, and the
column list all come from that version, so a later ALTER TABLE does not
change the result.
SELECT * FROM ns.main.my_table AT (VERSION => 3);
SELECT count(*) FROM ns.main.my_table AT (TIMESTAMP => TIMESTAMP '2026-01-01 00:00:00');
-- Restore rows that were deleted since version 3
INSERT INTO ns.main.my_table
SELECT * FROM ns.main.my_table AT (VERSION => 3) WHERE id = 42;
Time travel is read-only: DuckDB does not accept AT on the target of
INSERT, UPDATE, DELETE, or MERGE. VERSION and TIMESTAMP are the
only units DuckDB's AT clause defines, so Lance tags and branches are not
addressable yet. On REST namespaces, historical reads open the dataset directly
rather than through query_table.
Write datasets¶
COPY ... TO ... (FORMAT lance, ...)¶
Write a new dataset (or overwrite an existing one):
COPY (
SELECT 1::BIGINT AS id, 'a'::VARCHAR AS s
UNION ALL
SELECT 2::BIGINT AS id, 'b'::VARCHAR AS s
) TO 'path/to/out.lance' (FORMAT lance, mode 'overwrite');
Append to an existing dataset:
COPY (SELECT 3::BIGINT AS id, 'c'::VARCHAR AS s)
TO 'path/to/out.lance' (FORMAT lance, mode 'append');
Create an empty dataset (schema only):
COPY (
SELECT 1::BIGINT AS id, 'x'::VARCHAR AS s
LIMIT 0
) TO 'path/to/empty.lance' (FORMAT lance, mode 'overwrite', write_empty_file true);
Notes:
- mode accepts create (the default), overwrite, or append.
create fails if the dataset already exists.
- data_storage_version defaults to 2.2 when COPY creates or overwrites a dataset. Appends preserve the existing storage version.
- enable_stable_row_ids (BOOLEAN) creates the dataset with stable row ids, so _rowid stays the same
across compaction; without them _rowid is the physical row address and changes when rows move. The
setting takes effect when the dataset is created. Appending to or overwriting a dataset with a
different setting is an error, and omitting the option keeps the dataset's setting.
- write_empty_file defaults to true and controls whether an empty dataset is materialized when the input produces zero rows.
- Floating-point list columns with consistent, nonzero dimensions are written as
fixed-size vectors. Prefer explicit FLOAT[N] / DOUBLE[N] types when writing
vectors; inconsistent list dimensions raise an error.
CREATE TABLE / CTAS in an attached namespace¶
When a directory is attached as a namespace, CREATE TABLE and CREATE TABLE AS SELECT write datasets into the namespace root.
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
-- Schema-only (creates an empty dataset)
CREATE OR REPLACE TABLE ns.main.my_empty (id BIGINT, s VARCHAR);
-- CTAS (writes query results)
CREATE OR REPLACE TABLE ns.main.my_dataset AS
SELECT 1::BIGINT AS id, 'a'::VARCHAR AS s
UNION ALL
SELECT 2::BIGINT AS id, 'b'::VARCHAR AS s;
SELECT count(*) FROM ns.main.my_dataset;
DETACH ns;
CREATE TABLE and CTAS default to data storage version 2.2. Override it with
WITH (data_storage_version = '<version>') when another version is required.
WITH (enable_stable_row_ids = true) creates the table with stable row ids, as for COPY, including for
tables created through a REST namespace. In a directory namespace, CREATE OR REPLACE TABLE keeps the
existing table's setting and fails when the option asks for another one; in a REST namespace it drops and
recreates the table, so the option (default off) applies to the new table. CREATE TABLE and CTAS reject
any other WITH option.
CREATE TABLE ns.main.my_stable WITH (enable_stable_row_ids = true) AS
SELECT 1::BIGINT AS id, 'a'::VARCHAR AS s;
DML on attached tables¶
These statements apply to tables inside an attached namespace (e.g. ns.main.my_table).
INSERT¶
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
INSERT INTO ns.main.my_table VALUES (3::BIGINT, 'c'::VARCHAR);
INSERT INTO ns.main.my_table SELECT 4::BIGINT AS id, 'd'::VARCHAR AS s;
DETACH ns;
UPDATE¶
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
UPDATE ns.main.my_table SET s = 'bb' WHERE id = 2;
UPDATE ns.main.my_table SET s = 'x'; -- update all rows
UPDATE ns.main.my_table SET s = DEFAULT WHERE id = 2;
DETACH ns;
An UPDATE commits against the table version its scan read. COMMIT fails with a retryable
conflict when another writer, or an earlier statement of the same transaction, updated or deleted
rows of a fragment it updates; changes to other fragments do not conflict. See
Transactions for what one transaction may contain.
A rewrite of a Lance fragment takes one new value per affected row, so UPDATE ... FROM assigns
each target row once. When several source rows match the same target row, Lance applies one of the
matches and counts, and returns, that target row once; which match is applied is not guaranteed.
DELETE¶
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
DELETE FROM ns.main.my_table WHERE id <= 2;
DELETE FROM ns.main.my_table WHERE id IN (SELECT id FROM staging);
DELETE FROM ns.main.my_table USING staging s WHERE my_table.id = s.id;
DELETE FROM ns.main.my_table; -- delete all rows
DETACH ns;
DELETEs of different rows both commit in concurrent connections, even when the rows share a
fragment. COMMIT still fails with a retryable conflict when another writer deleted one of the same
rows or rewrote one of the fragments (UPDATE, MERGE, compaction). See
Transactions for what one transaction may contain.
RETURNING¶
INSERT and UPDATE return the new row values, including evaluated defaults
and assignments; DELETE returns the old values. Expressions, aliases, and
RETURNING * use DuckDB's normal binding rules. UPDATE ... FROM and
DELETE ... USING return each affected target row once even when several
source rows match it.
INSERT INTO ns.main.my_table VALUES (5, 'e') RETURNING id, s;
UPDATE ns.main.my_table SET s = upper(s) WHERE id = 5 RETURNING *;
DELETE FROM ns.main.my_table WHERE id = 5 RETURNING id, s;
Returned rows have no guaranteed order. They describe the statement's affected
rows; inside an explicit transaction they do not imply that COMMIT has
succeeded. The existing transaction restrictions still apply.
A single INSERT, UPDATE, or DELETE CTE can feed a SELECT. A statement
containing a modifying CTE and a Lance write cannot combine multiple writes,
including other DML operations, CREATE TABLE AS, COPY TO, or query-backed
CREATE TYPE. These combinations fail before execution; ordinary CTAS and COPY
with read-only inputs still work.
MERGE INTO¶
MERGE is supported on Lance tables, including:
- WHEN MATCHED with UPDATE / DELETE / DO NOTHING / ERROR
- WHEN NOT MATCHED (BY TARGET) with INSERT / DO NOTHING
- WHEN NOT MATCHED BY SOURCE with UPDATE / DELETE / DO NOTHING
- RETURNING (including DuckDB's merge_action virtual column)
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
MERGE INTO ns.main.my_table AS t
USING (
SELECT 2::BIGINT AS id, 'bb'::VARCHAR AS s
UNION ALL
SELECT 3::BIGINT AS id, 'c'::VARCHAR AS s
) AS src
ON t.id = src.id
WHEN MATCHED THEN UPDATE SET s = src.s
WHEN NOT MATCHED THEN INSERT (id, s) VALUES (src.id, src.s);
MERGE INTO ns.main.my_table AS t
USING (SELECT 3::BIGINT AS id) AS src
ON t.id = src.id
WHEN MATCHED THEN DELETE
RETURNING merge_action, id, s;
DETACH ns;
Notes:
- Mutating the same target row more than once in a single MERGE raises a constraint error.
- MERGE uses a single Lance transaction per statement, committed against the table version its
scan read, with the same conflicts as UPDATE.
TRUNCATE TABLE¶
Transactions¶
Every INSERT, UPDATE, DELETE and MERGE on an attached Lance table builds its own Lance
transaction against the table version its scan read. COMMIT publishes them one at a time, each
as its own Lance commit. Lance has no commit that spans several statements or several datasets, and
a statement cannot read the uncommitted writes of an earlier statement of the same transaction.
Two such commits on the same table conflict unless they are appends or deletes of different rows.
To keep COMMIT from failing half-way, with the earlier statements already published, or from
silently dropping a change, a DELETE, UPDATE or MERGE that cannot follow the table's earlier
write in the transaction fails with a Not implemented Error. The statement is not executed;
ROLLBACK discards the transaction's earlier writes.
| Statements on one table in one transaction | Supported |
|---|---|
INSERT after any write |
yes |
DELETE after DELETE |
yes, if they delete different rows; a shared row still fails COMMIT with a retryable conflict |
UPDATE or MERGE after any write |
no |
DELETE after INSERT, UPDATE or MERGE |
no |
DELETE FROM t without a filter, or any DELETE after it |
no |
Limits that remain:
- Reads inside the transaction see the table as of the start of each statement, not the
transaction's own earlier writes. INSERT ... SELECT from the table being written does not see
the transaction's earlier INSERT.
- A transaction that writes several Lance tables commits them one after another. If a later commit
fails (for example because of a concurrent writer), the earlier tables stay committed.
DDL on attached tables¶
DROP TABLE¶
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
DROP TABLE ns.main.my_table;
DROP TABLE IF EXISTS ns.main.my_table;
DETACH ns;
Notes: - Directory namespaces reject unsafe dataset names that attempt path traversal.
ALTER TABLE¶
ALTER TABLE ... OPTIMIZE INDEXES is described in the Maintenance SQL section.
Schema evolution:
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
ALTER TABLE ns.main.my_table
ADD COLUMN age_plus_one BIGINT;
UPDATE ns.main.my_table SET age_plus_one = age + 1;
ALTER TABLE ns.main.my_table RENAME COLUMN score TO score2;
ALTER TABLE ns.main.my_table ALTER COLUMN age TYPE BIGINT;
ALTER TABLE ns.main.my_table ALTER COLUMN embedding TYPE FLOAT[128];
ALTER TABLE ns.main.my_table DROP COLUMN score2;
ALTER TABLE ns.main.my_table ALTER COLUMN age SET NOT NULL;
ALTER TABLE ns.main.my_table ALTER COLUMN age DROP NOT NULL;
DETACH ns;
Stored defaults must pass DuckDB's default binder; they cannot reference other
columns or use DataFusion-only functions. Use literal or cast-literal defaults
with the current DuckDB target. DuckDB rewrites other ADD COLUMN ... DEFAULT
forms into multiple statements ending in ALTER COLUMN ... SET DEFAULT, which
Lance does not support. Such a statement can fail after the column is added.
Table and column comments:
ATTACH 'path/to/dir' AS ns (TYPE LANCE);
COMMENT ON TABLE ns.main.my_table IS 'table comment';
COMMENT ON COLUMN ns.main.my_table.age_plus_one IS 'col comment';
DETACH ns;
Notes:
- ALTER COLUMN ... SET NOT NULL and DROP NOT NULL update Lance nullability metadata.
SET NOT NULL rejects a column containing NULL values; subsequent writes enforce the constraint.
Index DDL¶
The extension supports index DDL targeting a dataset path.
CREATE INDEX¶
Vector ANN index:
CREATE INDEX vec_idx ON 'path/to/dataset.lance' (vec)
USING IVF_FLAT WITH (num_partitions=1, metric_type='l2');
Scalar index:
Full-text index:
Notes:
- CREATE INDEX currently supports a single column.
- Option names in WITH (...) are case-insensitive, and each option may be given once.
- Vector indices accept these options; any other option, or a value of the wrong type, is an error:
- every type: metric_type (l2 by default, euclidean, cosine, dot, hamming),
num_partitions (default 256), version (v3; legacy only for IVF_PQ)
- IVF_PQ, IVF_HNSW_PQ: num_bits (default 8), num_sub_vectors (16), max_iterations (50)
- IVF_SQ, IVF_HNSW_SQ: num_bits (default 8), sample_rate (256)
- IVF_RQ: num_bits (default 8)
- IVF_HNSW_*: hnsw_m, hnsw_ef_construction, hnsw_max_level, hnsw_prefetch_distance
(Lance's defaults). To disable prefetching, give the full option set as params JSON with
"hnsw_prefetch_distance": null.
- Scalar and full-text index options are passed to Lance with lower-cased names. Lance validates
their values and ignores names it does not recognize.
- params = '<json object>' supplies the complete option set, with names as written, and cannot
be combined with other options except replace and train.
- For optional vector parameters, JSON null means omission, except that
hnsw_prefetch_distance: null disables prefetching.
- Vector indices require a fixed-size vector column. If a dataset was written with
FLOAT[] / DOUBLE[], first cast it with ALTER TABLE ... ALTER COLUMN ... TYPE FLOAT[N]
(or DOUBLE[N]) and then create the index.
SHOW INDEXES¶
DROP INDEX¶
ALTER INDEX ... OPTIMIZE¶
Use ALTER TABLE ... OPTIMIZE INDEXES for every index; see the Maintenance SQL section for the modes.
Maintenance SQL¶
Maintenance statements accept either:
- A dataset path string literal (for example 'path/to/dataset.lance')
- An attached Lance table name (for example ns.main.my_table)
OPTIMIZE¶
OPTIMIZE 'path/to/dataset.lance' WITH (
target_rows_per_fragment = 1048576,
max_rows_per_group = 1024,
max_bytes_per_file = 0,
materialize_deletions = true,
materialize_deletions_threshold = 0.1,
num_threads = 0,
batch_size = 0,
defer_index_remap = false
);
Returns:
- Operation (compact)
- Target
- MetricsJSON (compaction metrics)
VACUUM LANCE¶
VACUUM LANCE 'path/to/dataset.lance' WITH (
older_than_seconds = 1209600,
delete_unverified = false,
error_if_tagged_old_versions = true,
retain_n_versions = 3
);
Returns:
- Operation (cleanup)
- Target
- MetricsJSON (bytes_removed, old_versions, failed_deletes)
Notes:
- Cleanup is best-effort: a file that cannot be deleted is counted in failed_deletes and left in place for a later run instead of failing the statement.
- delete_unverified = true removes unreferenced data and index files regardless of age, including those of writes that have not committed, so use it only when nothing else is writing to the dataset. It is rejected while a transaction in this database holds Lance writes it has not committed, though a write or COMMIT that is still running is not detected. The rejection aborts the transaction it runs in, so a following COMMIT rolls that transaction back.
ALTER INDEX ... OPTIMIZE and ALTER TABLE ... OPTIMIZE INDEXES¶
ALTER INDEX vec_idx
ON 'path/to/dataset.lance'
OPTIMIZE WITH (
mode = 'merge',
num_indices_to_merge = 4
);
ALTER TABLE ... OPTIMIZE INDEXES optimizes every index of the table in one statement and accepts the same options (index_name is null in the metrics; a table without indices is a no-op). Lance may skip an index it cannot rewrite (for example one of an unknown type or a newer segment version) and only logs a warning that this extension does not surface; the metrics do not list skipped indices, so check SHOW INDEXES when it matters.
Supported mode values:
- append (default for ALTER INDEX): index new rows as one more segment; IVF partitions are never rebalanced
- auto (default for ALTER TABLE ... OPTIMIZE INDEXES): Lance's default merge count. A vector index appends a segment (always, if its segments use different IVF models), or merges all segments when a partition splits or joins, and with no new rows it may rebalance one segment or do nothing; scalar indices (BTree, Bitmap, NGram, INVERTED and the others) replace their newest segment with a merge of it and the new rows instead of adding one; with stable row ids, older segments that cover fragments with deletions are rewritten too
- merge (num_indices_to_merge defaults to 1; enable_partition_rebalancing = true also splits or joins partitions inside the merged segments)
- retrain: retrain vector indices over all rows (Lance supports it for v3 vector indices only; scalar indices only merge their newest segment, as under auto); rejects num_indices_to_merge
enable_partition_rebalancing is only valid with mode = 'merge'. Under auto a vector index gains one segment per run unless a partition splits or joins. A scheduled job that must keep the segment count flat should use mode = 'merge', num_indices_to_merge = 1, enable_partition_rebalancing = true; a larger num_indices_to_merge consolidates segments that have already accumulated.
Returns:
- Operation (optimize_index)
- Target
- MetricsJSON
Auto cleanup configuration¶
ALTER TABLE 'path/to/dataset.lance'
SET AUTO_CLEANUP WITH (interval = 1, older_than = '1h', retain_versions = 3);
ALTER TABLE 'path/to/dataset.lance' UNSET AUTO_CLEANUP;
SHOW MAINTENANCE ON 'path/to/dataset.lance';
SHOW MAINTENANCE returns key/value rows including:
- enabled
- interval (if configured)
- older_than (if configured)
- retain_versions (if configured)
Versions and tags¶
Every committed write creates a new dataset version. lance_versions and lance_tags are read-only and accept a dataset path string literal or an attached Lance table name.
-- List versions and the tags that point at them.
SELECT version, "timestamp", tags FROM lance_versions('path/to/dataset.lance');
-- List tags.
SELECT tag, version, branch, created_at, updated_at FROM lance_tags('ns.main.my_table');
lance_versionsreturnsversion(BIGINT),timestamp(TIMESTAMP WITH TIME ZONE) andtags(VARCHAR[]).lance_tagsreturnstag,version,branch(NULLfor the main branch),created_atandupdated_at.- Tags are shared by every branch of a dataset.
lance_versionslists only the tags on the opened branch.