SQL error

Fix “Cannot Insert Into a Generated Column” in SQL

A generated column computes itself. Leave it out of the column list entirely.

The symptom

A generated column is defined as an expression over the table's other columns. The database derives its value, so supplying one is not a value it can accept and reject - it is a column you are not allowed to write at all.

DuckDB reports Binder Error: Cannot insert into a generated column. It is a binder error rather than a constraint error, which is the clue that nothing about your data is wrong. The statement is rejected before any row is examined.

Without a column list you get a different error

This one is worth knowing, because the naive attempt produces a message that points somewhere else entirely. A generated column is not part of the insert target, so insert into t values (...) with a value for it looks to DuckDB like too many values:Binder Error: table order_imports has 2 columns but 3 values were supplied.

The count, not the column, gets blamed

create or replace table order_imports (
  quantity integer,
  unit_price decimal(10,2),
  line_total decimal(12,2) generated always as (quantity * unit_price)
);

insert into order_imports values (2, 5.00, 10.00);

Three columns are visible in DESCRIBE and only two are writable. If you are chasing a column-count error on a table you are sure you counted correctly, a generated column is a likely reason.

UPDATE is refused too

The same applies after the row exists, with a third message:Binder Error: Cant update column "line_total" because it is a generated column! To change a generated value you change its inputs, and it follows.

Updating a generated column

create or replace table order_imports (
  quantity integer,
  unit_price decimal(10,2),
  line_total decimal(12,2) generated always as (quantity * unit_price)
);
insert into order_imports (quantity, unit_price) values (2, 5.00);

update order_imports set line_total = 99;

The fix: name only the real columns

Always write an explicit column list, and leave the generated column out of it. The expression runs on read, so the derived value is correct the moment the row lands and stays correct when its inputs change.

Insert the inputs, read the result

create or replace table order_imports (
  quantity integer,
  unit_price decimal(10,2),
  line_total decimal(12,2) generated always as (quantity * unit_price)
);

insert into order_imports (quantity, unit_price)
values (2, 5.00), (3, 4.50);

select quantity, unit_price, line_total
from order_imports
order by quantity;

Changing an input recomputes the output with no second statement and no chance of the two disagreeing, which is the reason to use a generated column instead of a plain column you remember to keep in sync.

Change the input, the output follows

update order_imports
set quantity = 10
where quantity = 2;

select quantity, unit_price, line_total
from order_imports
order by quantity;

DuckDB generated columns are virtual only

Several databases let you choose between a VIRTUAL column, computed on read, and a STORED one, computed on write and kept on disk. DuckDB supports only the virtual form and rejects the other outright: Invalid Input Error: Can not create a STORED generated column! If you need the value materialised, that is aCTAS snapshot, not a column option.

How other engines word it

Engine	Message
DuckDB	`Binder Error: Cannot insert into a generated column`
PostgreSQL	`ERROR: cannot insert a non-default value into column "line_total"`
MySQL	`The value specified for generated column 'line_total' in table 'order_imports' is not allowed.`
SQL Server	`The column "line_total" cannot be modified because it is either a computed column or is the result of a UNION operator.`
SQLite	`cannot INSERT into generated column "line_total"`