Deviopath › PostgreSQL

Generated Line Total

Hard · DDL

A generated column is computed from other columns of the same row, and Postgres keeps it up to date on every INSERT and UPDATE: ADD COLUMN c type GENERATED ALWAYS AS (expression) STORED. STORED saves the value on disk (in PostgreSQL 18 the default is VIRTUAL, computed when read).

Add a stored generated column line_total numeric(10, 2) to order_items. Its value is quantity * unit_price minus discount_pct percent, rounded to 2 decimals.

The check inserts and updates rows afterwards, so a one-time UPDATE is not enough.

Next: Priority as an Enum