Generated column
A generated column is a table column whose value is automatically computed from an expression involving other columns, rather than being stored directly by INSERT or UPDATE statements.
Overview
A generated (or computed) column is defined by an expression instead of a plain data type, and its value is derived automatically from other columns in the same row. Applications cannot insert or update a generated column's value directly — the database computes it.
Copy code
CREATE TABLE orders (
quantity INTEGER,
unit_price DECIMAL(10,2),
total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price)
);
Generated columns are useful for keeping derived values (totals, normalized text, extracted JSON fields) consistent and in sync with their source columns automatically, without relying on application code or triggers to recompute them.
Virtual vs stored
SQL databases that support generated columns typically offer two flavors: virtual (computed on the fly whenever the column is read, not stored on disk) and stored (computed once and persisted to disk, refreshed whenever the source columns change, trading storage for read speed).
DuckDB specifics
DuckDB supports generated columns with the syntax column_name [type] GENERATED ALWAYS AS (expression) [VIRTUAL]:
Copy code
CREATE TABLE t1 (x FLOAT, two_x AS (2 * x)); -- type inferred, virtual
CREATE TABLE t2 (x FLOAT, two_x FLOAT GENERATED ALWAYS AS (2 * x) VIRTUAL); -- fully explicit
As of the current DuckDB version, only the VIRTUAL variant is implemented — generated column values are computed from the expression each time the column is referenced, rather than being persisted to disk; STORED generated columns are not yet supported. A generated column's expression can reference other regular columns or even other generated columns defined earlier in the same table.
Related terms
A column represents a single field or attribute in a database table or DataFrame that contains values of the same data type.
Columnar storage →Columnar storage is a data layout that groups values from the same column together on disk, rather than storing complete rows contiguously, which speeds up analytical scans and compression.
UNPIVOT →UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT.
Natural key →A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
FAQS
No. Generated columns derive their value from an expression over other columns, so INSERT and UPDATE statements cannot set them directly — the database computes the value automatically.
Currently only VIRTUAL generated columns are supported in DuckDB, meaning the value is recomputed from the expression each time it's read rather than persisted to disk. STORED generated columns are not yet implemented.
