Use the following syntax to define a column for local tables.
Syntax
column_nameDatatype [(precision[,scale])]
[{DEFAULT default_value | GENERATED BY DEFAULT AS IDENTITY
(START WITH n[, INCREMENT BY m])}] | [[NOT] NULL]
[IDENTITY] [PRIMARY KEY]
where:
column_name
is the name to be assigned to the column.
Datatype
is the data type of the column to be created. See the "Data Types" topic for a list of supported data types.
precision
is the number characters for CHAR and VARCHAR columns, the number of bytes for BINARY and VARBINARY columns, and the total number of digits for DECIMAL columns.
scale
is the number of digits to the right of the decimal point for DECIMAL columns and the number of seconds for DATETIME columns.
default_value
is the default value to be assigned to the column. The following default values are allowed in column definitions for local tables:
For character columns, a single-quoted string or NULL. The only SQL function that can be used is CURRENT_USER.
For datetime columns, a single-quoted Date, Time, or Timestamp value or NULL. You can also use the following datetime SQL functions: CURRENT_DATE, CURRENT_TIME, CURRENT_ TIMESTAMP, TODAY, or NOW.
For boolean columns, the literals FALSE, TRUE, NULL.
For numeric columns, any valid number or NULL.
For binary columns, any valid hexadecimal string or NULL.
IDENTITY | GENERATED BY DEFAULT AS IDENTITY
defines an auto-increment column. Either clause can be specified only on INTEGER or BIGINT columns. Identity columns are considered primary key columns, so a table can have only one Identity column.
The GENERATED BY DEFAULT AS IDENTITY clause is the standard SQL syntax for specifying an Identity column.
The IDENTITY operator is equivalent to GENERATED BY DEFAULT AS IDENTITY without the optional START WITH clause.
START WITH n[, INCREMENT BY m]
specifies the sequence of numbers generated for the Identity column. n and m are the starting and incrementing values, respectively, for an Identity column. The default start value is 0 and the default increment value is 1.
Example A
Assuming the current schema is PUBLIC, a local table is created. id is an identity column with a starting value of 0 and an increment value of 1 because no Start With and Increment By clauses are specified.
CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(30))
This example is equivalent to the previous example.
CREATE TABLE Test (id INTEGER IDENTITY, name VARCHAR(30))
Example B
Assuming the current schema is PUBLIC, a local table is created. id is an identity column with a starting value of 2 and an increment of 2.
CREATE TABLE Test (id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 2, INCREMENT BY 2), name VARCHAR(30))
For character columns, a single-quoted string or NULL. The only SQL function that can be used is CURRENT_USER.