Summary

The CREATE TABLE command creates a new table within Connect’s ‘delta’ schema and any available custom schemas.

Syntax

create [unlogged] table [if not exists] 
<schema name>.<table name>
(<column name> <type>
[,primary key (column_name1 [, column_name2..n])]
[,foreign key (column_name1 [, column_name2..n]) 
references <schema name>.<table name> (column_name1 [, column_name2..n])]);

Parameters

<td<schema name><td”>(Optional) Connect supports both primary keys on a single column and composite primary keys.

Parameter	Description
[unlogged]	(Optional) Unlogged tables may improve the performance of INSERT, UPDATE, and DELETE operations run against the table. Xactly recommends using unlogged tables for transient or temporary data sets only. The risk of data loss in the event of system failure is present when using unlogged tables. By default, custom tables are logging enabled.
(Required) Connect allows users to create tables in the ‘delta’ schema and any custom schemas found in your environment. Developers may not create, alter, or drop tables in the ‘xactly’, ‘xdms’, and ‘staging’ schemas.
<table name>	(Required) A unique string descriptor for the table. Table names within a given schema must be unique.
[if not exists]	(Optional) Instructs Connect to create the table only if it is not already found in the system. The parameter can be useful in pipeline steps where you want to create a table only if it is not present.
<column name>	(Required) Column names within a table must be unique.
<type>	(Required) Connect supports the following column data types: boolean – values stored as ‘true’ and ‘false’, but xSQL statements will implicitly convert 1 to true and 0 to false. byte – 1-byte signed integer. Minimum allowed value is -128 and maximum value is 127. date – date values (year, month, and day). date_time – date and time (the date and time portion, with no offset or zone). Consider using the ‘instant’ data type, as it offers UTC support. decimal(p,s) – Decimal numeric with precision and scale parameters. Precision defines the total number of significant digits allowed (across both sides of the decimal). Scale defined the number of digits allowed to the right of the decimal. Parameters are optional. If precision and scale are not specified, then both unbounded and are assigned dynamically based on a given value. double – 8-byte floating point. float – 4-byte floating point. long – 8-byte signed integer. Minimum and maximum values are -2^63 and 2^63-1, respectively. instant – Instant, along the universal timeline (date and time portions in UTC). integer – 4-byte signed integer. Minimum and maximum values are -2^31 and 2^31-1, respectively. offset_date_time – Offset date and time (the date and time with offset). short – 2-byte signed integer. Minimum and maximum allowed values are -32,768 and 32,767, respectively. string(max_characters) or varchar(max_characters)– supports Unicode. The `max_characters` length parameter is optional. Strings can contain up to 4k characters, but beware that Incent application tables only permit much smaller string lengths. time – Time (only the time portion, with no offset or zone). zoned_date_time – Zoned date time (the date and time with a zone name).
[,primary key (column_name1 [, column_name2..n]]	(Optional) Connect supports both single column and composite primary key constraints.
[,foreign key (column_name1 [, column_name2..n]) references <schema name>.<table name> (column_name1 [, column_name2..n])]	(Optional) Connect supports foreign key constraints.

Details

xSQL tables are similar to table objects found in traditional relational databases such as Oracle, MySQL, and others. Tables store data in rows and columns, with data types for each column in a table are defined when a table is created.

After creating a table, you can manipulate data in the table using the INSERT, UPDATE, and DELETE statements and query the table using the SELECT statement.

Refer to INSERT to create a table based on the result set of a SELECT query.

Examples

create table delta.my_first_table
(col1 varchar(256),
col2 integer,
col3 instant,
col4 decimal);

create_table_01

create unlogged table if not exists delta.raw_sales_transactions
(order_number string primary key,
line_number integer,
invoice_date date,
order_amount decimal);

create_table_02

create table delta.primary_key_example
(col1 string(256),
col2 string(28),
col3 zoned_date_time,
col4 string(256),
primary key(col1));

create_table_03

create table delta.foreign_key_example
(column_1 string(256),
column_2 string(100),
column_3 decimal(12,2),
foreign key(column_1) references delta.primary_key_example(col1));

create_table_04