generationClause

GENERATED ALWAYS AS ( valueExpression )

A valueExpression 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 generationClause may reference other non-generated columns in the table, but it must not reference any generated column. The generationClause must not reference a column in another table.

Functions

The generationClause 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 generationClause 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 );