Inserting IDENTITY Values from the SQL CLI

You can insert values into IDENTITY fields, regardless of whether you specified it as GENERATED ALWAYS OR GENERATED BY DEFAULT, using DDL statements and API calls.

Each IDENTITY field you create uses one of these syntax choices:
  • GENERATED ALWAYS AS IDENTITY
  • GENERATED BY DEFAULT AS IDENTITY
  • GENERATED BY DEFAULT ON NULL AS IDENTITY
How you create an IDENTITY field affects what happens when you INSERT values. You cannot change the IDENTITY value of a column that is a primary key.
As an example, you create the following table with an IDENTITY field as GENERATED ALWAYS. The IDENTITY field is not a primary key:

sql-> CREATE Table Test_SGSqlInsert2(
  id INTEGER,
  name STRING,
  deptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1),
  PRIMARY KEY(id));
Statement completed successfully 
To successfully insert values into this table, always specify DEFAULT as the value of the deptID IDENTITY field, so that the SG generates the next value.

sql->
> INSERT INTO Test_SGSqlInsert2 VALUES (148, 'sally', DEFAULT);
> INSERT INTO Test_SGSqlInsert2 VALUES (250, 'joe', DEFAULT);
> INSERT INTO Test_SGSqlInsert2 VALUES (346, 'dave', DEFAULT);
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
The preceding INSERT statements add the following rows, with the SG getting the next available IDENTITY value each time you specify DEFAULT.

sql-> select * from Test_SGSqlInsert2;
{"id":148,"name":"sally","deptId":1}
{"id":250,"name":"joe","deptId":2}
{"id":346,"name":"dave","deptId":3}
3 rows returned 
To get the value of the generated deptId for future reference in one statement, use the returning deptId clause as follows:
 
INSERT INTO Test_SGSqlInsert2 VALUES (600, 'jabba', DEFAULT) returning deptId;
{"deptId":6} 
INSERT INTO Test_SGSqlInsert2 VALUES (700, 'bubba', DEFAULT) returning deptId;
{"deptId":7}
Using the following INSERT statement, indicating a value rather than DEFAULT, causes an exception. You cannot specify any value for any IDENTITY field you define as GENERATED BY DEFAULT AS IDENTITY:
 
sql-> INSERT INTO Test_SGSqlInsert2 VALUES (1, 'joe', 200) ;
Error handling command INSERT INTO Test_SGSqlInsert2 VALUES (1, 'joe', 200): 
Error: at (1, 48) Generated always identity column must use DEFAULT construct. 
As another example, create a table with a DeptID integer field, GENERATED BY DEFAULT AS IDENTITY, and make it the primary and shard key:

sql-> CREATE TABLE Test_SGSqlInsert_Default (
>  ID INTEGER,
>  NAME STRING,
>  DeptID INTEGER GENERATED BY DEFAULT AS IDENTITY (
>  START WITH 1
>  INCREMENT BY 1
>  MAXVALUE 100),
>  PRIMARY KEY (SHARD(DeptID), ID));
Statement completed successfully
The following statements show how to insert values into table Test_SGSqlInsert_Default. In this case, since the column ID is not an IDENTITY, you can assign integer values to the field:

sql->
> INSERT INTO Test_SGSqlInsert_Default VALUES (100, 'tim', DEFAULT);
> INSERT INTO Test_SGSqlInsert_Default VALUES (200, 'dave', 210);
> INSERT INTO Test_SGSqlInsert_Default VALUES (300, 'sam', 310);
> INSERT INTO Test_SGSqlInsert_Default VALUES (400, 'Jennifer', DEFAULT);
> INSERT INTO Test_SGSqlInsert_Default VALUES (500, 'Barbara', 2);
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
These sample statements insert the following rows into the database.

sql-> select * from Test_SGSqlInsert_Default;
{"ID":300,"NAME":"sam","DeptID":310}
{"ID":100,"NAME":"tim","DeptID":1}
{"ID":400,"NAME":"Jennifer","DeptID":2}
{"ID":500,"NAME":"Barbara","DeptID":2}
{"ID":200,"NAME":"dave","DeptID":210}
5 rows returned
 
Since you specified two values as DEFAULT in your INSERT statements, the SG supplies them, as 1 and 2. The other values are inserted as you specify (210, 310, and 2). Each value is acceptable, even though one results in two DeptID values the same (2 supplied from a DEFAULT, and 2 as a value you supply).
Because you defined the IDENTITY column as GENERATED BY DEFAULT AS IDENTITY, the SG supplies a value only when you do not specify a value. Specifying values 210, 310, or 2 is correct. The system neither checks for duplicates, nor enforces uniqueness for GENERATED BY DEFAULT AS IDENTITY column values. It is the application’s responsibility to ensure that there are no duplicate values if that is a requirement.