generated-column-spec

A generated column is one whose value is defined by an expression, typically involving values from other columns in the same table. The value of a generated column is automatically updated whenever there’s a change in the value of any column upon which the expression depends.

[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH IntegerConstant
[ ,INCREMENT BY IntegerConstant] ) ]  ]  ]

{ALWAYS | BY DEFAULT} AS IDENTITY

A table can have at most one identity column. See the Identity Column Attributes section below for more information about identity columns.Splice Machine supports two kinds of identity columns:

GENERATED ALWAYS

An identity column that is GENERATED ALWAYS will increment the default value on every insertion and will store the incremented value into the column. Unlike other defaults, you cannot insert a value directly into or update an identity column that is GENERATED ALWAYS. Instead, either specify the DEFAULT keyword when inserting into the identity column, or leave the identity column out of the insertion column list altogether. For example:

create table greetings
  (i int generated always as identity, ch char(50));
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bonjour');

Automatically generated values in a GENERATED ALWAYS identity column are unique. Creating an identity column does not create an index on the column.

GENERATED BY DEFAULT

An identity column that is GENERATED BY DEFAULT will only increment and use the default value on insertions when no explicit value is given. Unlike GENERATED ALWAYS columns, you can specify a particular value in an insertion statement to be used instead of the generated default value.

To use the generated default, either specify the DEFAULT keyword when inserting into the identity column, or just leave the identity column out of the insertion column list. To specify a value, included it in the insertion statement. For example:

create table greetings
(i int generated by default as identity, ch char(50));
       -- specify value "1":
insert into greetings values (1, 'hi');
       -- use generated default
insert into greetings values (DEFAULT, 'salut');
       -- use generated default
insert into greetings(ch) values ('bonjour');

Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee uniqueness. Thus, in the above example, the hi and salut rows will both have an identity value of “1”, because the generated column starts at 1 and the user-specified value was also 1. You can prevent duplication by specifying a START WITH value, and using a primary key or unique constraint on the identity column

START WITH IntegerConstant

The first identity value that Splice Machine should assign.

INCREMENT BY IntegerConstant

The amount by which to increment the identity value each time one is assigned.

Identity Column Attributes

A table can have at most one identity column.

For SMALLINT, INT, and BIGINT columns with identity attributes, Splice Machine automatically assigns increasing integer values to the column. Identity column attributes behave like other defaults in that when an insert statement does not specify a value for the column, Splice Machine automatically provides the value. However, the value is not a constant; Splice Machine automatically increments the default value at insertion time.

The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types.

By default, the initial value of an identity column is 1, and the amount of the increment is 1. You can specify any positive integer value for both the initial value and the interval amount when you define the column with the key words START WITH and INCREMENT BY. Splice Machine increments the value with each insert. A value of 0 raises a statement exception.

The maximum and minimum values allowed in identity columns are determined by the data type of the column. Attempting to insert a value outside the range of values supported by the data type raises an exception. The following table shows the supported ranges.

Data Type Maximum Value Minimum Value
SMALLINT 32767 (java.lang.Short.MAX_VALUE) -32768 (java.lang.Short.MIN_VALUE)
INT 2147483647 (java.lang.Integer.MAX_VALUE) -2147483648 (java.lang.Integer.MIN_VALUE)
BIGINT 9223372036854775807 (java.lang.Long.MAX_VALUE) -9223372036854775808 (java.lang.Long.MIN_VALUE)

Automatically generated values in an identity column are unique. Use a primary key or unique constraint on a column to guarantee uniqueness. Creating an identity column does not create an index on the column.

Specify the schema, table, and column name using the same case as those names are stored in the system tables–that is, all upper case unless you used delimited identifiers when creating those database objects.

Using Generated Columns

Splice Machine keeps track of the last increment value for a column in a cache. It also stores the value of what the next increment value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions can leave “gaps” in the values automatically inserted into an identity column. Splice Machine behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high.

When an insert happens within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement into the identity column is available from ConnectionInfo only within the trigger code. The trigger code is also able to see the value inserted by the statement that caused the trigger to fire. However, the statement that caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement into the identity column. Likewise, triggers can be nested (or recursive).

An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL statement that causes trigger T2 to fire. If both T1 and T2 insert rows into a table that cause Splice Machine to insert into an identity column, trigger T1 cannot see the value caused by T2’s insert, but T2 can see the value caused by T1’s insert. Each nesting level can see increment values generated by itself and previous nesting levels, all the way to the top-level SQL statement that initiated the recursive triggers. You can only have 16 levels of trigger recursion.

Examples

create table greetings
  (i int generated by default
    as identity (START WITH 2, INCREMENT BY 1),
  ch char(50));
 -- specify value "1":
insert into greetings values (1, 'hi');
 -- use generated default
insert into greetings values (DEFAULT, 'salut');
 -- use generated default
insert into greetings(ch) values ('bonjour);
drop table if exists words;

splice> CREATE TABLE WORDS(WORD VARCHAR(20), UWORD GENERATED ALWAYS AS (UPPER(WORD)));
0 rows inserted/updated/deleted
splice> CREATE INDEX IDX_UWORD ON WORDS(UWORD);
0 rows inserted/updated/deleted
splice> INSERT INTO WORDS(WORD) VALUES 'chocolate', 'Coca-Cola', 'hamburger', 'carrot';
4 rows inserted/updated/deleted
splice> select * from words;
WORD                |UWORD
--------------------------------------------------------------------------------
chocolate           |CHOCOLATE
Coca-Cola           |COCA-COLA
hamburger           |HAMBURGER
carrot              |CARROT

4 rows selected
splice> select upper(word) from words;
1
--------------------------------------------------------------------------------
CHOCOLATE
COCA-COLA
HAMBURGER
CARROT

4 rows selected
splice> drop table if exists t;
0 rows inserted/updated/deleted
WARNING 42Y55: 'DROP TABLE' cannot be performed on 'T' because it does not exist.
splice> CREATE TABLE T(COL1 INT, COL2 INT, COL3 GENERATED ALWAYS AS (COL1+COL2));
0 rows inserted/updated/deleted
splice> INSERT INTO T (COL1, COL2) VALUES (1,2), (3,4), (5,6);
3 rows inserted/updated/deleted
splice> select * from t;
COL1       |COL2       |COL3
--------------------------------------------------------------------------------
1          |2          |3
3          |4          |7
5          |6          |11

3 rows selected
splice> UPDATE T SET COL2 = 100 WHERE COL1 = 1;
1 row inserted/updated/deleted
splice> select * from t;
COL1       |COL2       |COL3
--------------------------------------------------------------------------------
1          |100        |101
3          |4          |7
5          |6          |11

3 rows selected