Define a new table.
CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP } | UNLOGGED] TABLE [IF NOT EXISTS]
table_name (
{ column_name data_type [ COLLATE collation ] [column_constraint [ ... ] ]
[ ENCODING ( storage_directive [, ...] ) ]
| table_constraint
| LIKE source_table [ like_option ... ] }
| [ column_reference_storage_directive [, ...]
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ USING { MARS2 | MARS3} ]
[ WITH ( storage_parameter [=value] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTED BY (column [opclass], [ ... ] )
| DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED ]
{ --partitioned table using SUBPARTITION TEMPLATE
[ PARTITION BY partition_type (column)
{ [ SUBPARTITION BY partition_type (column1)
SUBPARTITION TEMPLATE ( template_spec ) ]
[ SUBPARTITION BY partition_type (column2)
SUBPARTITION TEMPLATE ( template_spec ) ]
[...] }
( partition_spec ) ]
} |
{ -- partitioned table without SUBPARTITION TEMPLATE
[ PARTITION BY partition_type (column)
[ SUBPARTITION BY partition_type (column1) ]
[ SUBPARTITION BY partition_type (column2) ]
[...]
( partition_spec
[ ( subpartition_spec_column1
[ ( subpartition_spec_column2
[...] ) ] ) ],
[ partition_spec
[ ( subpartition_spec_column1
[ ( subpartition_spec_column2
[...] ) ] ) ], ]
[...]
) ]
}
CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} | UNLOGGED ] TABLE [IF NOT EXISTS]
table_name
OF type_name [ (
{ column_name WITH OPTIONS [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ WITH ( storage_parameter [=value] [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]where column_constraint is:
[ CONSTRAINT constraint_name]
{ NOT NULL
| NULL
| CHECK ( expression ) [ NO INHERIT ]
| DEFAULT default_expr
| UNIQUE index_parameters
| PRIMARY KEY index_parameters
| REFERENCES reftable [ ( refcolumn ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE key_action ] [ ON UPDATE key_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ]
| UNIQUE ( column_name [, ... ] ) index_parameters
| PRIMARY KEY ( column_name [, ... ] ) index_parameters
| FOREIGN KEY ( column_name [, ... ] )
REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE key_action ] [ ON UPDATE key_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]like_option is:
{INCLUDING|EXCLUDING} {DEFAULTS|CONSTRAINTS|INDEXES|STORAGE|COMMENTS|ALL}The index_parameters constraints in UNIQUE and PRIMARY KEY are:
[ WITH ( storage_parameter [=value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]The storage_directive of the column is:
compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE|MXCUSTOM}
[compresslevel={0-9}]
[blocksize={8192-2097152} ]
// When compressiontype=MXCUSTOM, you need to specify the specific encodechain
[encodechain={LZ4|ZSTD|DELTADELTA(缩放位数)|DELTAZIGZAG|GORILLA|GORILLA2|FLOATINT|FDS|AUTO}]The storage_parameter of the table is:
appendoptimized={TRUE|FALSE}
blocksize={8192-2097152}
orientation={COLUMN|ROW}
checksum={TRUE|FALSE}
// The following are the relevant parameters of the encoding chain
compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE|MXCUSTOM}
// When compressiontype=MXCUSTOM, you need to specify the specific encodechain
encodechain={LZ4|ZSTD|DELTADELTA(缩放位数)|DELTAZIGZAG|GORILLA|GORILLA2|FLOATINT|FDS|AUTO}
/* When compresstype=MXCUSTOM and encodechain=AUTO, adaptive encoding mode can be specified at the table level:
* automode=1 means compression rate is preferred, automode=2 means speed is preferred
*/
automode={1|2}
compresslevel={0-9}
fillfactor={10-100}
[oids=FALSE]
// The following are the parameters related to the MARS2 table
// Used to control all MARS2 tables, how many Runs can be triggered by L0 (v4.5.0 starts to support)
sortheap_automerge_threshold={10-2048}
// Used to control how many Runs are reached in MARS2 single table L0 to trigger merges (v4.5.0 is supported)
level0_merge_threshold={1-2048}
// Control the size of the MARS2 single table L0 -> L1 upgrade. When L0 merges, the result will be upgraded to L1 if it exceeds this size.
*(v4.5.0 is supported)
*/
level0_upgrade_size={1-10000}
/* Control the size of the MARS2 single table L1 -> L2 upgrade. When the result of the L1 merge exceeds this size, it will be upgraded to L2.
*(v4.5.0 is supported)
*/
level1_upgrade_size={1-10000}
// Controls the sorted memory size of MARS2 forms individual inserts, and if the insert target table is a partitioned table, they will share this size (v4.5.0 starts to support)
sortheap_sort_mem={128-2147483647}
// Controls at least how much sorted memory is allocated for a single MARS2 partition table (v4.5.0 is supported)
sortheap_sort_mem_core={128-2147483647}
/* Compression threshold. Used to control how many tuples in each column of the MARS2 single table are compressed at one time.
* It is the upper limit of the number of compressed Tuples in the same unit (v5.0.0 starts to support)
*/
compress_threshold={1-100000}
// The following are the parameters related to the MARS3 table (all supported starting from v5.1.0)
/* rowstore_size is used to control when L0 Run switches in MARS3 tables. When the data size exceeds this value (in MB), it will switch to the next Run;
* compress_threshold is same as MARS2; prefer_load_mode is used to specify the loading mode of data in MARS3. normal means normal mode,
* The data will be stored in the disk according to the row, and then gradually converted into column storage as more data is written. bluk represents batch loading mode, and the data will be converted into column storage and then dropped into disk.
* When the amount of data inserted in a batch exceeds rowstore_size, it will be converted into multiple columns in multiple times;
* level_size_amplifier The enlargement coefficient used to specify the Level size
*/
compress_threshold={1-100000},mars3options='rowstore_size={8-1024},prefer_load_mode={NORMAL|BLUK},level_size_amplifier={1-1000}'
// The following are the parameters related to the MARS3 table downgrade storage function (all supported starting from v5.2.0)
/* ttl_interval Used to specify the threshold for data transition from hot to cold. The unit must be specified manually, such as “2 H,” which means that data from two hours ago is defined as cold data and will be automatically downgraded;
* ttl_interval currently supports d (days)/H (hours) units;
* ttl_space referers to the corresponding tablespace, and cannot be created if it does not exist;
* ttl_interval and ttl_space both need to be declared when creating tables or set with ALTER statements on existing tables.
*/
ttl_interval={1-INT_MAX},ttl_space={}key_action is:
ON DELETE
| ON UPDATE
| NO ACTION
| RESTRICT
| CASCADE
| SET NULL
| SET DEFAULTpartition_type is:
LIST | RANGEpartition_specification is:
partition_element [, ...]partition_element is:
DEFAULT PARTITION
name
| [PARTITION name] VALUES (list_value [,...] )
| [PARTITION name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ([datatype] [number |INTERVAL] 'interval_value') ]
| [PARTITION name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ([datatype] [number |INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ column_reference_storage_directive [, ...] ]
[ TABLESPACE tablespace ]Where subpartition_spec or template_spec is:
subpartition_element [, ...]Subpartition_element is:
DEFAULT SUBPARTITION name
| [SUBPARTITION name] VALUES (list_value [,...] )
| [SUBPARTITION name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ([datatype] [number |INTERVAL] 'interval_value') ]
| [SUBPARTITION name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ([datatype] [number |INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[ column_reference_storage_directive [, ...] ]
[ TABLESPACE tablespace ]The partition_storage_parameter of the partition is:
appendoptimized={TRUE|FALSE}
blocksize={8192-2097152}
orientation={COLUMN|ROW}
checksum={TRUE|FALSE}
// The following are the relevant parameters of the encoding chain
compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE|MXCUSTOM}
// When compressiontype=MXCUSTOM, you need to specify the specific encodechain
encodechain={LZ4|ZSTD|DELTADELTA(缩放位数)|DELTAZIGZAG|GORILLA|GORILLA2|FLOATINT|FDS|AUTO}
/* 当 compresstype=MXCUSTOM，且 encodechain=AUTO 时，支持在表级别指定自适应编码模式：
* automode=1 means compression rate is preferred, automode=2 means speed is preferred
*/
automode={1|2}
compresslevel={0-9}
fillfactor={10-100}
[oids=FALSE]
// The following are the parameters related to the MARS2 table
// Used to control all MARS2 tables, how many Runs can be triggered by L0 (v4.5.0 starts to support)
sortheap_automerge_threshold={10-2048}
// Used to control how many Runs are reached in MARS2 single table L0 to trigger merges (v4.5.0 is supported)
level0_merge_threshold={1-2048}
// Control the size of the MARS2 single table L0 -> L1 upgrade. When L0 merges, the result will be upgraded to L1 if it exceeds this size.
*(v4.5.0 is supported)
*/
level0_upgrade_size={1-10000}
/* Control the size of the MARS2 single table L1 -> L2 upgrade. When the result of the L1 merge exceeds this size, it will be upgraded to L2.
*(v4.5.0 is supported)
*/
level1_upgrade_size={1-10000}
// Controls the sorted memory size of MARS2 forms individual inserts, and if the insert target table is a partitioned table, they will share this size (v4.5.0 starts to support)
sortheap_sort_mem={128-2147483647}
// Controls at least how much sorted memory is allocated for a single MARS2 partition table (v4.5.0 is supported)
sortheap_sort_mem_core={128-2147483647}
/* Compression threshold. Used to control how many tuples in each column of the MARS2 single table are compressed at one time.
* It is the upper limit of the number of compressed Tuples in the same unit (v5.0.0 starts to support)
*/
compress_threshold={1-100000}
// The following are the parameters related to the MARS3 table (all supported starting from v5.1.0)
/* rowstore_size Used to control when L0 Run in the MARS3 table switches. When the data size exceeds this value (in MB), it will switch to the next Run;
* compress_threshold is same as MARS2; prefer_load_mode is used to specify the loading mode of data in MARS3. normal means normal mode,
* The data will be stored in the disk according to the row, and then gradually converted into column storage as more data is written. bluk represents batch loading mode, and the data will be converted into column storage and then dropped into disk.
* When the amount of data inserted in a batch exceeds rowstore_size, it will be converted into multiple columns in multiple times;
* level_size_amplifier The enlargement coefficient used to specify the Level size
*/
compress_threshold={1-100000},mars3options='rowstore_size={8-1024},prefer_load_mode={NORMAL|BLUK},level_size_amplifier={1-1000}'
// The following are the parameters related to the MARS3 table downgrade storage function (all supported starting from v5.2.0)
/* ttl_interval Used to specify the threshold for data transition from hot to cold. The unit must be specified manually, such as “2 H,” which means that data from two hours ago is defined as cold data and will be automatically downgraded；
* ttl_interval currently supports d (days)/H (hours) units;
* ttl_space referers to the corresponding tablespace, and cannot be created if it does not exist;
* ttl_interval and ttl_space both need to be declared when creating tables or set with ALTER statements on existing tables.
*/
ttl_interval={1-INT_MAX},ttl_space={}Notes！
For detailed information on the parameters of the MARS2 (v4.5.0 and later) and MARS3 (v5.1.0 and later) storage engines, please click here.
CREATE TABLE creates an initially empty table in the current database. The user who executes the command owns the table.
To be able to create a table, you must have USAGE privilege on all column types or the types in the OF clause.
If a schema name is specified, YMatrix will create the table in the specified schema. Otherwise, YMatrix will create the table in the current schema. Temporary tables exist in a special schema, so you cannot specify a schema name when creating a temporary table. The table name must be different from the names of any other tables, external tables, sequences, indexes, views, or external tables in the same schema.
CREATE TABLE also automatically creates a data type that represents the composite type corresponding to a row in the table. Therefore, the table cannot have the same name as any existing data type in the same schema.
Optional constraint clauses specify the conditions that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that can help define the valid value set in a table in various ways. Constraints apply to tables, not partitions. You cannot add constraints to partitions or subpartitions.
Referential integrity constraints (foreign keys) are accepted but not enforced. This information is retained in the system catalog and otherwise ignored.
There are two ways to define constraints: table constraints and column constraints. Column constraints are defined as part of the column definition. Table constraints are not bound to specific columns and can include multiple columns. Each column constraint can also be written as a table constraint. When a constraint affects only one column, using a column constraint is merely a matter of convenience.
When creating a table, there is an additional clause to declare the YMatrix database distribution strategy. If the DISTRIBUTED BY, DISTRIBUTED RANDOMLY, or DISTRIBUTED REPLICATED clauses are not provided, the YMatrix database will assign a hash distribution strategy to the table using the PRIMARY KEY (if the table has one) or the first column of the table as the distribution key. Columns of geometric or user-defined data types do not meet the requirements for YMatrix distribution key columns. If there are no columns of eligible data types in the table, rows will be distributed based on round-robin or random distribution. To ensure uniform distribution of data across the YMatrix database system, you should select a distribution key that is unique for each record, or if that is not possible, select DISTRIBUTED RANDOMLY.
If the DISTRIBUTED REPLICATED clause is provided, the YMatrix database will distribute all rows of the table to all Segments in the YMatrix database system. This option can be used if user-defined functions must be executed on Segments and these functions require access to all rows of the table. Replicated functions can also be used to prevent Broadcast Motions of the table, thereby improving query performance. The DISTRIBUTED REPLICATED clause cannot be used with the PARTITION BY clause or the INHERITS clause. A replicated table cannot be inherited by another table. Hidden system columns (ctid, cmin, cmax, xmin, xmax, and gp_segment_id) cannot be referenced in user queries on replicated tables because they do not have a single, unambiguous value.
Using the PARTITION BY clause, you can divide a table into multiple sub-tables (or partitions), which together form the parent table and share its schema. Although sub-tables exist as independent tables, the YMatrix database imposes important restrictions on their use. Internally, partitions are implemented as a special form of inheritance. Each subtable partition is created based on different CHECK constraints, which restrict the data that the table can contain according to certain defined conditions. The query optimizer also uses CHECK constraints to determine which table partitions to scan to satisfy the given query predicate. These partition constraints are automatically managed by the YMatrix database.
YMatrix v5.0.0 now supports specifying a custom encoding chain algorithm for compression when creating tables. For details, see Using Compression.
GLOBAL | LOCAL
TEMPORARY | TEMP
UNLOGGED
table_name
OF type_name
column_name
data_type
COLLATE collation
DEFAULT default_expr
ENCODING ( storage_directive [, ...] )
INHERITS ( parent_table [, …])
CONSTRAINT constraint_name
NULL | NOT NULL
UNIQUE ( column_constraint )
UNIQUE ( column_name [, ... ] ) ( table_constraint )
PRIMARY KEY ( column constraint )
PRIMARY KEY ( column_name [, ... ] ) ( table constraint )
REFERENCES reftable [ ( refcolumn ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ON DELETE | ON UPDATE] [key_action]
FOREIGN KEY (column_name [, ...])
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATEINITIALLY DEFERRED
If the constraint is delayable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default value. If the constraint is INITIALLY DEFERRED, checks are only performed at the end of the transaction. The constraint check time can be changed using the SET CONSTRAINTS command.WITH ( storage_parameter=value )
The WITH clause can specify storage parameters for tables and indexes associated with UNIQUE or PRIMARY constraints. Note that you can also set storage parameters on specific partitions or subpartitions by declaring the WITH clause in the partition specification. The lowest-level setting takes precedence.
The default values for certain table storage options can be specified using the server configuration parameter gp_default_storage_options.
The following storage options are available:
The encoding chain compression specified in the WITH clause is table-level.
ON COMMIT
TABLESPACE tablespace
USING INDEX TABLESPACE tablespace
DISTRIBUTED BY (column [opclass], [ ... ] )
DISTRIBUTED RANDOMLY
DISTRIBUTED REPLICATED
If the parameter value is set to on, the YMatrix database follows the following rules:
The DISTRIBUTED REPLICATED clause replicates the entire table to all YMatrix database Segment instances. It can be used when a function needs to access all rows in the table or when it is necessary to improve query performance by blocking Broadcast Motion. It can be used when executing user-defined functions on Segment.
PARTITION BY
SUBPARTITION BY
SUBPARTITION TEMPLATE
Create a table named rank in a schema called baby and distributed the data using the rank, gender, and year columns:
CREATE TABLE baby.rank (id int, rank int, year smallint,
gender char(1), count int ) DISTRIBUTED BY (rank, gender,
year);Create table files and table allocators (by default, the primary key will be used as the YMatrix distribution key):
CREATE TABLE films (
code char(5) CONSTRAINT firstkey PRIMARY KEY,
title varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
kind varchar(10),
len interval hour to minute
);
CREATE TABLE distributors (
did integer PRIMARY KEY DEFAULT nextval('serial'),
name varchar(40) NOT NULL CHECK (name <> '')
);Create a gzip compressed, appended table:
CREATE TABLE sales (txn_id int, qty int, date date)
WITH (appendoptimized=true, compresslevel=5)
DISTRIBUTED BY (txn_id);Create a simple, single-level partition table:
CREATE TABLE sales (id int, year int, qtr int, c_rank int, code char(1), region text)
DISTRIBUTED BY (id)
PARTITION BY LIST (code)
( PARTITION sales VALUES ('S'),
PARTITION returns VALUES ('R')
);Create a three-level partition table without using the SUBPARTITION TEMPLATE clause:
CREATE TABLE sales (id int, year int, qtr int, c_rank int, code char(1), region text)
DISTRIBUTED BY (id)
PARTITION BY LIST (code)
SUBPARTITION BY RANGE (c_rank)
SUBPARTITION by LIST (region)
( PARTITION sales VALUES ('S')
( SUBPARTITION cr1 START (1) END (2)
( SUBPARTITION ca VALUES ('CA') ),
SUBPARTITION cr2 START (3) END (4)
( SUBPARTITION ca VALUES ('CA') ) ),
PARTITION returns VALUES ('R')
( SUBPARTITION cr1 START (1) END (2)
( SUBPARTITION ca VALUES ('CA') ),
SUBPARTITION cr2 START (3) END (4)
( SUBPARTITION ca VALUES ('CA') ) )
);Use the SUBPARTITION TEMPLATE clause to create the same partition table as the previous example:
CREATE TABLE sales1 (id int, year int, qtr int, c_rank int, code char(1), region text)
DISTRIBUTED BY (id)
PARTITION BY LIST (code)
SUBPARTITION BY RANGE (c_rank)
SUBPARTITION TEMPLATE (
SUBPARTITION cr1 START (1) END (2),
SUBPARTITION cr2 START (3) END (4) )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION ca VALUES ('CA') )
( PARTITION sales VALUES ('S'),
PARTITION returns VALUES ('R')
) ;Create a three-level partition table and use the subpartition template and default partition at each level:
CREATE TABLE sales (id int, year int, qtr int, c_rank int, code char(1), region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (qtr)
SUBPARTITION TEMPLATE (
START (1) END (5) EVERY (1),
DEFAULT SUBPARTITION bad_qtr )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions)
( START (2009) END (2011) EVERY (1),
DEFAULT PARTITION outlying_years);Specify the SEGMENT_SET object for the new partition:
## Create SEGMENT_SET object ss1
CREATE SEGMENT_SET ss1 SEGMENTS('0,2');
## Create a t table
CREATE TABLE t(a int, b int) DISTRIBUTED BY(a) PARTITION BY RANGE(b) (DEFAULT PARTITION others SEGMENT_SET ss1);
## Specify the SEGMENT_SET object for the new partition
CREATE TABLE t_part_manual PARTITION OF t FOR VALUES FROM (3) TO (6) SEGMENT_SET ss1;Create a MARS2 table. MARS2 tables depend on the matrixts extension. Before building a table, you first need to create an extension in the database using the storage engine.
CREATE EXTENSION matrixts;Create a table that can be adaptively encoded and compressed. For details, see Using Compression.
CREATE TABLE t (
f1 int8
, f2 int8
)
USING MARS2
WITH(
compresstype=mxcustom
);Creates a table that specifies both the table level and the column level compression. The column-level compression specification takes precedence over the table level (the column specifies ENCODING(compresstype=none)/ENCODING(minmax) exception). For details, see Using Compression.
CREATE TABLE t (
f1 int8 ENCODING(compresstype=lz4)
, f2 int8
)
USING MARS2
WITH(
compresstype=mxcustom
, encodechain=auto
);
CREATE INDEX idx_mars2 ON t USING mars2_btree(f1);Notes!
matrixtsis extended to the database level, and it can be created once in a database without repeated creation.
CREATE TABLE disk_mars2(
time timestamp with time zone,
tag_id int,
read float,
write float
)
USING MARS2
WITH (compresstype=zstd, compresslevel=3)
DISTRIBUTED BY (tag_id);
CREATE INDEX ON disk_mars2 USING mars2_btree(time,tag_id);Create a MARS3 table. The MARS3 table also relies on the matrixts extension. Before building a table, you first need to create an extension in the database using the storage engine.
CREATE EXTENSION matrixts;CREATE TABLE t(
time timestamp with time zone,
tag_id int,
i4 int4,
i8 int8
)
USING MARS3
WITH (compresstype=zstd, compresslevel=3,compress_threshold=1200,
mars3options='rowstore_size=64,prefer_load_mode=normal,level_size_amplifier=8')
DISTRIBUTED BY (tag_id)
ORDER BY (time, tag_id);The CREATE TABLE command complies with SQL standards, except for the following:
Temporary Tables — In the SQL standard, temporary tables are defined only once and automatically exist in each session that requires them (starting with empty content). Instead, the YMatrix database requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, while the standard approach constraints all instances of a given temporary table name to have the same table structure.
The standard difference between global and local temporary tables is not in the YMatrix database. The YMatrix database will accept the GLOBAL and LOCAL keywords in the temporary table declaration, but they are invalid and deprecated.
If the ON COMMIT clause is omitted, the SQL standard specifies the default behavior as ON COMMIT DELETE ROWS. However, the default behavior in the YMatrix database is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option does not exist in the SQL standard.
Column Check Constraints — The SQL standard says that CHECK column constraints can only reference the columns to which they apply. Only CHECK table constraints can reference multiple columns. The YMatrix database does not enforce this restriction; it treats column and table check constraints the same.
NULL Constraint — NULL constraints are a YMatrix database extension to the SQL standard, for compatibility with certain other database systems (and symmetric NOT NULL constraints). Since it is the default value for any column, it does not need its existence.
Inheritance — Multiple inheritance through the INHERITS clause is an extension of the YMatrix database language. SQL: 1999 and later defines a single inheritance using different syntax and semantics. The YMatrix database does not yet support SQL:1999 style inheritance.
Partitioning — Table partitioning through the PARTITION BY clause is an extension of the YMatrix database language.
Zero-column tables — The YMatrix database allows the creation of tables that do not contain any columns (for example, CREATE TABLE foo();). This is an extension of the SQL standard and does not allow the use of zero lists. The zero list itself is of little use, but the use of zero lists does not allow strange special cases when ALTER TABLE DROP COLUMN, so YMatrix decided to ignore this specification limit.
LIKE — Although there are LIKE clauses in the SQL standard, many of the options accepted by the YMatrix database are not in the standard, and some of the options of the YMatrix database do not implement the standard.
WITH clause — The WITH clause is a YMatrix database extension. Storage parameters and OID are not in the standard.
Tablespaces — The YMatrix database concept for tablespaces is not part of the SQL standard. Clauses TABLESPACE and USING INDEX TABLESPACE are extensions.
Data Distribution — The concept of YMatrix databases for parallel or distributed databases is not part of the SQL standard. The DISTRIBUTED clause is an extension.