generation-clause

GENERATED ALWAYS AS ( value-expression )

A value-expression is an Expression that resolves to a single value, with some limitations that are described here. See SQL expressions for more information about Expressions.

References

The generation-clause may reference other non-generated columns in the table, but it must not reference any generated column. The generation-clause must not reference a column in another table.

Functions

The generation-clause may invoke user-coded functions, if the functions meet the following requirements:

The functions must not read or write SQL data.
The functions must have been declared DETERMINISTIC.
The functions must not invoke any of the following possibly non-deterministic system functions:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURRENT_USER
- CURRENT_ROLE
- CURRENT SCHEMA
- CURRENT SQLID
- SESSION_USER

Subqueries

The generation-clause must not include subqueries.

Foreign keys

If the generated column is part of a foreign key that references another table, the referential action must not specify SET NULL or SET DEFAULT, and the update rule must not specify ON UPDATE CASCADE.

Example

CREATE TABLE employee
(
  employeeID           int,
  name                 varchar( 50 ),
  caseInsensitiveName  GENERATED ALWAYS AS( UPPER( name ) )
);
CREATE INDEX caseInsensitiveEmployeeName ON employee( caseInsensitiveName );