col_name integer generated always as …
generated always
col_name integer generated always as …
stored
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!).
stored
persisted
col_name integer generated always as …
col_name integer generated always as …
stored
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 ]
The following chart only shows if the syntax is accepted. The underlying tests do not check if the generated column is stored or not.
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.
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.
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
.
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.
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
.
Generated columns are defined in ISO/IEC 9075-2:2023 as optional feature T175.
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »
Or constants, of course.
ISO/IEC 9075-2:2023 §11.4 SR 10c
This example neglects some accounting requirements.
The standard describes the functionality of generated columns by generating their result at the time of the data modification—i.e. during insert
, update
or merge
. Strictly speaking that implies that the standard requires the values to be stored (as it becomes SQL-data). SQL:2003 Has Been Published also describes the functionality in this way.
In the field, however, it is not generally true that all systems implement generated columns by storing the generated values. Personally, I think, this is still in the vein of the term effectively as used by the SQL standard (Part 1, “Rule evaluation order”) even though the wording doesn’t cover this case.