1.1.1

Create a new table, providing new names for the columns but using the same data types as
the columns of an existing table:
CREATE TABLE T3 (A,B,C,D,E) AS SELECT * FROM T1 WITH NO DATA;
Create a new table, providing new names for the columns but using the data types from
specific columns of an existing table:
CREATE TABLE T3 (A,B,C) AS SELECT V,DP,I FROM T1 WITH NO DATA;
This example shows that the columns in the result of the query expression may be unnamed
expressions, but their data types can still be used to provide the data types for the
corresponding named columns in the newly-created table:
CREATE TABLE T3 (X,Y) AS SELECT 2*I,2.0*F FROM T1 WITH NO
DATA;
Column Definition
The column definition defines the name of a column and its data-type.
Syntax
column-name data-type
[
Column ConstraintSyntax on page 495
]*
[ [ WITH ] DEFAULT { constant-expression | NULL }
| [ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH integer-constant [, INCREMENT BY integer-constant
] ) ] ] ]
[
Column ConstraintSyntax on page 495
]*
The set of supported data-types are detailed in Data Types on page 624. Identity columns must be of type BIGINT
or INTEGER.
Identity Columns
SQLFire supports both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns only for
BIGINT and INTEGER data types. The START WITH and INCREMENT BY clauses are supported only for
GENERATED BY DEFAULT identity columns. Creating an identity column does not create an index on the
column.
GENERATED ALWAYS Identity Columns
For a GENERATED ALWAYS identity column, SQLFire increments the default value on every insertion, and
stores the incremented value in the column. You cannot insert a value directly into a GENERATED ALWAYS
identity column, and you cannot update a value in a GENERATED ALWAYS identity column. Instead, you
must either specify the DEFAULT keyword when inserting data into the table or you must leave the identity
column out of the insertion column list.
Consider a table with the following column definition:
create table greetings (i int generated always as identity, ch char(50));
493
SQL Language Reference