Fix “Cannot Insert Into a Generated Column” in SQL

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

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.

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.

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

Write an explicit column list and leave the generated column out of it. In DuckDB the expression runs when the column is read, so the derived value follows changes to its inputs.

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.

update order_imports
set quantity = 10
where quantity = 2;

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

How other engines word it

Related errors

• Fix “NOT NULL Constraint Failed” in SQL

  A required column got no value. Supply one, give the column a default, or let it be null.

• Fix “CHECK Constraint Failed” in SQL

  A value is storable but breaks a business rule the table enforces. Read the expression.

• Fix “Failing Row Contains” Constraint Errors in SQL

  An INSERT or UPDATE broke a NOT NULL or CHECK rule on the table. The detail line shows the row.

Learn more

• SQL error messages explained
• Reference · CREATE TABLE
• Reference · Table constraints
• Lesson 6.1 · Calculated columns