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


Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite200520072009201120132015201720192021⚠ 3.31.0 - 3.42.0adg⊘ 3.5.7 - 3.30.0⚠ 2008R2 - 2022bcd⚠ 12 - 15ef⊘ 8.4 - 11✓ 11gR1+a⚠ 5.7 - 8.0.33ade⊘ 5.0 - 5.6⚠ 5.2 - 10.11ade⊘ 5.1⚠ 2.0.202 - 2.1.214c⚠ 1.4.200cd⚠ 1.4.192 - 1.4.198cd⚠ 1.4.191ce✓ 9.7+⊘ 2.0⚠ 10.15.1.3 - 10.16.1.1d
  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

Apache DerbydBigQueryDb2 (LUW)H2MariaDBegMySQLaffOracle DBPostgreSQLSQL ServerbSQLiteccgPrimary keyUniqueForeign key to another tableForeign key from another table
  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 ]

Note

The following chart only shows if the syntax is accepted. The underlying tests do not check if the generated column is stored or not.

Apache DerbyBigQueryDb2 (LUW)aaaaH2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (…)           … as (…) persisted … as (…) persistent… as (…) stored    … as (…) 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.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (… gen_col …)

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.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBaMySQLOracle DBPostgreSQLSQL ServeraSQLitedeterministicnon-deterministic
  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.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (<expr>)… as  <expr> … generated always as ……                  as …

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.

  • 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.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. Or constants, of course.

  2. SQL-2:2016, §11.4 SR 10 c

  3. This example neglects some accounting requirements.

  4. 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.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license