Columns That Compute Themselves

Constraints reject bad data. These three tools go further: they let the schema itself carry logic. A value gets computed for you. A rule travels with a type instead of being retyped on every column. Generated columns, domains, and enums all push correctness down into the table definition, where it’s enforced once and no caller can forget it. And PostgreSQL 18 changed the default on the first of them — a good reason to check what actually happens rather than trust an older tutorial.

Generated columns: a value the table derives

A generated column is defined by an expression over the row’s other columns. You never write it; the database computes it. The classic case is a line total that must always equal quantity times price:

CREATE TABLE scratch.invoice_line (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    quantity   integer NOT NULL,
    unit_price numeric(6,2) NOT NULL,
    line_total numeric(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

Insert only the source columns, and line_total fills itself in:

INSERT INTO scratch.invoice_line (quantity, unit_price) VALUES (3, 12.50), (1, 99.99);
SELECT * FROM scratch.invoice_line ORDER BY id;

 id | quantity | unit_price | line_total
----+----------+------------+------------
  1 |        3 |      12.50 |      37.50
  2 |        1 |      99.99 |      99.99

The value can never drift out of sync with its inputs, because it is its inputs. Change quantity and the total follows in the same statement:

UPDATE scratch.invoice_line SET quantity = 5 WHERE id = 1;
SELECT * FROM scratch.invoice_line WHERE id = 1;

 id | quantity | unit_price | line_total
----+----------+------------+------------
  1 |        5 |      12.50 |      62.50

37.50 became 62.50 with no code to keep them consistent. That’s the point of a generated column over a plain one you update by hand: consistency isn’t a discipline you have to maintain, it’s a property of the schema.

And because the column is the expression, you’re not allowed to write it directly. Try to supply it on insert:

INSERT INTO scratch.invoice_line (quantity, unit_price, line_total) VALUES (2, 10.00, 999.00);

ERROR:  cannot insert a non-DEFAULT value into column "line_total"
DETAIL:  Column "line_total" is a generated column.

And on update:

UPDATE scratch.invoice_line SET line_total = 0 WHERE id = 1;

ERROR:  column "line_total" can only be updated to DEFAULT
DETAIL:  Column "line_total" is a generated column.

Both refused. There is no way to store a line_total that disagrees with quantity * unit_price, which is exactly the guarantee you want from a derived value.

STORED vs VIRTUAL, and what PostgreSQL 18 changed

A generated column comes in two flavors. STORED computes the value on write and keeps it on disk like an ordinary column. It costs space, reads for free, and can be indexed. VIRTUAL computes the value on read and stores nothing. It costs nothing on disk but recomputes every time you select it.

Before Postgres 18, STORED was the only option and you had to spell it out. Postgres 18 added VIRTUAL and made it the default when you write neither keyword. That’s a real behavior change, so check it directly rather than trust the docs. A virtual column works as you’d expect:

CREATE TABLE scratch.gen_virtual (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    qty integer NOT NULL,
    price numeric(6,2) NOT NULL,
    total numeric(10,2) GENERATED ALWAYS AS (qty * price) VIRTUAL
);
INSERT INTO scratch.gen_virtual (qty, price) VALUES (4, 5.00);
SELECT * FROM scratch.gen_virtual;

 id | qty | price | total
----+-----+-------+-------
  1 |   4 |  5.00 | 20.00

And when you omit the keyword entirely, Postgres 18 makes it virtual — you can confirm the storage kind in the catalog (v = virtual, s = stored):

CREATE TABLE scratch.gen_default (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    qty integer NOT NULL,
    total integer GENERATED ALWAYS AS (qty * 2)   -- no keyword
);
SELECT attname, attgenerated AS kind FROM pg_attribute
WHERE attrelid = 'scratch.gen_default'::regclass AND attname = 'total';

 attname | kind
---------+------
 total   | v

So on Postgres 18 a bare GENERATED ALWAYS AS (...) is now virtual: not stored on disk, not indexable. That reverses the pre-18 assumption. If you want it materialized and indexable, write STORED explicitly. (MySQL has had both STORED and VIRTUAL for years, with VIRTUAL its default too; SQLite supports both as well.)

DOMAINs: a type with its rule attached

A domain is a named type built from an existing one plus its own constraints. It bottles up “a base type and the rules that always apply to it” so you can reuse it across many columns without retyping the CHECK each time. Two useful ones — a price that must be positive, and a text value shaped like an email:

CREATE DOMAIN scratch.positive_price AS numeric(6,2)
    CHECK (VALUE > 0);
CREATE DOMAIN scratch.email AS text
    CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');

VALUE is the keyword for the value being checked. Now use them like any type:

CREATE TABLE scratch.product (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price   scratch.positive_price NOT NULL,
    contact scratch.email
);
INSERT INTO scratch.product (price, contact) VALUES (19.99, 'shop@example.com') RETURNING *;

 id | price |     contact
----+-------+------------------
  1 | 19.99 | shop@example.com

A valid row goes in. A bad one is rejected by the domain. The error names the domain’s constraint, so the same message appears everywhere the type is used, not per-table:

INSERT INTO scratch.product (price, contact) VALUES (-1.00, 'a@b.com');

ERROR:  value for domain scratch.positive_price violates check constraint "positive_price_check"

INSERT INTO scratch.product (price, contact) VALUES (5.00, 'not-an-email');

ERROR:  value for domain scratch.email violates check constraint "email_check"

The payoff over a repeated column CHECK is one definition, reused: write positive_price once, apply it to every money column, change the rule in a single place. It’s the type-level version of the instinct behind normalization — one fact, one definition. (Domains are Postgres and standard-SQL; MySQL doesn’t support them, so there you repeat the CHECK per column.)

ENUMs: a fixed set of labels, in order

CREATE TYPE ... AS ENUM defines a type whose values are a fixed list of labels. It’s the right tool when a column may hold only one of a small, closed set — an order status, a size, a tier:

CREATE TYPE scratch.order_status AS ENUM ('placed', 'shipped', 'delivered', 'returned', 'cancelled');

CREATE TABLE scratch.o (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status scratch.order_status NOT NULL
);
INSERT INTO scratch.o (status) VALUES ('delivered'), ('placed'), ('shipped');

One detail people get wrong: an enum sorts by declaration order, not alphabetically. We declared placed before shipped before delivered, so that’s how they sort — exactly what you want for a status with a natural progression:

SELECT id, status FROM scratch.o ORDER BY status;

 id |  status
----+-----------
  2 | placed
  3 | shipped
  1 | delivered

Alphabetically that would be delivered, placed, shipped. It isn’t, because the enum remembers the order you defined it in. And a value outside the set is rejected at the type level:

INSERT INTO scratch.o (status) VALUES ('refunded');

ERROR:  invalid input value for enum scratch.order_status: "refunded"

The trade-off: enums are compact and self-documenting, but changing one is awkward. You can ALTER TYPE ... ADD VALUE to append a label, but removing or reordering labels means rebuilding the type. So when the allowed values shift often, a lookup table with a foreign key is more flexible — that’s the referential pattern from chapter 2. When the set is genuinely fixed, an enum is tidier. (MySQL has an inline ENUM('a','b') column type, similar in spirit but not a reusable named type the way Postgres’s is.)

Final thoughts

These three features move logic out of your application and into the schema, where it’s enforced once and can’t be bypassed. A generated column derives a value from its neighbors and won’t let you contradict it. On Postgres 18, remember the default is now VIRTUAL, so add STORED when you want it on disk and indexable. A domain packages a base type with its rule so the same constraint, and the same error, apply everywhere the type is used. An enum pins a column to a fixed, ordered set of labels. Each one is the schema doing work you’d otherwise trust every caller to do correctly. But some logic is too complex for a column expression or a CHECK — it needs to run a whole procedure when a row changes. That’s the next tool, and the most powerful one in the schema’s kit.

Next: Code that fires on a change: triggers — trigger functions, BEFORE and AFTER, and the row-level logic that runs on every insert, update, and delete.