3.3

Generated ColumnsΒΆ

It is possible to define columns whose value is computed by applying a generation expression in the context of the current row. The generation expression can reference the values of other columns.

Generated columns are defined by providing a generation expression. Providing a data type is optional. It is inferred by the return type of the supplied expression if omitted:

cr> CREATE TABLE computed (
...   dividend double,
...   divisor double,
...   quotient GENERATED ALWAYS AS (dividend / divisor)
... );
CREATE OK, 1 row affected (... sec)

See also

For a full syntax description, see CREATE TABLE.

Generated columns are read-only. Their values are computed as needed for every INSERT and UPDATE operation.

For example:

cr> INSERT INTO computed (dividend, divisor) VALUES (1.7, 1.5), (0.0, 10.0);
INSERT OK, 2 rows affected (... sec)

The generated column is now filled with the computed value:

cr> SELECT dividend, divisor, quotient
... FROM computed
... ORDER BY quotient;
+----------+---------+--------------------+
| dividend | divisor |           quotient |
+----------+---------+--------------------+
|      0.0 |    10.0 | 0.0                |
|      1.7 |     1.5 | 1.1333333333333333 |
+----------+---------+--------------------+
SELECT 2 rows in set (... sec)

The generation expression is evaluated in the context of the current row. This means that you can compute a generated value from the values of base columns in the same row. However, it is not possible to reference other generated columns from within a generation expression.

Note

If the generated expression is deterministic, its value will not be recomputed unless the value of a referenced column has changed.

If values are supplied for generated columns, these values are validated against the result of applying the generation expression:

cr> INSERT INTO computed (dividend, divisor, quotient) VALUES (100.0, 2.0, 12.0);
SQLActionException[SQLParseException: Given value 12.0 for generated column does not match defined generated expression value 50.0]

Warning

Supplied values for generated columns are not validated when they are imported using COPY FROM.

Because CURRENT_TIMESTAMP is non-deterministic, you can use this expression to record a last modified date that is set when the row is first inserted, and subsequently updated every time the row is updated:

cr> CREATE TABLE computed_non_deterministic (
...   id LONG,
...   last_modified TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP
... )
CREATE OK, 1 row affected (... sec)

They can also be used in the PARTITIONED BY in order to compute the value to partition by from existing columns in the table:

cr> CREATE TABLE computed_and_partitioned (
...   huge_cardinality long,
...   big_data string,
...   partition_value GENERATED ALWAYS AS (huge_cardinality % 10)
... ) PARTITIONED BY (partition_value);
CREATE OK, 1 row affected (... sec)