CREATE TABLE
Function
Creates an empty table in the current database. The table will be owned by the creator.
Precautions
- This section describes only the new syntax of Dolphin. The original syntax of openGauss is not deleted or modified.
Syntax
Create a table using LIKE.
CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name LIKE source_table [ like_option [...] ]
Create a table.
CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| table_indexclause
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ AUTO_INCREMENT [ = ] value ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMMENT {=| } 'text' ];
[ create_option ]
Where create\_option is:
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMPRESSION [=] compression_arg ]
[ ENGINE [=] engine_name ]
[ COLLATE [=] collation_name ]
[ [DEFAULT] { CHARSET | CHARACTER SET } [=] charset_name ]
[ ROW_FORMAT [=] row_format_name ]
In addition to the WITH option, you can enter the same create\_option for multiple times. The latest input prevails.
table_indexclause:
{INDEX | KEY} [index_name] [index_type] (key_part,...)[index_option]...
This syntax does not support CREATE FOREIGN TABLE (such as MOT).
Values of index_type are as follows:
USING {BTREE | HASH | GIN | GIST | PSORT | UBTREE}
Values of key_part are as follows:
{col_name[(length)] | (expr)} [ASC | DESC]
length indicates the prefix index.
The index_option parameter is as follows:
index_option:{ COMMENT 'string' | index_type }
The sequence and quantity of COMMENT and index_type can be random, but only the last value of the same column takes effect.
The like_option is as follows:
{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }
Parameter Description
data_type
Specifies the data type of the column.
For the enumeration type ENUM and character types such as CHAR, CHARACTER, VARCHAR, TEXT, you can use the keyword CHARSET or CHARACTER SET to specify the column character set when creating a table. Currently, it is used only for syntax and has no actual purpose.
column_constraint
The ON UPDATE feature of MySQL is added to the column type constraint. The constraint is of the same type as the DEFAULT attribute. The ON UPDATE attribute is used to automatically update the timestamp column when the timestamp column of the UPDATE operation is set to the default value.
CREATE TABLE table_name(column_name timestamp ON UPDATE CURRENT_TIMESTAMP);
COLLATE collation
Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the select * from pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.
If a collation is not supported, the database issues a warning and sets the column as the default collation.
{ [DEFAULT] CHARSET | CHARACTER SET } [=] charset_name
Selects the character set used by the table. Currently, it is used only for syntax and has no actual purpose.
COLLATE [=] collation_name
Selects the collation used by a table. Currently, it is used only for syntax and has no actual purpose.
ROW_FORMAT [=] row_format_name
Selects the row-store format used by a table. Currently, it is used only for syntax and has no actual purpose.
Examples
--Create an index on a table.
openGauss=# CREATE TABLE tpcds.warehouse_t24
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2) ,
key (W_WAREHOUSE_SK) ,
index idx_ID using btree (W_WAREHOUSE_ID)
);
--Create composite indexes, expression indexes, and function indexes on tables.
openGauss=# CREATE TABLE tpcds.warehouse_t25
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2) ,
key using btree (W_WAREHOUSE_SK, W_WAREHOUSE_ID desc) ,
index idx_SQ_FT using btree ((abs(W_WAREHOUSE_SQ_FT))) ,
key idx_SK using btree ((abs(W_WAREHOUSE_SK)+1))
);
--The index\_option column is included.
openGauss=# create table test_option(a int, index idx_op using btree(a) comment 'idx comment');
--Specify the character set for the column when creating a table.
openGauss=# CREATE TABLE t_column_charset(c text CHARSET test_charset);
WARNING: character set "test_charset" for type text is not supported yet. default value set
CREATE TABLE
--Specify the character order for the table when creating the table.
openGauss=# CREATE TABLE t_table_collate(c text) COLLATE test_collation;
WARNING: COLLATE for TABLE is not supported for current version. skipped
CREATE TABLE
--Specify the character set for the table when creating the table.
openGauss=# CREATE TABLE t_table_charset(c text) CHARSET test_charset;
WARNING: CHARSET for TABLE is not supported for current version. skipped
CREATE TABLE
--Specify the row record format for the table when creating the table.
openGauss=# CREATE TABLE t_row_format(c text) ROW_FORMAT test_row_format;
WARNING: ROW_FORMAT for TABLE is not supported for current version. skipped
CREATE TABLE