Extend the CREATE TABLE statement added to the "server" module in
CALCITE-707 with generated columns (both VIRTUAL and STORED), CHECK constraints, and DEFAULT clause.
- There are two kinds of generated columns: virtual and stored; we refer to the latter as "stored generated columns".
- Stored generated columns are calculated from an expression when a row is inserted into the table and stored in that table.
- Virtual columns are calculated from an expression when a row is read from a table.
- The DEFAULT clause provides a value when the column is not mentioned in the INSERT.
- Like columns with a DEFAULT, generated columns have an associated expression; but unlike columns with a DEFAULT, generated columns may not be specified in an INSERT statement (with a small exception involving the DEFAULT expression, described below).
- A CHECK constraint checks that a given condition is not false before inserting a row.
- When query involving a table with a stored generated column is planned, the planner sees a constraint as if there were a CHECK constraint on that column.
Comparison to other databases. Some databases confusingly use "virtual" as an umbrella term for both stored and non-stored:
- MySQL's "generated columns" may be declared either "stored" or "virtual", using the STORED and VIRTUAL keywords;
- MariaDB's "virtual (computed)" columns may be tagged VIRTUAL, PERSISTENT, STORED;
- MSSQL's "computed columns" may be virtual or persisted, with the PERSISTED keyword;
- Oracle only has "virtual columns", and the VIRTUAL keyword;
- DB2 has "generated columns" which are always stored, and neither VIRTUAL nor STORED keyword.
If neither VIRTUAL nor STORED is specified, VIRTUAL is the default.
You can insert into a generated column only if the expression is DEFAULT:
We allow GENERATED ALWAYS before AS, and CONSTRAINT name before CHECK.
A CHECK constraint is satisfied if it evaluates to TRUE or UNKNOWN. Thus the following statement would insert one row:
To prevent NULL values, in other words to force o = i + 4 to always be TRUE, write the constraint as follows:
Not part of this proposal:
- CHECK as part of a column definition;
- Invisible columns. Oracle has these, and they solve the problem that INSERT INTO t SELECT * FROM u fails if t has any generated columns;
- A variant of the DEFAULT clause that provides values when the incoming value is NULL.