Can I use… generated always as (…) (generated columns)

1. Supports storage options
2. Only without data type: col_name integer generated always as …
3. Only without keywords generated always
4. Limited constraint support
5. Requires explicit data type: col_name integer generated always as …
6. Requires storage option stored
7. Data type not honored if specified

SQL supports generated columns, sometimes called computed columns or virtual columns, whose value is automatically derived from other values of the same table row.0

The following example defines a table with two base columns (i.e., non-generated columns): net_price and vat_rate. Further it defines a generated column vat_amount that calculates the VAT amount.

CREATE TABLE … (
 net_price  NUMERIC(15,2),
 vat_rate   NUMERIC( 5,2),
 vat_amount NUMERIC(15,2) GENERATED ALWAYS AS
                          (net_price * vat_rate / 100.0)
)

Note that standard SQL requires parenthesis around the generation expression and only allows deterministic expressions (exceptions).

A table can have any number of computed columns but in standard SQL they cannot see each other.1 Some dialects don’t enforce this limitation as long as there is no cyclic dependency among them (see “Chaining Generated Columns”). In such dialects the following column could be added to this table.

ALTER TABLE …
 gross_price GENERATED ALWAYS AS (net_price + vat_amount)

Note that this example omits the data type so that the column gets the type of the generation expression’s result. In this particular example, this might be sufficient as the correct precision and scale can be derived from the expression. This is, however, not true for the vat_amount column defined above: the division can lead to a result that has more fractional digits as amounts in that currency should have.2

The functionality as shown so far could also be achieved by creating a view that provides these extra columns. You would not even need to worry about the limitations regarding chaining expressions or determinism. What makes generated columns special is that they are table columns and can thus be used wherever table columns can be used. That is, most importantly, in constraints and indexes (see Use The Index, Luke!).

Constraints

1. Only with storage option stored
2. Only with storage option persisted
3. Only without explicit data type: col_name integer generated always as …
4. Requires explicit data type: col_name integer generated always as …
5. As table constraint, not inside the column definition
6. Foreign keys in the column definition are parsed but ignored • Only with storage option stored
7. With unique constraint on generated column

Storage Options: Virtual or Physical

In practice, the above shown syntax might cause the generated columns to be physically stored like base columns – or it might not.3 The DBMS could also spare that space and just calculate the value when it is queried. As a generation expression has to be deterministic, in principle, it doesn’t matter when it is evaluated.

As both approaches—physical and virtual generated columns—have pros and cons it is quite common that a system offers both variants and a syntax extension to choose one. As this is not covered by the SQL standard, the respective keywords vary among the dialects.

… GENERATED ALWAYS AS (…) [ persisted | persistent | stored
                          | virtual ]

1. Accepted in a few cases, but suspected to be a parser oddity — use with caution!

Chaining Generated Columns

In standard SQL a generation expression must not refer to a generated column. But as there is no compelling reason to generally prohibit it, it is allowed in some systems as long as the chain does not close into a circle.

Valid Expressions

Another not-always-enforced restriction of standard SQL is that generation expressions must be deterministic. That means that functions like current_timestamp or even more striking random() are not allowed. Obviously, the use of non-deterministic expressions leads to non-deterministic results. But in the case of generated columns it might even lead to unpredictable results when it is not fully known how the system copes with them.

1. Only if it is neither directly nor indirectly (via a constraint or index) persisted

Syntax Variants

Finally, there are some syntax variants that don’t change the behavior of generated columns but affect their portability. To be explicit: Standard SQL strictly requires the parenthesis around the generation expression as well as the keywords generated always.

Writing to Generated Columns

The whole point of generated columns is that their value is automatically derived from other data. Therefore, it is not allowed to set their value in insert, update or merge statements. This can be accomplished by not mentioning them at all or by using the keyword default as the corresponding value expression.

Related

• Hidden/Invisible columns

• generated [always|by default] as identity (identity column)

• generated always as row [start|end] (system-versioned tables)

• default (column default): Similar, but can be overwritten with insert, update and merge.

• Function-based Indexing

Normative References

Generated columns are defined in ISO/IEC 9075:2016-2 as optional feature T175.