CREATE TABLE - Lindorm - Alibaba Cloud Documentation Center

This topic describes the syntax used to create a table that supports geometry data types.

Syntax

create_table_statement ::=  CREATE TABLE [ IF NOT EXISTS ] table_name
                            '('
                                column_definition
                                ( ',' column_definition )*
                                 ',' [constraint pk]  PRIMARY KEY '(' primary_key ')' 
                            ')' WITH [ table_options ]
column_definition      ::=  column_name lql_type 
primary_key            ::=  primary_item [ ',' primary_item ]
primary_item           ::=  column_name (ASC|DESC)
                            | [z-order] '(' [ column_name ( ',' column_name )* ] ')'

Note This syntax supports IF NOT EXISTS. You can specify one or more columns as the primary key.

Table options


Option	Type	Description
COMPRESSION	String	The algorithm used to compress the table. Valid values: SNAPPY ZSTD LZ4 Note In LindormTable versions earlier than 2.3.4, no compression algorithm is specified by default when you create a table. In LindormTable V2.3.4 and later, the ZSTD algorithm is specified by default to compress a table when you create the table.
TTL	Int	The Time-to-Live (TTL) value of data in the table. Unit: seconds. By default, if you do not specify this option, data in the table does not expire.
MUTABILITY	String	An option related to indexes. This option specifies the mutability of the base table. Valid values: IMMUTABLE IMMUTABLE_ROWS MUTABLE_LATEST MUTABLE_ALL Note For more information, see High-performance native secondary indexes.
CONSISTENCY	String	The consistency level of the table. Default value: eventual. Valid values: eventual: Data in the base table and index table become consistent after a point in time. strong: Data in the base table and index table are consistent at all points in time.
NUMREGIONS	Int	The number of data regions in the table that you want to create.
CHS	Int	The time period after which data is stored by using cold storage. Unit: second. Note To configure this option, you must enable the cold storage feature. For more information, see Overview. When you configure this option, you must also set the CHS_L2 option to storagetype=COLD.
STARTKEY and ENDKEY	Same data type as that of the first column in the primary key specified by PRIMARY KEY	The start key and end key of each region. Note The STARTKEY and ENDKEY options must be specified together with the NUMREGIONS option. If the NUMREGIONS option is not specified, the STARTKEY and ENDKEY options do not take effect. If you specify string values for the STARTKEY and ENDKEY options, the strings are implicitly converted to values with the same data types as the first column of the primary key. Only the following data types are supported for the STARTKEY and ENDKEY options: SMALLINT INTEGER BIGINT CHAR VARCHAR FLOAT DOUBLE
DYNAMIC_COLUMNS	String	Specifies whether to enable dynamic columns. Default value: False. Valid values: True: Enable dynamic columns for the table. False: Disable dynamic columns for the table. Note Dynamic columns support only the VARBINARY data type. For more information about dynamic columns, see Dynamic columns.

Note In Lindorm 2.2.16 and later versions, you can use multiple keywords to configure table attributes. In Lindorm versions earlier than 2.2.16, you do not need to add the WITH keyword when you configure table attributes. In addition, you must enclose the keys of table attributes in single quotation marks (') when you configure table attributes. If the value of a table attribute is a string, you must also enclose the value in single quotation marks ('). Lindorm 2.2.16 and later versions support all syntax rules in earlier versions. In Lindorm 2.2.16 and later versions, you can add the WITH keyword before table attributes. You do not need to use single quotation marks (') to enclose the keys of table attributes. However, you must use single quotation marks (') to enclose the values of table attributes. The sample statements described in the following section can be directly executed.

Examples

You can execute the following statement to create a table that supports all geometry data types:

CREATE TABLE geoms(gid INT, g GEOMETRY, PRIMARY KEY(gid));

You can execute the following statement to write different types of geometry data to the table:

UPSERT INTO geoms(gid, g) VALUES(0,ST_GeomFromText('POINT(-10.1 3.3)')),(1,ST_GeomFromText('LINESTRING(-12.2 4.3, -10.2 4.3)')),(2,ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))'));

You can execute the following statement to create a table that supports only point objects:
```
CREATE TABLE pts(gid INT, g GEOMETRY(POINT), PRIMARY KEY(gid);
```
You can execute the following statement to write point objects to the table:
```
UPSERT INTO pts(gid, g) VALUES(0, ST_MakePoint(0,0)),(1, ST_MakePoint(1,1));
```
You can execute the following statement to create a table that supports only polygon objects:
```
CREATE TABLE polys(gid INT, g GEOMETRY(POLYGON), PRIMARY KEY(gid));
```
You can execute the following statement to write a polygon object to the table:
```
UPSERT INTO polys(gid,g) VALUES(0,ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))'));
```
You can execute the following statement to create a table that supports only LineString objects:
```
CREATE TABLE lines(gid INT, g GEOMETRY(LINESTRING), PRIMARY KEY(gid));
```
You can execute the following statement to write a LineString object to the table:
```
UPSERT INTO lines(gid,g) VALUES(0, ST_GeomFromText('LINESTRING(-12.2 4.3, -10.2 4.3)'));
```
You can execute the following statement to create a table that supports only MultiPoint objects:
```
CREATE TABLE multipoints(gid INT, g GEOMETRY(MULTIPOINT), PRIMARY KEY(gid));
```
You can execute the following statement to write a MultiPoint object to the table:
```
UPSERT INTO multipoints(gid,g) VALUES(0, ST_GeomFromText('MULTIPOINT (10 40, 40 30, 20 20, 30 10)'));
```

You can execute the following statement to create a table that supports only MultiLineString objects:

CREATE TABLE multilines(gid INT, g GEOMETRY(MULTILINESTRING), PRIMARY KEY(gid));

You can execute the following statement to write a MultiLineString object to the table:

UPSERT INTO multilines(gid,g) VALUES(0, ST_GeomFromText('MULTILINESTRING ((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'));

You can execute the following statement to create a table that supports only MultiPolygon objects:

CREATE TABLE multipolys(gid INT, g GEOMETRY(MULTIPOLYGON), PRIMARY KEY(gid));

You can execute the following statement to write a MultiPolygon object to the table:

UPSERT INTO multipolys(gid,g) VALUES(0, ST_GeomFromText('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),((15 5, 40 10, 10 20, 5 10, 15 5)))'));

You can execute the following statement to create a table that supports only GeometryCollection objects:

CREATE TABLE collections(gid INT, g GEOMETRY(GEOMETRYCOLLECTION), PRIMARY KEY(gid));

You can execute the following statement to write a GeometryCollection object to the table:

UPSERT INTO collections(gid,g) VALUES(0, ST_GeomFromText('GEOMETRYCOLLECTION (POINT (40 10), LINESTRING (10 10, 20 20, 10 40), POLYGON ((40 40, 20 45, 45 30, 40 40)))'));

You can execute the following statement to create a table that contains multiple columns of different geometry data:
```
CREATE TABLE mix(gid INT, pt GEOMETRY(POINT), ply GEOMETRY(POLYGON), PRIMARY KEY(gid));
```