Generated Columns in PostgreSQL: Replacing Oracle Virtual Columns

Same Idea, One Critical Difference

Oracle virtual columns and PostgreSQL generated columns describe the same idea: a column whose value is an expression over other columns in the row, not something you insert. full_name AS (first || ' ' || last), total AS (qty * price). They migrate cleanly — except on the one axis that actually matters: storage.

Oracle virtual columns are VIRTUAL by default — computed on read, taking zero disk. PostgreSQL, through version 17, offers only STORED generated columns — computed on write and persisted to disk. So a naive port silently converts a free, zero-storage column into one that grows every row. Usually fine; occasionally a problem you want to see coming.

The Clean Mapping

For most virtual columns the direct translation is correct and boring — which is what you want:

-- Oracle (VIRTUAL — not stored)
ALTER TABLE orders ADD (
  line_total AS (quantity * unit_price)
);

-- PostgreSQL (STORED — the only option)
ALTER TABLE orders
  ADD COLUMN line_total numeric
  GENERATED ALWAYS AS (quantity * unit_price) STORED;

Behaviour is otherwise the same: you can’t write to it directly, it updates when its inputs change, and it can be queried and indexed like any column. The only cost is the bytes on disk.

The Smarter Move: If It Only Existed to Be Indexed

Here’s the migration insight most straight ports miss. A large share of Oracle virtual columns exist for exactly one reason: to put an index on a computed expression (case-insensitive search, a date truncation, a JSON path). If nothing selects the column directly, you don’t need the column at all in PostgreSQL — you need an expression index, which stores nothing extra:

-- Oracle: a virtual column purely to index a lowercased email
ALTER TABLE users ADD (email_lc AS (LOWER(email)));
CREATE INDEX ix_users_email_lc ON users (email_lc);

-- PostgreSQL: skip the column entirely — index the expression
CREATE INDEX ix_users_email_lc ON users (LOWER(email));
-- queries just use the expression directly:
--   SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Same query performance, zero added storage, one fewer object to maintain. Reach for a STORED generated column only when the application actually reads the computed value.

The IMMUTABLE Requirement

PostgreSQL requires a generated column’s expression to be IMMUTABLE — the same inputs must always produce the same output. That rules out anything time- or session-dependent. An Oracle virtual column like age AS (SYSDATE - birth_date) won’t port to a generated column, because now() isn’t immutable.

• Time-based expressions (now(), CURRENT_DATE) → compute in a view, or in the query, not in a stored column.
• A user-defined function in the expression must itself be declared IMMUTABLE.
• No subqueries and no reference to other tables — the expression sees only the current row.

Restrictions That Bite Silently

A few PostgreSQL limits differ from Oracle and tend to surface late, so plan for them during assessment rather than at cutover:

• No partition key. Oracle lets you partition on a virtual column; a PostgreSQL generated column cannot be part of a partition key. Partition on the underlying expression or restructure.
• No chaining. A generated column can’t reference another generated column. Flatten the dependency into one expression.
• No default, no direct writes. You can’t give it a DEFAULT, and INSERT/UPDATE can only assign DEFAULT to it.

None of these are showstoppers — but each is a place a virtual column looks like it ported cleanly and then fails on the first partition definition or dependent expression. Catch them in the assessment and they become one-line notes instead of cutover surprises.