Skip to main content

CREATE TABLE

Creates a table.

Syntax​

CREATE TABLE [IF NOT EXISTS] 
  <table_name> ( 
  [ { <column_name> <data_type> [<column_constraint> [ ... ] ] [ ENCODING ( <storage_directive> [, ...] ) ]
    | <table_constraint>
    | LIKE <source_table> [ <like_option> ... ] 
    | COLUMN <column_name> ENCODING ( <storage_directive> [, ...] ) [, ...] }
    [, ... ]
] )
[ WITH ( <storage_parameter> [=<value>] [, ... ] ) ]
[ DISTRIBUTED BY (<column>)
    | DISTRIBUTED RANDOMLY
    | DISTRIBUTED REPLICATED]
[ CLUSTER BY ([
    { <column_name> | ( <expression> ) } 
        [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
])]
[ TTL [HOT | <ttl_expression> WARM] [<delete_expression> DELETE]]


Where <column_constraint> is:
  [ CONSTRAINT <constraint_name>]
  { NOT NULL 
    | NULL 
    | CHECK  ( <expression> ) 
    | DEFAULT <default_expr> }

Where <storage_directive> is:
{ compresstype | compresslevel | blocksize }

Where <table_constraint> is:
  [ CONSTRAINT <constraint_name> ]
  [ CHECK ( <expression> ) ]


Where <like_option> is:
  { INCLUDING | EXCLUDING }
  { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | STORAGE | ALL }

Description​

CREATE TABLE creates an initially empty table in the current database. The user who issues the command owns the table.

If you specify a schema name, Relyt creates the table in the specified schema. Otherwise Relyt creates the table in the current schema.

CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.

The optional constraint clauses specify conditions 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.

You can define two types of constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.

Parameters​

  • <table_name>

    The name of the table. You can prefix the table name with a schema name to create the table in the specified schema. Otherwise, the table will be created in the current schema.

  • IF NOT EXISTS

    If you include IF NOT EXISTS in your command, no error will be reported when an existing table with the same name exists. A notice will be issued instead.

  • <column_name>

    The name of the column to be created in the table.

  • <data_type>

    The data type of the column. This can include array specifiers.

  • ENCODING ( <storage_directive> [, ...] )

    For a column, the optional ENCODING clause specifies the type of compression and block size for the column data. Supported values for <storage_directives> are compresstype, compresslevel, and encode:

    • compresstype: the compression type, which can be LZ4, ZSTD, or Uncompressed. LZ4 is the default. The value Uncompressed indicates that data will not be compressed. ZSTD offers a balance of speed and a good compression ratio, which can be adjusted using the compresslevel option. ZSTD is superior to these compression types for typical workloads.

    • compresslevel: the compression level. If compresstype is LZ4, the value range is an integer from 1 (fastest compression) to 12 (highest compression ratio), with a default value of 1; if compresstype is ZSTD, the range is from 1 to 22, with a default value of 1.

    • encode: the encoding value, with a range of integers from -1 to 3, where:

      • -1: specifies to use automatic encoding.

        When encode = -1, the system will decide whether to use dictionary encoding based on the field type: for varchar/text types, the system will dynamically determine whether to use dictionary encoding based on data characteristics; for other types, dictionary encoding is disabled.

      • 0: specifies to use dictionary encoding.

      • 1: the default value, specifying that data is stored in its original state without encoding.

    Column compression settings are inherited from the table level. The lower-level settings have priority.

    This clause is valid only for Relyt tables.

  • LIKE [<source_table> <like_option> ...]

    The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, not-null constraints, and distribution policy.

    Note that storage properties like append-optimized or partition structure are not copied.

    The optional like_option clauses specify which additional properties of the original table to copy. Specifying INCLUDING copies the property, specifying EXCLUDING omits the property. EXCLUDING is the default. If multiple specifications are made for the same kind of object, the last one is used. The available options are:

    • INCLUDING COMMENTS: Comments for the copied columns, constraints, and indexes will be copied. The default behavior is to exclude comments, resulting in the copied columns and constraints in the new table having no comments.
    • INCLUDING CONSTRAINTS: CHECK constraints will be copied. No distinction is made between column constraints and table constraints. Not-null constraints are always copied to the new table.
    • INCLUDING DEFAULTS: Default expressions for the copied column definitions will be copied. Otherwise, default expressions are not copied, resulting in the copied columns in the new table having null defaults. Note that copying defaults that call database-modification functions, such as nextval, may create a functional linkage between the original and new tables.
    • INCLUDING GENERATED: Any generation expressions of copied column definitions will be copied. By default, new columns will be regular base columns.
    • INCLUDING STATISTICS: Extended statistics are copied to the new table.
    • INCLUDING STORAGE: STORAGE settings for the copied column definitions will be copied. The default behavior is to exclude STORAGE settings, resulting in the copied columns in the new table having type-specific default settings.
    • INCLUDING ALL: INCLUDING ALL is an abbreviated form of all available options (It may be useful to specify individual EXCLUDING clauses after INCLUDING ALL to select all but some specific options.)

    The LIKE clause can also be used to copy columns from views. Inapplicable options are ignored.

  • CONSTRAINT <constraint_name>

    An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages, so constraint names like column must be positive can be used to communicate helpful constraint information to client applications. Double-quotes are needed to specify constraint names that contain spaces. If a constraint name is not specified, the system generates a name.

  • NOT NULL

    Specifies that the column cannot contain null values.

  • NULL

    Specifies that the column can contain null values. This is the default.

    note

    This clause is only provided for compatibility with non-standard SQL databases. Its use is discouraged in new applications.

  • CHECK (<expression>)

    The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to true or unknown succeed. Should any row of an insert or update operation produce a false result, Relyt raises an error exception, and the insert or update does not alter the database. A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns.

    Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. You can reference the system column tableoid, but not any other system column.

    When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints.

  • DEFAULT <default_expr>

    The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. The data type of the default expression must match the data type of the column. The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

  • WITH <storage_parameter>=<value>

    The WITH clause specifies optional storage parameters for a table or index; see Storage Parameters below for details. For backward-compatibility the WITH clause for a table can also include OIDS=false to specify that rows of the new table should not contain OIDs (object identifiers), OIDS=true is no longer supported.

  • DISTRIBUTED BY (<column>), DISTRIBUTED RANDOMLY, or DISTRIBUTED REPLICATED

    Specifies the distribution policy.

    • The DISTRIBUTED BY <column> clause specifies a column as the distribution key. The data type of the column can be smallint, integer, bigint, text, or varchar.

    • DISTRIBUTED RANDOMLY: randomly sends data to shards.

    • DISTRIBUTED REPLICATED: replicates the entire table to all shards.

    If not specified, the system will randomly select a column from the table as the distribution key.

  • CLUSTER BY

    Declares one or more columns (up to 3) in the table as the cluster key. The cluster key must consist of at least one column or expression.

    Note that the data type of cluster key columns can only be smallint, integer, bigint, or date. To use columns of other data types as cluster keys, you must convert the columns to any supported data types.

    info

    If both CLUSTER BY and DISTRIBUTED BY are needed, specify DISTRIBUTED BY first.

  • ASC | DESC

    Specifies the option to sort rows in ascending or descending order. Supported values include:

    • ASC: ascending order

    • DESC: descending order

    If omitted, ASC is used by default.

  • NULLS { FIRST | LAST }

    Specifies the option for NULL values to be returned before or after non-NULL values. Supported values include:

    • NULLS FIRST: NULL values are returned before non-NULL values.

    • NULLS LAST: NULL values are returned after non-NULL values.

    If omitted, the default behavior changes with the sort order:

    • If the sort order is ASC, NULLs are returned before non-NULL values.

    • If the sort order is DESC, NULLs are returned after non-NULL values.

  • TTL

    Specifies the data lifecycle management policies. You can use the TTL clause to set tiered storage for hot, warm, and cold data, as well as deletion policy for data.

    • HOT | <ttl_expression> WARM

      Specifies whether the data is hot or warm.

      • HOT: specifies that the table is a hot table, with all data permanently residing in cache.

      • <ttl_expression> WARM: specifies that the table is a warm table, with data turning cold according to the policy specified by <ttl_expression>. Once turned cold, the data will be deleted from the cache. The column types referenced in the expression can only be date, varchar, and integer. Furthermore, if the referenced column type is not date/time-based, it needs to be converted to a datetime data type through functions.

        Usage Notes

        The WARM attribute can be set at table or column level. To set it at the row level, the following two conditions must be met at the same time:

        • The CLUSTER BY clause is used in the table definition statement, and the first column in the clause is of type date, varchar, or integer.

        • The column referenced in the TTL expression is the first column specified in the CLUSTER BY clause.

    • <delete_expression> DELETE

      The deletion policy for data. The deletion strategy can be effective at both table and row levels.

      Usage Notes

      To set the deletion policy at the row level, the following two conditions must be met at the same time:

      • The CLUSTER BY clause is used in the table creation statement, and the first column in the clause is of type date, varchar, or integer.

      • The column referenced in the deletion expression is the first column specified in the CLUSTER BY clause.

    For more information, see Use TTL to Manage Data.

Storage parameters​

Relyt supports the following storage parameters for tables:

  • filesize: the maximum storage size for a single table (in MB). The range is from 0 to 8192, with a default value of 192.

  • blocksize: the size of each block in each table (in MB). The range is from 0 to 1024, with a default value of 64.

  • compresstype: the compression type, which can be LZ4, ZSTD, or Uncompressed. LZ4 is the default. The value Uncompressed indicates that data will not be compressed. ZSTD offers a balance of speed and a good compression ratio, which can be adjusted using the compresslevel option. ZSTD is superior to these compression types for typical workloads.

  • compresslevel: the compression level. If compresstype is LZ4, the value range is an integer from 1 (fastest compression) to 12 (highest compression ratio), with a default value of 1; if compresstype is ZSTD, the range is from 1 to 22, with a default value of 1.

    • encode: the encoding value, with a range of integers from -1 to 3, where:

      • -1: specifies to use automatic encoding.

        When encode = -1, the system will decide whether to use dictionary encoding based on the field type: for varchar/text types, the system will dynamically determine whether to use dictionary encoding based on data characteristics; for other types, dictionary encoding is disabled.

      • 0: specifies to use dictionary encoding.

      • 1: the default value, specifying that data is stored in its original state without encoding.

Usage notes​

You cannot define a table with more than 1600 columns. (In practice, the effective limit is usually lower because of tuple-length constraints.)

A primary key constraint is simply a combination of a unique constraint and a not-null constraint.

Foreign key constraints are not supported in Relyt.

For inherited tables, unique constraints, primary key constraints, indexes, and table privileges are not inherited in the current implementation.

Examples​

Create a table named raps and set the cluster key to rank:

CREATE TABLE raps (
    musician text,
    style text,
    rank int
)
CLUSTER BY rank;

Create a table named test, and set the file size of 1024, block size to 100, compression type to lz4, and encoding value to 1:

CREATE TABLE test(
    a integer, 
    b integer
) 
USING test_1
OPTIONS(filesize= 1024, blocksize= 100, compresstype='lz4', compresslevel = 1, encode = 0);

SQL standard compatibility​

CREATE TABLE command conforms to the SQL standard, with the following exceptions:

Column check constraints​

The SQL standard says that CHECK column constraints may only refer to the column they apply to; only CHECK table constraints may refer to multiple columns. Relyt does not enforce this restriction; it treats column and table check constraints alike.

NULL constraint​

The NULL constraint is a Relyt extension to the SQL standard that is included for compatibility with some other database systems (and for symmetry with the NOT NULL constraint). Since it is the default for any column, its presence is not required.

Zero-column tables​

Relyt allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from the SQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so Relyt decided to ignore this spec restriction.

Multiple identity columns​

Relyt allows a table to have more than one identity column. The standard specifies that a table can have at most one identity column. This is relaxed mainly to give more flexibility for performing schema changes or migrations. Note that the INSERT command supports only one override clause that applies to the entire statement; multiple identity columns with different behaviors is not well supported.

LIKE clause​

While a LIKE clause exists in the SQL standard, many of the options that Relyt accepts for it are not in the standard, and some of the standard's options are not implemented by Relyt.

WITH clause​

The WITH clause is a v extension; neither storage parameters nor OIDs are in the standard.

Data distribution​

The Relyt concept of a parallel or distributed database is not part of the SQL standard. The DISTRIBUTED clauses are extensions.

Data lifecycle management​

The TTL clause is a Relyt extension to the SQL standard.