CREATE TABLE

CREATE TABLE — create a new table.

Syntax

Create a table:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name data_type [ column_constraints ] [ , ... ]
    [, table_constraints ]
 ) [ AUTORECOMPUTE { ON | OFF } ]

Create a temporary table:

CREATE [ LOCAL ] { TEMPORARY | TEMP } TABLE [ IF NOT EXISTS ] table_name
 ( column_name data_type [ column_constraints ] [, ... ]
    [, table_constraints ]
 ) [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
MERGE TREE indexes cannot be created on temporary tables.

Create a table with partitioning:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name data_type [ column_constraints ] [ , ... ]
    [, table_constraints ]
 )
 {
  STORE IN storage_group |
    { PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
        ( [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
          [ STORE IN storage_group ] ]...)
    } |
    { PARTITION BY LIST (column_name) [ STORE IN storage_group ]
        ( [ PARTITION partition_name VALUES IN ( {literal [,literal]...} | DEFAULT )
          [ STORE IN storage_group ] ]...)
    }
 } [ AUTORECOMPUTE { ON | OFF } ]

Where column_constraints can be one or more of the following:

  NOT NULL | NULL
  CHECK ( expression )
  DEFAULT default_expr
{ COLLATE | COLLATION } collation_name
  PRIMARY KEY
  REFERENCES reference_table [ ( reference_column ) ]
  UNIQUE
  generated_column

and generated_column can be one of the following:

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [(generator_name)]
GENERATED ALWAYS AS (expression) PERSISTED

Where table_constraints is:

[ CONSTRAINT [ constraint_name ] ] table_constraint [ , table_constraint ]...

and table_constraint is:

CHECK ( boolean_expression )
PRIMARY KEY ( column_name [, ...] ) [ index_qualifiers ]
FOREIGN KEY ( column_name [, ...] ) REFERENCES reference_table [ ( reference_column [, ... ] ) ]
UNIQUE ( column_name [, ...] [ WITH ( [ RESOLUTION n, [ MAX KEY SIZE n ], [ NULLS [NOT] DISTINCT ] ) ]
KEY indexName ( column_name [, ...] | expression [,...] ) [ index_qualifiers ]
UNIQUE KEY indexName ( column_name[, ...]) [ index_qualifiers [ WITH ( [ RESOLUTION n, [ MAX KEY SIZE n ], [ NULLS [NOT] DISTINCT ] ) ]

Description

CREATE TABLE creates a new, initially empty table in the current database. The table will be owned by the user issuing the command.

If a schema_name is specified (for example, CREATE TABLE myschema.mytable …​) then the table is created in the specified schema. Otherwise, the table is created in the current schema, assigned by the USE SCHEMA command. Tables cannot be created in the SYSTEM schema. This schema is reserved for database metadata. Temporary tables exist in a special schema, so a schema_name cannot be specified when creating a temporary table.

The name of a persistent table must be distinct from the name of any other table, sequence, index or view in the current or specified schema. Temporary tables can be created using the same name as an existing persistent table. However, the persistent table is not visible to the current session while the temporary table exists, unless they are referenced with their schema-qualified names. This is due to the fact that any reference to that table by name, without schema name, references the temporary table.

The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways.

There are two ways to define constraints: column constraints and table constraints. A column constraint is defined as part of a column definition and only affects that one column. A table constraint definition is not tied to a particular column, and it can encompass more than one column.

A table cannot have more than 32000 columns. (In practice, the effective limit is lower because of record-length constraints).

The CREATE TABLE command may also create indexes. See also, section reference-information/sql-language/sql-statements/create-index.adoc#_running_concurrent_index_builds on the CREATE INDEX page that describes behavior when many index builds run concurrently.

Parameters

IF NOT EXISTS

Does not throw an error if a table with the same name already exists. Note that there is no guarantee that the existing table is anything like the one that would have been created.

schema_name

Name of the schema in which the table will be created. The default is the current schema as defined by the USE command. If schema_name is provided, and the schema does not exist, it will be created.

You cannot specify schema_name for a TEMPORARY table.

table_name

Name of the table to be created.

column_name

Name of a column to be created in the new table.

data_type

The data type of the column. The data type specifies the length and scale factor for the column. For more information, see SQL Data Types.

storage_group

The name of a storage group. When using the storage_group parameter with a STORE IN clause, a default value is provided and stored in the PARTITIONEDTABLES table. This default value may be used thereafter by ALTER TABLE…​ADD PARTITION statements. For information about creating a storage group, see Managing Storage Groups. Also see Table Partitioning.

partition_name

The name of a partition. Partition names must be unique. See also Table Partitioning.

value

This is used in the partitioning syntax for VALUES LESS THAN, literal and VALUES IN (literal [,literal]…​). See Table Partitioning.

DEFAULT

The keyword DEFAULT is used by the syntax VALUES IN (DEFAULT) to specify a default partition for all data that does not meet previous VALUES IN partitioning criterion. See Table Partitioning.

MAXVALUE

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See Table Partitioning.

Temporary Tables

MERGE TREE indexes cannot be created on temporary tables.
TEMPORARY | TEMP

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or, optionally, at the end of the current transaction (see ON COMMIT below). Any indexes created on a temporary table are automatically temporary as well. Multiple connections to a database can create temporary tables with the same name. Optionally, LOCAL can be written before TEMPORARY or TEMP, as all temporary tables are, by default, LOCAL.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

No special action is taken at the ends of transactions. This is the default behavior.

DELETE ROWS

All rows in the temporary table will be deleted at the end of each transaction.

DROP

The temporary table will be dropped at the end of the current transaction.

Generated Columns

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [(generator_name)]

On INSERT, the value for a generated column is generated based on a generator sequence. If generator_name was not specified, a sequence is created implicitly (as if by executing a CREATE SEQUENCE statement) with an implicitly generated name. An error is returned if generator_name was specified but a sequence with this name does not exist when the column is created. If generator_name was not specifed when the column was created, the implicitly generated sequence will be dropped when the column is dropped. A table can have at most one identity column.

The difference between ALWAYS and BY DEFAULT is as follows:

  • GENERATED ALWAYS AS IDENTITY - Values for this column are automatically generated and cannot be manually inserted/updated.

  • GENERATED BY DEFAULT AS IDENTITY - When inserting a new record, a value will be generated for this column if one is not provided. Values in these columns can be manually updated. NuoDB will set the column equal to the next value of the sequence.

You can use GENERATED ALWAYS AS IDENTITY to improve performance for inserts and updates. When you specify this clause the database itself is responsible for generating unique values for the column. This makes index operations against the column complete more quickly. In other words, strictly from the performance point of view, it is typically faster to perform inserts against a table whose primary key is GENERATED ALWAYS AS IDENTITY than one whose keys are provided by the client.

See Example 8.

Computed Columns

A computed column is a column whose value is computed based on a given expression. The expression can be any expression that will generate deterministic results.

GENERATED ALWAYS AS (expression) PERSISTED

Columns are stored in the table as normal columns. It is not possible to give custom values or to update the values in computed columns, but the column will update if the result of the expression changes.

See Example 9.

Limitations of Computed Columns

The following limitations apply when using computed columns:

  • A computed column cannot be created where:

    • The expression references another table.

    • The expression contains a subquery.

    • The expression is not deterministic:

      • Only deterministic native functions may be used. A function is deemed to be deterministic if it always returns the same value when given the same arguments. For example RAND and NOW cannot be used in computed column definitions since their return value does not depend on the value of the arguments. Date and time functions and operators for which the result depends on the user provided session time zone are also forbidden.

      • All user-defined functions must be declared as DETERMINISTIC.

    • The expression uses aggregate/window functions.

    • The expression does not reference any table columns.

    • It is not possible to determine the precision or scale of the result of an expression, for example an arithmetic operation on a STRING column, without an explicit CAST function.

  • User-defined functions can be used in computed columns, but NuoDB does not track this dependency. The user-defined function can be changed or dropped. If changed, the result is that the data in the table is invalid and the column in the table will need to be dropped and re-created manually. The result of dropping the user-defined function is that INSERT and UPDATE operations on the target table fail with an error message indicating that the user-defined function cannot be found.

Constraints

NOT NULL

The column is not allowed to contain NULL values.

NULL

The column is allowed to contain NULL values. This is the default.

DEFAULT default_expr

The DEFAULT clause assigns a default value for the column. The value expression, default_expr, cannot include a variable, sub-select, or cross-references to other columns in the current table. The default_expr can be a scalar user defined function. This type of function returns a single value.

The data type of the default_expr must be compatible with the data type of the column. For example, the default_expr for a numeric value must evaluate to a number, and the default_expr for a timestamp must evaluate to a timestamp.

Data type checking on the DEFAULT value expression is enforced at compile time for CREATE TABLE and ALTER TABLE.

The default_expr is assigned as the column value in any INSERT operation that does not assign a value for the column.

CHECK (expression)

CHECK clauses specify integrity constraints or tests that must be satisfied by new or updated rows to allow an insert or update operation to succeed. CHECK constraints are satisfied if they evaluate to a value convertible to TRUE or to NULL. Each constraint must be an expression producing a boolean result. A condition appearing within a column definition should reference that column’s value only, while a condition appearing as a table constraint may reference multiple columns. CHECK expressions cannot contain sub-selects nor refer to variables other than columns of the current row.

CHECK table constraints can be optionally qualified with the keyword CONSTRAINT followed by a name. When a table constraint is named, the error message produced when the constraint is not met will include the name of the constraint, for example, violation of constraint "\constraint_name`"`.

See Example 6.

{COLLATE|COLLATION} {DEFAULT|case_insensitive|"8859-1U"} collation_name

This clause allows defining a collation for the column. See Example 5. Supported collation types include: DEFAULT, case_insensitive and "8859-1U". Both case_insensitive and "8859-1U" collation types are synonyms for each other. Therefore, COLLATE case_insensitive will produce the same results as COLLATE "8859-1U". The DEFAULT collation is case sensitive.

PRIMARY KEY(column_name[,…​])

The PRIMARY KEY constraint specifies that a column or group of columns of the new table will be part of the table’s primary key. The role of a PRIMARY KEY is to provide a column or group of columns which can be used to uniquely identify any row in the table. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL.

Only one PRIMARY KEY can be created for a table, whether as a column constraint or a table constraint.

The PRIMARY KEY constraint should name a set of columns that is different from other sets of columns named by any unique constraint, such as a unique index, defined for the same table.

A PRIMARY KEY table constraint can be optionally qualified by both the keyword CONSTRAINT along with a constraint_name. In this case, the primary key index is named constraint_name in SYSTEM.INDEXES (see INDEXES System Table Description).

index_qualifiers

One or more modifiers for an index used in the KEY, UNIQUE KEY, and PRIMARY KEY table constraints, where index_qualifiers is:

( index_qualifier [ , index_qualifier ]…​ )

and index_qualifier is one of:

RESOLUTION n

Set the resolution for the index histogram. For more information, see CREATE INDEX.

MAX KEY SIZE literal

The default maximum key size is 1024 bytes. NuoDB recommends that index key size be less than or equal to the default maximum because having larger index keys might decrease performance. However, if your application requires a larger maximum index key size then you can change this using ALTER TABLE. There is no system property for setting a maximum index key size. You can increase the maximum index key size only when you create a particular index. To change the maximum size allowed for an index key, specify WITH(MAX KEY SIZE n) and replace n with a value from 100 bytes to 3072 bytes. NuoDB throws an exception if you try to specify a maximum key size that is less than 100 bytes or more than 3072 bytes.

FOREIGN KEY ( column_name [, …​] ) REFERENCES reference_table [ ( reference_column [, …​ ] ) ]

The FOREIGN KEY constraint defines a relationship between one or more columns in the new table, named as column_name, with referencing column(s), named as reference_column, in the referenced table, named as reference_table. There are rules for specifying this reference:

  • The reference_column must be referencing either columns in a primary key or a unique constraint that is defined on the reference_table.

  • All columns in the primary key or unique constraint of the reference_table must be referenced in the reference_column column list. However, they do not have to appear in the same order as they appear in the primary key or unique constraint on the reference_table.

  • The number of provided column_name must be equal to the number of provided reference_column or the number of columns in the primary key if "reference_column" is not provided.

  • If no reference_column is specified, the default is to reference the PRIMARY KEY column(s) defined for the table specified as the reference_table.

    This FOREIGN KEY constraint, by default, will prevent the table specified by reference_table from being dropped, as long as it remains referenced as a FOREIGN KEY constraint on this new table, or any other table.

    A FOREIGN KEY table constraint can be created with or without specifying a constraint_name. If a constraint_name is specified, it must be qualified by the keyword CONSTRAINT, which is not required if no constraint_name is specified. If no constraint_name is specified, a unique constraint_name is generated (see FOREIGNKEYS System Table Description).

    The FOREIGN KEY constraint_name is stored in the FOREIGNKEYNAME column of the SYSTEM.FOREIGNKEYS table. This is the name that must be referred to if the foreign key constraint is to be dropped using the ALTER TABLE command.

    NuoDB supports the syntax required to define FOREIGN KEY constraints but does not enforce any kind of referential integrity. Only a semantic check is available that prevents the dropping of a table referenced from another table. If required, referential integrity must be maintained by the application.
UNIQUE

The UNIQUE constraint specifies that a column or group of columns of the new table can contain only unique (non-duplicate) values. Columns defined in a UNIQUE constraint do not have to be defined as NOT NULL (NULLS [NOT] DISTINCT. For more information on NULLS [NOT] DISTINCT, see CREATE INDEX).

Creating a UNIQUE constraint will create a UNIQUE INDEX on the same column(s) defined by the UNIQUE constraint.

A UNIQUE table constraint can be optionally qualified by the keyword CONSTRAINT and a constraint_name. You may also specify the resolution of the histogram, the maximum length of an index key, and the treatment of NULL values.

KEY indexName ( column_name[, …​] | expression[,…​] ) [ WITH (RESOLUTION n ) ]

To add a named index to a table at the same time that you create the table, specify the KEY table constraint. Specify a name for the index and the column(s) to index. You may also specify the resolution of the histogram.

A KEY table constraint can be optionally qualified by the keyword CONSTRAINT and a constraint_name. In this case, the index is named constraint_name and the indexName after the keyword KEY is ignored.

For information on index creation, see CREATE INDEX.

Table Partitioning

CREATE TABLE supports an optional PARTITION BY clause which allows control over where data in the table is stored. Without table partitioning, a row in a table is stored on every Storage Manager (SM). This is a very powerful tool for increasing performance by reducing disk and network traffic, especially in a Region distributed application. The notion is to keep data local to the client accessing it. CREATE TABLE supports two formats of PARTITION BY: BY RANGE and BY LIST.

In addition to associating a table with multiple storage groups, a table can be created and assigned to just one storage group by specifying the syntax STORE IN storage_group. In this case, we establish a partition for the table and all the data in the table is put into that partition regardless of any other associations established for that table.

Partition by Range

PARTITION BY RANGE specifies that each row is mapped to a partition based on the first VALUES LESS THAN subclause that evaluates to true, where VALUES LESS THAN clauses are sorted from lowest to highest.

PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
    ( [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
      [ STORE IN storage_group ]... )

The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES LESS THAN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES LESS THAN clause, may specify a storage group to use for the given partition_name. The MAXVALUE keyword specifies a partition for values not evaluating to true for any of the VALUES LESS THAN clauses. If MAXVALUE is not specified, then values not evaluating to true for any of the VALUES LESS THAN clauses would cause an error. A partition_name may not be used in more than one VALUES LESS THAN clause. A literal may not be used in more than one VALUES LESS THAN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY RANGE table is done in the following order:

  1. If an optional storage group is specified after the VALUES LESS THAN clause, then that storage group is used.

  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY RANGE clause, use that storage group.

  3. If the partition name specified by PARTITION partition_name was previously associated with a storage group, then use that storage group.

  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB. If the table has a PRIMARY KEY and/or UNIQUE index, the partitioning column must be added as a column for the index to ensure uniqueness across multiple partitions. The exception to this is a partitioning column that is defined as GENERATED ALWAYS AS IDENTITY. This column would not be required to be added to the PRIMARY KEY and/or UNIQUE index.

For more information, see Table Partitions and Storage Groups.

Partition by List

PARTITION BY LIST specifies that each row is mapped to a partition based on the first VALUES IN clause that contains the value stored in the partitioning column specified by column_name.

PARTITION BY LIST (column_name) [STORE IN storage_group ]
    ( [ PARTITION partition_name VALUES IN ( { literal [,literal]... } | DEFAULT )
      [ STORE IN storage_group ] ]... )

Like PARTITION BY RANGE, the first optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES IN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES IN clause, may specify a storage group to use for the given partition_name. If a value in the partitioning column specified by column_name does not match any of the VALUES IN lists, then an exception is thrown. The DEFAULT keyword specifies a partition for values not evaluating to true for any of the previous VALUES IN clauses. If DEFAULT is not specified, then values not evaluating to true for any of the VALUES IN clauses would cause an error. A partition_name may not be used in more than one VALUES IN clause. A literal may not be used in more than one VALUES IN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY LIST table is done in the following order:

  1. If an optional storage group is specified after the VALUES IN clause, then that storage group is used.

  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY LIST clause, use that storage group.

  3. If the partition specified by PARTITION partition_name was previously mapped to a storage group, then use that storage group.

  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB.

For more information, see Table Partitions and Storage Groups.

Partition by Expression

Range or List partitioning can also use a computed column as the partitioning column. An expression or deterministic function can be used to decide how the data is partitioned. See Example 10.

Table Statistics

AUTORECOMPUTE {ON|OFF}

The automatic creation of table statistics can be switched ON or OFF. The default is ON.

For more information, see How auto-update works.

Examples

Example 1: Create a table using column constraints.
CREATE TABLE hockey_fans
(id        BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 name      STRING      NOT NULL,
 address   STRING      NULL,
 city      STRING      NOT NULL,
 zip_code  STRING      NOT NULL,
 teamid    VARCHAR(3)  DEFAULT 'BOS',
 gender    CHAR(1)     CHECK (gender IN ('M','F') ),
 phone     STRING
);

SHOW TABLE hockey_fans;

    Tables named HOCKEY_FANS

    Found table HOCKEY_FANS in schema HOCKEY

        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            ZIP_CODE string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: gender IN ('M','F')
            PHONE string
        Primary Index: HOCKEY_FANS..PRIMARY_KEY on field: ID
Example 2: Create a local, temporary table.
SHOW TABLES
    No tables found in schema USER

CREATE LOCAL TEMP TABLE local_table (Col1 STRING,
                                     Col2 INTEGER);

SHOW TABLES
    Tables in schema USER
        LOCAL_TABLE (temporary)
Example 3: Create a temporary table that will delete rows on commit.
CREATE TEMPORARY TABLE tmp_table (col1 INTEGER, col2 STRING) ON COMMIT DELETE ROWS;

START TRANSACTION;
INSERT INTO tmp_table VALUES (1, 'string1');
SELECT * FROM tmp_table;
 COL1   COL2
 ----- -------
   1   string1
COMMIT;
SELECT * FROM tmp_table;
 COL1   COL2
 ----- -------
<< no records >>
Example 4: Defining the collation type on a single column.
DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table WHERE column1 = 'abc';
COLUMN1
--------
   abc

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd


DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12) COLLATE "8859-1U");
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table WHERE column1 = 'abc';
 COLUMN1
 --------
   abc
   ABC

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD
Example 5: Create a table using a function as a default column value.
SET DELIMITER @
CREATE FUNCTION fnc_getdatabase()
  RETURNS STRING
AS
    RETURN (SELECT 'Database: '||database() FROM DUAL);
END_FUNCTION;
@
SET DELIMITER ;

CREATE TABLE test_table
  (column1 STRING,
   database_name  STRING NOT NULL DEFAULT fnc_getdatabase());

INSERT INTO test_table VALUES ('row1',DEFAULT);
INSERT INTO test_table (column1) VALUES ('row2');
INSERT INTO test_table VALUES ('row3','Database: TEST2');
SELECT * FROM test_table;
 COLUMN1   DATABASE_NAME
 -------- ---------------
   row1   Database: test
   row2   Database: test
   row3   Database: TEST2
Example 6: Use both a named and unnamed CHECK constraint.

Create a table called t1, with one column constraint and two table constraints.

CREATE TABLE t1 (
    f1 INT CHECK (f1 > 10),
    CONSTRAINT even CHECK (f1 % 2 = 0),
    CONSTRAINT CHECK (f1 < 100) );

In this scenario, we are setting a column constraint on f1 that checks that the value inserted is greater than 10. We also set one named table constraint, even, that checks if f1 mod 2 equals 0 and one unnamed table constraint that checks if f1 is less than 100. Violations of these constraints would be reported as follows:

INSERT INTO t1 VALUES (5);
violation of constraint "F1"``
INSERT INTO t1 VALUES (6);``
violation of constraint "F1"``
INSERT INTO t1 VALUES (11);``
violation of constraint "EVEN"``
INSERT INTO t1 VALUES (100);``
violation of constraint "T1$constraint0"
Example 7: Use table partitioning.

Create a table called test_table1, with table partitioning by range on column x and a partition p1 and a storage group sg1.

CREATE TABLE test_table1 (x INTEGER, y STRING) PARTITION BY RANGE (x)
    (PARTITION p1 VALUES LESS THAN (10) STORE IN sg1);

Create a table called test_table2, with table partitioning by list on column x and a storage group sg2.

CREATE TABLE test_table2 (x INTEGER, y STRING) PARTITION BY LIST (x)
    (PARTITION p2 VALUES IN (1,3) STORE IN sg2);

Create a table called test_table3, with multiple table partitioning clauses. In this example partition p1 and partition p2 are assigned to the DEFAULT storage group, whereas partition p3 is assigned to the storage group sg3.

CREATE TABLE test_table3 (x INTEGER, y STRING)
    PARTITION BY RANGE (x) STORE IN DEFAULT
        ( PARTITION p1 VALUES LESS THAN (10)
          PARTITION p2 VALUES LESS THAN (20)
          PARTITION p3 VALUES LESS THAN (MAXVALUE) STORE IN sg3);

For an end-to-end example of creating storage groups and mapping table partitions to them, see Example of Using Storage Groups and Table Partitions.

Example 8: Create a generated column.
CREATE SEQUENCE auto_seq_1;
CREATE TABLE test_seq_tab1 (column1 INTEGER GENERATED ALWAYS AS IDENTITY (auto_seq_1),
                            column2 STRING);
Example 9: Create a computed column.
create table t1 (f1 int, f2 int generated always as (f1 + 10) persisted);
insert into t1 values (1),(2),(3);
select * from t1;

 F1  F2
 --- ---
  1  11
  2  12
  3  13
Example 10: Partition by expression.

A simple example of how a computed column can be used to distribute table records across four partitions.

create table partitioned_table (f1 int, f2 int generated always as (f1 % 4) persisted)
        partition by list (f2)
        partition p1 values in (0) store in stg1
        partition p2 values in (1) store in stg2
        partition p3 values in (2) store in stg3
        partition p4 values in (3) store in stg4;
insert into partitioned_table values (1),(2),(3),(4),(5),(6),(7),(8);

select * from partitioned_table order by f1;

 F1  F2
 --- ---
  1   1
  2   2
  3   3
  4   0
  5   1
  6   2
  7   3
  8   0
Example 11: Disable automatic statistics collection on table.
create table t1 (f1 int) autorecompute off;
create table t2 (f1 int) autorecompute on;
select tablename, norecompute from system.tables where tablename in ('T1', 'T2');

 TABLENAME  NORECOMPUTE
 ---------- ------------

     T1        TRUE
     T2        FALSE