Neoview SQL Reference Manual (R2.4)

ALTER TABLE t2 ALTER COLUMN a SET INCREMENT BY 1;
INSERT INTO t2 VALUES(DEFAULT,4);
SELECT * FROM t2;
A B
----- ------
0 1
1 2
2 3
3 4
The recalibration of the internal sequence generator current value is now complete. Unique values
will be generated for the IDENTITY column.
Considerations for CREATE TABLE AS
The CREATE TABLE AS statement is supported for the GENERATED BY DEFAULT AS
IDENTITY column. The values for the GENERATED BY DEFAULT AS IDENTITY can be
user-supplied or system-generated. You cannot use the CREATE TABLE AS statement with the
GENERATED ALWAYS AS IDENTITY column.
In this GENERATED ALWAYS BY DEFAULT AS IDENTITY example, table t2 is created starting
with 15 in the IDENTITY column, as defined in the CREATE TABLE statement.
CREATE TABLE t1 (a LARGEINT GENERATED ALWAYS AS IDENTITY
(MINVALUE 10
MAXVALUE 99999
START WITH 15)
NOT NULL,
b INT UNSIGNED NOT NULL,
PRIMARY KEY(a));
SELECT * FROM t1;
A B
15 1
INSERT INTO t1 VALUES(DEFAULT,1);
CREATE TABLE t2 (a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
b INT, PRIMARY KEY(a)) AS SELECT a,b FROM t1; --A full select
--list is required
SELECT * FROM t2;
A B
15 1
The CREATE TABLE AS SELECT statement, using a full select list from the source table, provides
the values for the GENERATED ALWAYS AS IDENTITY column as user-supplied values. The
target table will contain the same values for the IDENTITY column as were selected from the
source table. As user-supplied values are provided for the IDENTITY column, the internal
sequence generator associated with table t2 has not been updated with these values. A subsequent
insert into table t2 using the internal sequence generator will generate values as specified by
the internal sequence generator options.
In this example, the START WITH option is not defined, so the internal sequence generator starts
with a default value of '0'. When an insert is performed, the internal sequence generator will
generate values to be used as the IDENTITY column value.
INSERT INTO t2 VALUES(DEFAULT,2);
SELECT * FROM t2;
82 SQL Statements