Generated columns

A generated column holds a value that the database computes from the other columns of the same row: a line total from price and quantity, a lower-case copy of an email address for searching, or a field taken out of a JSON document. Nobody writes it. The database computes it again whenever the row’s other columns change, so it can never disagree with them, whichever program wrote the row. There are two kinds. A STORED generated column is computed on every write and kept in the row like any other column. A VIRTUAL generated column is computed on every read and takes no space in the row. SQLite and MySQL have both and use VIRTUAL when you name neither; PostgreSQL had only STORED until version 18, which added VIRTUAL and made it the default. The runnable examples use sql.js 1.14.2 and so SQLite 3.49.1; where PostgreSQL 18 or MySQL 8.4 behave otherwise, the text says so.

Two generated columns at work

-- Order lines whose total and tax the database computes: one generated column computed on every read (VIRTUAL),
-- one computed on every write and kept in the row (STORED).
CREATE TABLE order_lines (
  line_id          INTEGER PRIMARY KEY,
  product          TEXT    NOT NULL,
  unit_price_paise INTEGER NOT NULL,
  qty              INTEGER NOT NULL,
  line_total_paise INTEGER GENERATED ALWAYS AS (unit_price_paise * qty) VIRTUAL,
  tax_paise        INTEGER GENERATED ALWAYS AS ((unit_price_paise * qty * 18 + 50) / 100) STORED
);
INSERT INTO order_lines (product, unit_price_paise, qty) VALUES
  ('Basmati rice 1 kg', 14999, 2), ('Toor dal 500 g', 8950, 1), ('Masala chai 250 g', 12000, 3);
SELECT product, unit_price_paise, qty, line_total_paise, tax_paise FROM order_lines;

-- Change the quantity, and both generated columns follow.
UPDATE order_lines SET qty = 4 WHERE product = 'Toor dal 500 g';
SELECT product, qty, line_total_paise, tax_paise FROM order_lines WHERE product = 'Toor dal 500 g';

-- A generated column cannot be written, not even with the value it would compute.
INSERT INTO order_lines (product, unit_price_paise, qty, line_total_paise) VALUES ('Ghee 500 ml', 32000, 1, 32000);

-- The catalog marks them: hidden = 2 for a virtual column, 3 for a stored one.
SELECT name, hidden FROM pragma_table_xinfo('order_lines') WHERE hidden > 0;

┌───────────────────┬──────────────────┬─────┬──────────────────┬───────────┐
│      product      │ unit_price_paise │ qty │ line_total_paise │ tax_paise │
├───────────────────┼──────────────────┼─────┼──────────────────┼───────────┤
│ Basmati rice 1 kg │ 14999            │ 2   │ 29998            │ 5400      │
│ Toor dal 500 g    │ 8950             │ 1   │ 8950             │ 1611      │
│ Masala chai 250 g │ 12000            │ 3   │ 36000            │ 6480      │
└───────────────────┴──────────────────┴─────┴──────────────────┴───────────┘
┌────────────────┬─────┬──────────────────┬───────────┐
│    product     │ qty │ line_total_paise │ tax_paise │
├────────────────┼─────┼──────────────────┼───────────┤
│ Toor dal 500 g │ 4   │ 35800            │ 6444      │
└────────────────┴─────┴──────────────────┴───────────┘
┌──────────────────┬────────┐
│       name       │ hidden │
├──────────────────┼────────┤
│ line_total_paise │ 2      │
│ tax_paise        │ 3      │
└──────────────────┴────────┘

Parse error near line 20: cannot INSERT into generated column "line_total_paise"

The inserts named only the product, its price and its quantity, and both generated columns appeared with their values. When the quantity of dal changed to 4, the total and the tax followed without any code asking for it. The insert that tried to write line_total_paise itself was refused, even though it wrote the value the column would have had: a generated column belongs to the database. The tax is computed with whole numbers only, adding 50 before dividing by 100 so that it rounds half up, which keeps it exact in every engine. In the catalog, pragma_table_xinfo() marks a virtual column with hidden 2 and a stored one with 3.

STORED or VIRTUAL

A stored column costs space and a little work on every write, and saves the work on every read; a virtual one is the other way round. For a cheap expression, such as a product of two numbers, VIRTUAL is the natural choice. For an expensive one that is read often, or one that must carry an index or a unique rule in PostgreSQL, STORED pays its way.

Where generated columns help

Four uses cover most of them. Derived amounts that must never drift from their inputs, such as a line total or a tax amount. Search keys that normalise a value, such as an email address in lower case or a product code without spaces, so that every query and every rule uses the same form. Fields pulled out of a JSON document, so that they can be filtered, sorted and constrained like ordinary columns. Parts of a value that queries group by, such as the year of an invoice date. What a generated column cannot hold is anything computed from other rows or tables, such as the total of an order from its items: that needs a view, a summary table kept by a trigger, or a query, which later modules cover.

Writing the generation expression

The syntax is the same everywhere: name type GENERATED ALWAYS AS (expression) STORED or VIRTUAL, and SQLite and MySQL also accept the short form name type AS (expression). The expression has rules of its own:

• The same row only. It may use the row’s ordinary columns, literals and functions, but no subquery and no other row.
• The same answer every time. Functions whose result can change, such as random() or the current time, are refused, because a stored value would go stale and a virtual one would change between two reads.
• Other generated columns may be used in SQLite, and in MySQL if they are defined earlier; PostgreSQL forbids it.
• Constraints still apply. A generated column may carry NOT NULL and CHECK, which test the computed value.

Your turn

-- Add the generated column price_with_tax_paise: price plus tax, in whole paise, rounded half up.
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  price_paise INTEGER NOT NULL,
  tax_pct     INTEGER NOT NULL
);

-- Leave these lines as they are.
INSERT INTO products (product_id, name, price_paise, tax_pct)
SELECT product_id, name, price_paise, tax_pct FROM catalogue;
UPDATE products SET price_paise = price_paise + 100 WHERE product_id = 1;
SELECT product_id, name, price_with_tax_paise FROM products WHERE price_with_tax_paise <= 50000 ORDER BY product_id;

# Sample tests: each one fills the table catalogue, runs your script and compares the rows it returns, in order.
tests:
  - name: adds the tax, rounds half up, and follows a changed price
    ordered: true
    setup: |
      CREATE TABLE catalogue (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL, tax_pct INTEGER NOT NULL);
      INSERT INTO catalogue VALUES
        (1, 'Steel tiffin box', 899, 5),
        (2, 'Cotton kurta', 99900, 5),
        (3, 'Pressure cooker 3 L', 189900, 18),
        (4, 'Notebook, 200 pages', 4500, 12);
    expected:
      columns: [product_id, name, price_with_tax_paise]
      rows:
        - [1, 'Steel tiffin box', 1049]
        - [4, 'Notebook, 200 pages', 5040]
  - name: keeps prices that come to exactly 50,000 paise with tax, and rounds a half paisa up
    ordered: true
    setup: |
      CREATE TABLE catalogue (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL, tax_pct INTEGER NOT NULL);
      INSERT INTO catalogue VALUES
        (1, 'Brass diya', 210, 5),
        (2, 'Ceiling fan', 42373, 18),
        (3, 'Wall clock', 47619, 5);
    expected:
      columns: [product_id, name, price_with_tax_paise]
      rows:
        - [1, 'Brass diya', 326]
        - [2, 'Ceiling fan', 50000]
        - [3, 'Wall clock', 50000]

Indexing generated columns in each engine, with query plans Pro: opens with a Pro, Premium or Ultimate pass

Limits, failure cases and money arithmetic, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• A generated column is computed by the database from the other columns of its row, and can never be written.
• STORED columns are computed on every write and take space; VIRTUAL ones are computed on every read and take none.
• Generation expressions use only the row itself and functions whose result never changes.
• SQLite and MySQL use VIRTUAL by default; PostgreSQL 18 does too, and earlier versions offer only STORED.