SQL Generated Columns

A generated column is defined by an expression instead of filled in by inserts. Depending on the database you will see it called a computed, virtual, or calculated column. They are the same idea: a margin derived from price and cost, a line total from quantity and unit price, kept in the table definition so every query reads the same answer.

create or replace table product_margins (
  product_id integer primary key,
  product_name varchar,
  price decimal(10, 2),
  cost decimal(10, 2),
  margin decimal(10, 2) generated always as (price - cost) virtual
);

insert into product_margins (product_id, product_name, price, cost)
select product_id, product_name, price, cost
from products;

select product_id, product_name, price, cost, margin
from product_margins
order by product_id
limit 5;

The insert names four columns and never mentions margin. DuckDB works it out when the row is read. VIRTUAL means the value is computed on read, not stored. In DuckDB it is the only kind there is.

The short form

create or replace table line_totals (
  order_item_id integer primary key,
  quantity integer,
  unit_price decimal(10, 2),
  line_total as (quantity * unit_price)
);

insert into line_totals (order_item_id, quantity, unit_price)
select order_item_id, quantity, unit_price
from order_items;

select order_item_id, quantity, unit_price, line_total
from line_totals
order by line_total desc, order_item_id
limit 5;

GENERATED ALWAYS, VIRTUAL, and the type can all be left out. DuckDB infers the type from the expression: line_total comes out as DECIMAL(18,2). Declaring a type casts the result to it, so an integer generated column over a decimal expression rounds the value.

It always matches the row

update product_margins
set price = 36.00
where product_id = 1;

select product_id, price, cost, margin
from product_margins
where product_id = 1;

Nobody touched margin, and it reads 18.00 now. This is the whole appeal: there is no second copy of the margin to forget to update.

Writing to it is refused

insert into product_margins (product_id, product_name, price, cost, margin)
values (100, 'Bird Netting', 10.00, 4.00, 6.00)

DuckDB answers Cannot insert into a generated column. Leave the column out of the list. If you hit this loading data, see Cannot insert into a generated column.

update product_margins
set margin = 0
where product_id = 1

That fails too, with Cant update column "margin" because it is a generated column!. To change a generated value, change the columns it is computed from.

insert into product_margins
values (100, 'Bird Netting', 10.00, 4.00);

select product_id, product_name, price, cost, margin
from product_margins
where product_id = 100;

Without a column list, DuckDB expects a value for every ordinary column and none for the generated one. Four values fill a five-column table here. Supplying five fails with table product_margins has 4 columns but 5 values were supplied, which is confusing to read when DESCRIBE lists five.

What the expression can use

Columns from the same row, literals, and ordinary functions, including other generated columns in the same table. DuckDB refuses anything that needs more than one row or another table:

create or replace table category_prices (
  category_id integer,
  price decimal(10, 2),
  category_total as (sum(price))
)

The error is Aggregate functions are not supported here. A subquery, a column qualified with another table's name, and a column that depends on itself (directly or through another generated column) are refused the same way. Totals across rows belong in a query or a view, not a column.

Finding them in the catalog

select column_name, data_type, column_default as expression
from duckdb_columns()
where table_name = 'product_margins'
order by column_index

DESCRIBE lists a generated column like any other, with nothing to mark it, and information_schema.columns leaves its is_generated field empty. duckdb_columns() is where the expression shows up, in column_default, wrapped in a cast to the column's type.

Changing one means rebuilding

alter table product_margins
add column margin_pct as (round(100.0 * margin / price, 1))

DuckDB answers Adding generated columns after table creation is not supported yet. Dropping an input column fails as well, while a generated column depends on it:

alter table product_margins
drop column cost

That one says Cannot drop column: column is a dependency of 1 or more generated column(s). Either way, the fix is a new table with the definition you want, filled from the old one:

create or replace table product_margins_v2 (
  product_id integer primary key,
  product_name varchar,
  price decimal(10, 2),
  cost decimal(10, 2),
  margin decimal(10, 2) generated always as (price - cost) virtual,
  margin_pct as (round(100.0 * margin / price, 1))
);

insert into product_margins_v2 (product_id, product_name, price, cost)
select product_id, product_name, price, cost
from product_margins;

drop table product_margins;
alter table product_margins_v2 rename to product_margins;

select product_id, price, margin, margin_pct
from product_margins
order by product_id
limit 3;

margin_pct is built on margin, another generated column. DuckDB allows that; Oracle and PostgreSQL do not.

create or replace table margins_snapshot as
select * from product_margins;

update margins_snapshot
set margin = 0
where product_id = 1;

select product_id, price, cost, margin
from margins_snapshot
where product_id = 1;

The copy accepts a margin of 0 on a product priced at 36.00 with a cost of 18.00. The original table would have refused the update. See CREATE TABLE AS SELECT.

When the value must stop changing

A generated column is recomputed every time it is read, so it can never remember an earlier state. That rules it out for a flag meant to stay set, like "this product ran low at some point", even after the stock that triggered it is gone.

create or replace table stock_status (
  product_id integer primary key,
  quantity_on_hand integer,
  reorder_level integer,
  needs_reorder as (
    case when quantity_on_hand <= reorder_level then 'Y' else 'N' end
  )
);

insert into stock_status (product_id, quantity_on_hand, reorder_level)
select product_id, quantity_on_hand, reorder_level
from products;

-- a delivery arrives for product 14
update stock_status
set quantity_on_hand = 50
where product_id = 14;

select product_id, quantity_on_hand, reorder_level, needs_reorder
from stock_status
where product_id in (11, 14)
order by product_id;

Product 14 had none in stock and read Y. After the delivery it reads N, and the record that it ever ran out is gone. That is right for "needs reordering now" and wrong for "has run low before".

create or replace table stock_alerts (
  product_id integer primary key,
  quantity_on_hand integer,
  reorder_level integer,
  flagged_low varchar
);

insert into stock_alerts (product_id, quantity_on_hand, reorder_level)
select product_id, quantity_on_hand, reorder_level
from products;

-- run after every stock change: it only fills flags that are still empty
update stock_alerts
set flagged_low = 'Y'
where flagged_low is null
  and quantity_on_hand <= reorder_level;

-- the same delivery
update stock_alerts
set quantity_on_hand = 50
where product_id = 14;

update stock_alerts
set flagged_low = 'Y'
where flagged_low is null
  and quantity_on_hand <= reorder_level;

select product_id, quantity_on_hand, reorder_level, flagged_low
from stock_alerts
where product_id in (11, 14)
order by product_id;

The where flagged_low is null is what makes it stick: once a row is flagged, later runs leave it alone. In Oracle, PostgreSQL, or SQL Server the same rule is often a trigger that fires on update. DuckDB has no triggers, so the UPDATE runs as a step in the load script. See safe UPDATE and DELETE.

Generated column, view, or query?

Across databases

Every major engine has the feature. The spelling, the default, and whether the value can be stored all differ.