Create a computed column

Applies to: Embedded ❌, Enterprise ✅ Lance can write a new column without rewriting the columns already in the table. That matters most on a table that also holds large images, video or embeddings, where rewriting everything just to add one small derived field would be wasteful. See Lance format for the storage model this relies on. A computed column is how LanceDB formalizes that capability for feature engineering specifically: instead of writing the derived values immediately, it is declared with an expression and starts with no values. Declaring it is cheap on any table size, because nothing is evaluated until a refresh asks for it.

computed requires LanceDB Enterprise. Ordinary eager add_columns transforms, without computed, remain available in LanceDB Embedded.

Fill a column with a Function

Enterprise When the value cannot be expressed in SQL — an embedding, a model call, an image decode — the column is filled by a registered function instead of a SQL expression. It needs a deployment with Functions enabled. Connect with an Enterprise connection. Continuing the caption Function registered in Create a Function, against a frames table that already has a jpeg column:

version = db.create_function_async(caption).wait()
table.add_columns({"caption": version(jpeg=col("jpeg"))})

ALTER TABLE frames ADD COLUMN caption AS caption(jpeg) WITH (version = 'fv_1')

A Function application is called with its parameters bound to source columns through col(...), keyed by the output column name. It commits a new, empty table version the same way a SQL expression does, so the usual versioning applies. Both a Function application and a SQL expression accept the same auto_refresh_enabled and auto_refresh_interval_seconds options, and both refresh and toggle the same way; see Auto Refresh.

Fill a column with a SQL expression

A value that SQL can compute doesn’t need a Function. Declare it with computed instead, as an expression evaluated over the row:

table = db.create_table("computed_demo", [{"x": 1}, {"x": 2}])

table.add_columns(computed={"doubled": "x * 2"})
# AddColumnsResult(version=2)

ALTER TABLE computed_demo ADD COLUMN doubled AS x * 2

That commits a new table version with the column present and empty. It is an ordinary table version, so the usual versioning applies, including that you can travel back to before the column existed. The expression can reference any column in the table, including another computed column — declaration order is dependency order, so it may reference one declared earlier in the same mapping, but not one declared after it:

table.add_columns(computed={
    "power_score": "cast(x as double) / 2.0",
    "is_strong":   "power_score > 0.5",
})

ALTER TABLE computed_demo ADD COLUMN power_score AS cast(x as double) / 2.0
ALTER TABLE computed_demo ADD COLUMN is_strong AS power_score > 0.5

The functions available are the ones the SQL dialect documents. A literal in the expression has to fit the column it meets: x / 2.0 is refused when x is an integer column, so write cast(x as double) / 2.0.

Keep the expression to top-level columns. A column refresh can fail on an expression that reads a field inside a struct, such as stats.magic + stats.strength. Try a new expression on a small table first.

Compute now or on refresh

add_columns takes either, and the difference matters:

SQL expressions in transforms are evaluated now, for every row, as part of the call. Good for a small table, or a column you want populated immediately. A FunctionApplication passed through transforms instead declares empty Function-bound columns, which a refresh fills; that needs a deployment with Functions enabled, and the embedded library refuses it.
computed stores the expression and evaluates nothing. The column is committed empty and filled later by refresh_column.

computed and transforms cannot be combined in one call. Reach for computed when the table is large enough that you would rather control when the work happens. Automatic refresh is on by default for each computed column, and takes effect when your Enterprise deployment enables its refresh scheduler. Until then, call refresh_column yourself. Pass auto_refresh_enabled=False to opt a column out, or auto_refresh_interval_seconds to set how often it refreshes:

table.add_columns(
    computed={"doubled": "x * 2"},
    auto_refresh_interval_seconds=300,
)

ALTER TABLE computed_demo ADD COLUMN doubled AS x * 2
WITH (auto_refresh_interval_seconds = 300)

See Auto Refresh for the full behavior, including how to change the setting later. An eager transforms call looks like this, starting from a small product table:

import numpy as np

data = [
    {
        "id": 1,
        "name": "Laptop",
        "price": 1200.00,
        "vector": np.random.random(128).tolist(),
    },
    {
        "id": 2,
        "name": "Smartphone",
        "price": 800.00,
        "vector": np.random.random(128).tolist(),
    },
    {
        "id": 3,
        "name": "Headphones",
        "price": 150.00,
        "vector": np.random.random(128).tolist(),
    },
]
table = db.create_table("schema_evolution_add_example", data, mode="overwrite")

# Add a discounted price column (10% discount)
table.add_columns({"discounted_price": "cast((price * 0.9) as float)"})

ALTER TABLE schema_evolution_add_example ADD COLUMN discounted_price
AS cast((price * 0.9) as float)

SQL’s ADD COLUMN ... AS always declares the column empty and defers the expression to a refresh, the same as computed does — there is no SQL form of eager transforms. The Python call above is the only way to get discounted_price filled immediately; declaring it in SQL fills it only once REFRESH COLUMN runs. For feature engineering on large existing tables, group related derived features into one add_columns call instead of running many separate writes: this creates one new table version for the schema change and computes every new column from the existing rows in it, rather than growing the table’s version history with many small updates.

On local tables, Python add_columns also accepts pandas or Polars DataFrames, PyArrow Tables, RecordBatches, RecordBatchReaders, and iterables of batches. Pass only the new columns, with exactly one row per current live table row in storage scan order. DataFrame indexes are ignored, and reader batch boundaries need not match table fragments. Keep the table unchanged between computing the values and adding them. Readers are consumed once.Data inputs cannot be combined with computed columns and are not supported on remote tables. Python callables and batch UDFs are not accepted; compute those values first, then pass the result as a DataFrame or reader.

Change columns used by a computed column

While a computed column’s declaration references another column, that column cannot be renamed, retyped, or dropped. Drop the computed column first if you need to change one of its inputs.

This is worth knowing before deriving a column from something you expect to reshape later. Dropping the computed column releases the constraint.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Create a computed column

Fill a column with a Function

Fill a column with a SQL expression

Compute now or on refresh

Change columns used by a computed column

Related guides

Refresh a computed column

Functions

​Fill a column with a Function

​Fill a column with a SQL expression

​Compute now or on refresh

​Change columns used by a computed column

​Related guides

Refresh a computed column

Functions

Fill a column with a Function

Fill a column with a SQL expression

Compute now or on refresh

Change columns used by a computed column

Related guides