Greenplum中定义数据库对象之创建与管理表

创建与管理表

创建表

         通过查询CREATETABLE命令帮助如下所示：

Command:     CREATE TABLE Description: define a new table Syntax: CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (     -->指定表类型：全局|本地临时 [ { column_name data_type [ DEFAULT default_expr ]     [column_constraint [ ... ] [ ENCODING ( storage_directive [,...] ) ]                              -->指定表编码 ]    | table_constraint                                             -->指定表约束    | LIKE other_table [{INCLUDING | EXCLUDING}                       {DEFAULTS | CONSTRAINTS}] ...}    [, ... ] ]    [column_reference_storage_directive [, . ]    )    [ INHERITS ( parent_table [, ... ] ) ]                              -->指定表继承关系    [ WITH ( storage_parameter=value [, ... ] )                       -->指定存储空间    [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]    [ TABLESPACE tablespace ]                                      -->指定表空间    [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]      -->指定分布列    [ PARTITION BY partition_type (column)                        -->指定分区列        [ SUBPARTITION BY partition_type (column) ]                -->指定子分区列           [ SUBPARTITION TEMPLATE ( template_spec ) ]        [...]     ( partition_spec )         | [ SUBPARTITION BY partition_type (column) ]           [...]     ( partition_spec       [ ( subpartition_spec            [(...)]          ) ]     )   where storage_parameter is:                                 -->指定创建表存在的参数：    APPENDONLY={TRUE|FALSE}                                  -->指定是否可以appendonly    BLOCKSIZE={8192-2097152}                                    -->指定表块大小    ORIENTATION={COLUMN|ROW}                                    -->指定表旋转方式    COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}                   -->指定表的压缩方式    COMPRESSLEVEL={0-9}                                         -->指定表的压缩级别    FILLFACTOR={10-100}                                         -->指定表的占空因数    OIDS[=TRUE|FALSE]                                           -->指定表的对象标识符   where column_constraint is:                                    -->指定列约束如下：    [CONSTRAINT constraint_name]                                 -->约束名称    NOT NULL | NULL                                              -->是否为空    | UNIQUE [USING INDEX TABLESPACE tablespace]                 -->唯一[使用索引表空间]             [WITH ( FILLFACTOR = value )]    | PRIMARY KEY [USING INDEX TABLESPACE tablespace]            -->主键                  [WITH ( FILLFACTOR = value )]    | CHECK ( expression )                                       -->其它表达式约束   and table_constraint is:               -->指定表约束如下：    [CONSTRAINT constraint_name]         -->指定表约束名称    UNIQUE ( column_name [, ... ] )      -->指定唯一的列名等           [USING INDEX TABLESPACE tablespace] -->唯一[使用索引表空间]           [WITH ( FILLFACTOR=value )]    | PRIMARY KEY ( column_name [, ... ] )  -->主键                  [USING INDEX TABLESPACE tablespace]                  [WITH ( FILLFACTOR=value )]    | CHECK ( expression )                   -->其它表达式约束   where partition_type is:                    -->指定分区类型：LIST|RANGE     LIST   | RANGE   where partition_specification is:           -->指定分区说明：包含分区元素 partition_element [, ...]   and 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 [, ...] and 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 ]   where storage_directive is:                     -->指定存储策略    COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}  | COMPRESSLEVEL={0-9}  | BLOCKSIZE={8192-2097152}   Where column_reference_storage_directive is:     -->指定列参考存储策略      COLUMN column_name ENCODING ( storage_directive [, ... ] ), ...  | DEFAULT COLUMN ENCODING ( storage_directive [, ... ] )

关于上述命令的说明：

选择列的数据类型

•   对于字符类型，多数选择TEXT或者VARCHAR
•   对于数值类型，尽量选择更小的数据类型
•   对于打算用作连接的列，选择相同的数据