网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts 盘古助手
华为云Astro大屏应用
计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
云手机服务器 CPH
专属主机 DeH
弹性伸缩 AS
镜像服务 IMS
函数工作流 FunctionGraph
云耀云服务器(旧版)
VR云渲游平台 CVR
Huawei Cloud EulerOS
网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
CDN与智能边缘
内容分发网络 CDN
智能边缘云 IEC
智能边缘平台 IEF
CloudPond云服务
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
可信智能计算服务 TICS
推荐系统 RES
云搜索服务 CSS
数据可视化 DLV
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
湖仓构建 LakeFormation
智能数据洞察 DataArts Insight
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
开天aPaaS
应用平台 AppStage
开天企业工作台 MSSE
开天集成工作台 MSSI
API中心 API Hub
云消息服务 KooMessage
交换数据空间 EDS
云地图服务 KooMap
云手机服务 KooPhone
组织成员账号 OrgID
云空间服务 KooDrive
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
区块链
区块链服务 BCS
数字资产链 DAC
华为云区块链引擎服务 HBS
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
价格
成本优化最佳实践
专属云商业逻辑
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
其他
管理控制台
消息中心
产品价格详情
系统权限
客户关联华为云合作伙伴须知
公共问题
宽限期保留期
奖励推广计划
活动
云服务信任体系能力说明
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts 盘古助手
华为云Astro大屏应用
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
存储容灾服务 SDRS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
云存储网关 CSG
专属分布式存储服务 DSS
数据工坊 DWR
地图数据 MapDS
键值存储服务 KVS
容器
云容器引擎 CCE
云容器实例 CCI
容器镜像服务 SWR
云原生服务中心 OSC
应用服务网格 ASM
华为云UCS
数据库
云数据库 RDS
数据复制服务 DRS
文档数据库服务 DDS
分布式数据库中间件 DDM
云数据库 GaussDB
云数据库 GeminiDB
数据管理服务 DAS
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
AI开发平台ModelArts
华为HiLens
图引擎服务 GES
图像识别 Image
文字识别 OCR
自然语言处理 NLP
内容审核 Moderation
图像搜索 ImageSearch
医疗智能体 EIHealth
企业级AI应用开发专业套件 ModelArts Pro
人脸识别服务 FRS
对话机器人服务 CBS
语音交互服务 SIS
人证核身服务 IVS
视频智能分析服务 VIAS
城市智能体
自动驾驶云服务 Octopus
盘古大模型 PanguLargeModels
IoT物联网
设备接入 IoTDA
全球SIM联接 GSL
IoT数据分析 IoTA
路网数字化服务 DRIS
IoT边缘 IoTEdge
设备发放 IoTDP
企业应用
域名注册服务 Domains
云解析服务 DNS
企业门户 EWP
ICP备案
商标注册
华为云WeLink
华为云会议 Meeting
隐私保护通话 PrivateNumber
语音通话 VoiceCall
消息&短信 MSGSMS
云管理网络
SD-WAN 云服务
边缘数据中心管理 EDCM
云桌面 Workspace
应用与数据集成平台 ROMA Connect
ROMA资产中心 ROMA Exchange
API全生命周期管理 ROMA API
政企自服务管理 ESM
视频
实时音视频 SparkRTC
视频直播 Live
视频点播 VOD
媒体处理 MPC
视频接入服务 VIS
数字内容生产线 MetaStudio
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
专属云
专属计算集群 DCC
开发者工具
SDK开发指南
API签名指南
DevStar
华为云命令行工具服务 KooCLI
Huawei Cloud Toolkit
CodeArts API
云化转型
云架构中心
云采用框架
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
客户运营能力
国际站常见问题
支持计划
专业服务
合作伙伴支持计划
我的凭证
华为云公共事业服务云平台
工业软件
工业数字模型驱动引擎
硬件开发工具链平台云服务
工业数据转换引擎云服务
更新时间:2025-01-08 GMT+08:00
分享

CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表。

该表由命令执行者所有,但系统管理员在普通用户同名schema下创建的表,表的所有者为schema的同名用户(非系统管理员)。

注意事项

  • 列存表支持的数据类型请参考列存表支持的数据类型
  • 创建列存和HDFS分区表的数量建议不超过1000个。
  • 表中的主键约束和唯一约束必须包含分布列。
  • 不支持修改已有表的分布列数据类型。
  • 行存REPLICATION分布表不支持将系统列设置为主键。
  • 如果在建表过程中数据库系统发生故障,系统恢复后可能无法自动清除之前已创建的、大小为0的磁盘文件。此种情况出现概率小,不影响数据库系统的正常运行。
  • 列存表支持PARTIAL CLUSTER KEY、主键和唯一表级约束,不支持外键表级约束。
  • 列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。
  • 列存表支持delta表,受表级参数enable_delta控制是否开启,受参数deltarow_threshold控制进入delta表的阈值。不推荐使用列存带Delta表,否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。
  • 冷热表仅支持列存分区表,依赖于可用的OBS服务。
  • 冷热表仅支持默认表空间为default_obs_tbs,如需新增obs表空间可联系技术支持。
  • 如需创建列存表,需显式设置orientation属性为column,存算分离版本如需创建本地表(数据全部存储在EVS盘),需显式指定colversion=2.0。
  • 创建表后,不支持通过ALTER TABLE语法将非V3表切换为V3表(即colversion为2.0不支持切为3.0)。
  • V3表(即colversion=3.0,存算分离表)不支持设置delta表和列存二级分区。
  • V3表不支持设置为Hstore表、冷热表、时序表。
  • V3表不支持创建全局临时表和临时表,创建的临时表会自动转化为colversion=2.0的临时表。
  • 不建议创建普通表时指定自定义TABLESPACE。
  • 创建行存表时应避免指定COMPRESS压缩属性。
  • 创建HASH分布的表对象时,要确保数据分布均匀(10G以上数据量的表,倾斜率控制在10%以内)。
  • 创建REPLICATION分布的表对象,要确保表数据量控制在100万行以内。
  • 创建HSTORE表时,必须确保数据库GUC参数设置满足以下条件:
    • autovacuum设置为on。
    • autovacuum_max_workers_hstore取值大于0。
    • autovacuum_max_workers取值大于autovacuum_max_workers_hstore的取值。
  • 针对存在时间字段的大表(数据量5000万行以上),必须设计成分区表,根据查询特征合理设计分区间隔。
  • 针对有大批量数据增删改的表,索引个数建议控制在3个以内,最多不超过5个。
  • 更多开发设计规范参见总体开发设计规范

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE [ [ GLOBAL | LOCAL | VOLATILE ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    { ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option [...] ] }
        [, ... ])|
        LIKE source_table [ like_option [...] ] } 
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ COMPRESS | NOCOMPRESS ]
    [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
    [ COMMENT [=] 'text' ];
  • 其中列约束column_constraint为:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    [ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) |
      DEFAULT default_expr |
      ON UPDATE on_update_expr |
      COMMENT 'text' |
      UNIQUE [ NULLS [NOT] DISTINCT | NULLS IGNORE ] index_parameters |
      PRIMARY KEY index_parameters |
      REFERENCES reftable [ ( refcolumn ) ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    
  • 其中列的压缩可选项compress_mode为:
    1
    { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
    
  • 其中表约束table_constraint为:
    1
    2
    3
    4
    5
    6
    [ CONSTRAINT constraint_name ]
    { CHECK ( expression ) |
      UNIQUE [ NULLS [NOT] DISTINCT | NULLS IGNORE ] ( column_name [, ... ] ) index_parameters |
      PRIMARY KEY ( column_name [, ... ] ) index_parameters |
      PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    
  • 其中like选项like_option为:
    1
    { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | DROPCOLUMNS | ALL }
    
  • 其中索引参数index_parameters为:
    1
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    

表设计参考

GaussDB(DWS)兼容PostgreSQL生态,行存及其btree索引和PostgreSQL类似,列存及其索引为自研。建表时,选择合适的存储方式、分布列、分区键、索引,能够使SQL在执行时快速命中数据,减少IO消耗。下图是一个SQL从发起到获取数据的执行流程,通过下图,可以理解每个技术手段的作用,用于指导性能调优。

  1. SQL执行时,分区表会通过Partition Column分区裁剪,定位到所在分区。
  2. Hash分布表会通过Distribute Column快速定位到数据所在的数据分片(存算一体定位到DN,存算分离定位到bucket)。
  3. 行存通过btree快速定位到数据所在的Page;列存通过min-max索引快速定位到可能存在数据的CU数据块,PCK(Partial Cluster Key)列上的min-max索引过滤效果最好。
  4. 系统自动为列存的所有列维护min-max索引,无需用户定义。min-max索引是粗过滤,满足min-max条件的CU数据块不一定真正存在满足filter条件的数据行,如果定义了bitmap column,可以通过bitmap index快速定位到CU内符合条件的数据所在的行号;对于有序的CU,也会通过二分查找快速定位数据的行号。
  5. 列存也支持btree和gin索引,通过btree/gin索引也可以快速定位到满足条件的数据所在的CU及行号,但是,索引的维护代价比较大,除非对点查有极高的性能诉求,推荐使用bitmap index替代btree/gin。

DWS已有的优化手段可参考下表。

表1 优化手段

编号

优化手段

使用建议

SQL示例

建表后是否可修改

1

字符串类型

  1. 字符串类型会比定长类型慢,如果能使用定长类型时不建议使用字符串类型。
  2. 尽量指定长度在16以内,性能会显著提高。

-

是,已有数据会重写。

2

Numeric类型

Numeric类型要求都指定精度,性能会显著提高,不建议使用无精度的numeric。

-

是,已有数据会重写。

3

Partition by Column

  1. 需用户定义,适用分区表,通过分区键进行剪枝,支持partition wise join,适用等值和范围查询。
  2. 分区数目不建议超过1000,分区列不建议超过两列。
1
SELECT * FROM t1 WHERE t1.c1='p1';

否,如需修改请重新建表。

4

secondary_part_column

  1. 需用户定义,只适用于列存表,适用等值查询。
  2. 可在最常用的一个等值过滤列上指定二级分区。
1
SELECT * FROM t1 WHERE t1.c1=p1;

否,如需修改请重新建表。

5

Distribute by Column

需用户定义,适用于频繁进行group by或者多表join的join字段,通过local join减少数据shuffle,适用等值查询。

1
SELECT * FROM t1 join t2 on  t1.c3 = t2.c1;

否,如需修改请重新建表。

6

Bitmap column

需用户定义,根据CU内的重复值,自适应创建bitmap index(基数<=32),或者bloom filter(基数>32),适用varchar/text类型列的等值查询,对于where条件涉及的列,建议创建。

1
SELECT * FROM t1 WHERE t1.c4=hello;

可以修改,已有数据不重写,只影响新数据。

7

Min-max索引

  1. min-max索引无需用户定义,适用等值和范围查询。
  2. min-max的过滤效果取决于数据的有序性,指定PCK的列,过滤效果会更好。
1
SELECT * FROM t1 WHERE c3 > 100 and c3 < 200;

PCK可以修改,已有数据不重写,只影响新数据。

8

主键(btree索引)

  1. UPSERT入库强依赖主键,需用户定义,适用等值和范围查询,不建议超过5列。
  2. 满足业务前提下,能使用定长类型的列更好,定义时尽可能将distinct值多的列放前面。
1
SELECT * FROM t1 WHERE c3 > 100 and c3 < 200;

可以修改,重建索引。

9

Gin索引

  1. 需用户定义,适用不固定的多条件等值查询,Distinct值超过100W的列不建议使用。
  2. 过滤后数据量小于1000的适用,过滤后数据量依然较大的,不推荐使用。
1
SELECT * FROM t1 WHERE c1 = 100 and c3 = 200 and c2 = 105;

可以修改,重建索引。

10

Orientation=column/row

指定表为行存或者列存,行存不支持压缩,适合点查询和频繁更新场景;列存支持压缩,适合分析场景。

-

否,如需修改请重新建表。

参数说明

  • UNLOGGED

    如果指定此关键字,则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中,这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断,会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。

    使用场景:非日志表不能保证数据的安全性,用户应该在确保数据已经做好备份的前提下使用,例如系统升级时进行数据的备份。

    故障处理:当异常关机等操作导致非日志表上的索引发生数据丢失时,用户应该对发生错误的索引进行重建。

    1. UNLOGGED表无主备机制,在系统故障或异常断点等情况下,会有数据丢失风险,不可用来存储基础数据。
    2. 9.1.0版本,UNLOGGED表默认存储在pg_unlogged表空间下,且不可迁移或指定到其他表空间。
    3. 从低版本升级到9.1.0版本后,旧版本创建的UNLOGGED表还存储在原表空间下。
    4. 由于实例异常重启时会对UNLOGGED表进行重置,所以可能影响实例RTO。9.1.0版本提供UNLOGGED表迁移脚本switch_unlogged_tablepace.py,配合GUC参数enable_unlogged_tablespace_compat,可以优化RTO。
  • GLOBAL | LOCAL | VOLATILE

    创建临时表时在TEMP或TEMPORARY前指定GLOBAL、LOCAL、VOLATILE关键字,以创建不同属性的临时表。仅8.2.1.220及以上集群版本支持全局临时表。

    • 指定关键字LOCAL,则创建LOCAL临时表(本地临时表)。
    • 指定关键字GLOBAL,则临时表的属性取决于GUC参数enable_global_temp_table的设置,该参数默认值为ON。

      当enable_global_temp_tabl=on时,表示创建GLOBAL临时表(全局临时表);

      当enable_global_temp_tabl=off时,表示创建LOCAL临时表(本地临时表),等同于指定关键字LOCAL。

    • 指定关键字VOLATILE,则创建VOLATILE临时表。
    • 当default_temptable_type=local时,不指定关键字创建的临时表为LOCAL类型临时表,当default_temptable_type=volatile时,不指定关键字创建的临时表为VOLATILE类型临时表。
  • TEMPORARY | TEMP

    如果指定TEMP或TEMPORARY关键字,则创建的表为临时表。临时表只在当前会话可见,本会话结束后会自动删除。因此,在除当前会话连接的CN以外的其他CN故障时,仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建,对于涉及对临时表操作的DDL语句,会产生DDL失败的报错。因此,建议DDL语句中不要对临时表进行操作。TEMP和TEMPORARY等价。

    • LOCAL/VOLATILE临时表通过每个会话独立的以pg_temp开头的schema来保证只对当前会话可见,因此,不建议用户在日常操作中手动删除以pg_temp,pg_toast_temp开头的schema。
    • 如果建表时不指定TEMPORARY/TEMP关键字,而指定表的schema为当前会话的pg_temp_开头的schema,则此表会被创建为临时表。
    • LOCAL临时表的所有相关元数据同普通表类似,都存储在系统表内,而VOLATILE临时表会将除schema外的相关表结构元数据直接存储在内存中。所以相对本地临时表而言,VOLATILE临时表有更多约束:
      • 当前CN或DN重启之后,对应实例上的内存数据丢失,相关volatile临时表会失效。
      • VOLATILE临时表当前不支持ALTER/GRANT等修改表结构相关操作。
      • VOLATILE临时表和LOCAL临时表共用一种临时schema,所以在同一session中,VOLATILE临时表和LOCAL临时表不能存在同名表。
      • VOLATILE临时表信息不存储在系统表内,所以无法通过对系统表执行DML语句查询到VOLATILE相关元数据。
      • VOLATILE临时表仅支持普通的行存、列存表,不支持delta表、时序表、冷热表。
      • 不支持基于VOLATILE临时表创建视图。
      • 不支持创建临时表时指定tablespace(VOLATILE临时表默认tablespace均为pg_volatile)。
      • 创建VOLATILE临时表时不支持指定约束:CHECK约束、UNIQUE约束、主键约束、触发器约束、EXCLUDE约束、PARTIAL CLUSTER约束。
    • GLOBAL临时表的所有相关元数据同普通表类似,都存储在系统表内。
      • GLOBAL临时表与LOCAL临时表不同的是,会话退出时元数据不会删除,但会话的数据会删除。不同会话的数据独立,但共享同一份GLOBAL临时表的元数据。
      • 全局临时表的schema与普通表类似,不是以pg_temp开头的schema,但与LOCAL/VOLATILE临时表不同,所以可以与LOCAL/VOLATILE临时表同名。
      • 全局临时表仅支持普通的行存、列存表,不支持delta表、时序表、冷热表。
      • 不支持操作其他逻辑集群的全局临时表。
  • IF NOT EXISTS

    指定IF NOT EXISTS时,若不存在同名表,则可以成功创建表。若已存在同名表,创建时不会报错,仅会提示该表已存在并跳过创建。

  • table_name

    要创建的表名。

    表名长度不超过63个字符,以字母或下划线开头,可包含字母、数字、下划线、$、#。

    使用双引号括起来的表名可以包含空格和特殊字符,但不建议在表名中使用这些字符,因为这样可能会使表名难以引用和使用,而且不同的数据库兼容模式下可能对于这种表名的处理方式也有所不同。

  • column_name

    新表中要创建的字段名。

    字段名长度不超过63个字符,以字母或下划线开头,可包含字母、数字、下划线、$、#。

  • data_type

    字段的数据类型。

    在兼容Teradata或MySQL语法的数据库中,字段数据类型指定为DATE时同样返回为DATE类型,否则返回TIMESTAMP类型。

  • compress_mode

    表字段的压缩选项,当前仅对行存表有效。该选项指定表字段优先使用的压缩算法。

    取值范围:DELTA、PREFIX、DICTIONARY、NUMSTR、NOCOMPRESS

  • COLLATE collation

    COLLATE子句指定列的排序规则(该列必须是可排列的数据类型)。如果没有指定,则使用默认的排序规则。

  • LIKE source_table [ like_option ... ]

    LIKE子句声明一个表,新表自动从这个表中继承所有字段名及其数据类型和非空约束。

    新表与源表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会传播到新表中,并且也不可能在扫描源表的时候包含新表的数据。

    被复制的列和约束并不使用相同的名字进行融合。如果明确的指定了相同的名字或者在另外一个LIKE子句中,将会报错。

    • 源表上的字段缺省表达式或者ON UPDATE表达式只有在指定INCLUDING DEFAULTS时,才会复制到新表中。缺省是不包含缺省表达式的,即新表中的所有字段的缺省值都是NULL。
    • 源表上的CHECK约束仅在指定INCLUDING CONSTRAINTS时,会复制到新表中,而其他类型的约束永远不会复制到新表中。非空约束总是复制到新表中。此规则同时适用于表约束和列约束。
    • 如果指定了INCLUDING INDEXES,则源表上的索引也将在新表上创建,默认不建立索引。
    • 如果指定了INCLUDING STORAGE,则复制列的STORAGE设置会复制到新表中,默认情况下不包含STORAGE设置。
    • 如果指定了INCLUDING COMMENTS,则源表列、约束和索引的注释会复制到新表中。默认情况下,不复制源表的注释。
    • 如果指定了INCLUDING PARTITION,则源表的分区定义会复制到新表中,同时新表将不能再使用PARTITION BY子句。默认情况下,不拷贝源表的分区定义。
    • 如果指定了INCLUDING RELOPTIONS,则源表的存储参数(即源表的WITH子句)会复制到新表中。默认情况下,不复制源表的存储参数。

      WITH中的'PERIOD','TTL'为partition相关参数,LIKE INCLUDING RELOPTIONS不会复制到新表中,若要复制需INCLUDING PARTITION。

    • 如果指定了INCLUDING DISTRIBUTION,则源表的分布信息会复制到新表中,包括分布类型和分布列,同时新表将不能再使用DISTRIBUTE BY子句。默认情况下,不拷贝源表的分布信息。
    • 如果指定了INCLUDING DROPCOLUMNS,则源表被删除的列信息会被复制到新表中。默认情况下,不复制源表的删除列信息。
    • INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS、INCLUDING DISTRIBUTION和INCLUDING DROPCOLUMNS的内容。
    • 如果指定了EXCLUDING,则表示不包括指定的参数。
    • 如果是OBS冷热表,INCLUDING PARTITION后新表所有分区均为本地热分区。
    • 如果源表包含serial、bigserial、smallserial类型,或者源表字段的默认值是sequence,且sequence属于源表(通过CREATE SEQUENCE ... OWNED BY创建),这些Sequence不会关联到新表中,新表中会重新创建属于自己的sequence。这和之前版本的处理逻辑不同。如果用户希望源表和新表共享Sequence,需要首先创建一个共享的Sequence(避免使用OWNED BY),并配置为源表字段默认值,这样创建的新表会和源表共享该Sequence。
    • 不建议将其他表私有的Sequence配置为源表字段的默认值,尤其是其他表只分布在特定的NodeGroup上,这可能导致CREATE TABLE ... LIKE执行失败。另外,如果源表配置其他表私有的Sequence,当该表删除时Sequence也会连带删除,这样源表的Sequence将不可用。如果用户希望多个表共享Sequence,建议创建共享的Sequence。
  • WITH ( { storage_parameter = value } [, ... ] )

    这个子句为表或索引指定一个可选的存储参数。

    使用任意精度类型Numeric定义列时,建议指定精度p以及刻度s。在不指定精度和刻度时,会按输入的显示出来。

    参数的详细描述如下所示。

    • FILLFACTOR

      一个表的填充因子(fillfactor)是一个介于10和100之间的百分数。100(完全填充)是默认值。如果指定了较小的填充因子,INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行,这就使得UPDATE有机会在同一页上放置同一条记录的新版本,这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是合适的选择,但是对于频繁更新的表,选择较小的填充因子则更加合适。该参数对于列存表没有意义。

      取值范围:10~100

    • ORIENTATION

      指定表数据的存储方式,即行存方式、列存方式,该参数设置成功后就不再支持修改。

      取值范围:

      • ROW,表示表的数据将以行式存储。

        行存储适合于OLTP业务,此类型的表上交互事务比较多,一次交互会涉及表中的多个列,用行存查询效率较高。

      • COLUMN,表示表的数据将以列式存储。

        列存储适合于数据仓库业务,此类型的表上会做大量的汇聚计算,且涉及的列操作较少。

      默认值:ROW,即行存方式。

    • COMPRESSION

      指定表数据的压缩级别,它决定了表数据的压缩比以及压缩时间。一般来讲,压缩级别越高,压缩比也越大,压缩时间也越长;反之亦然。实际压缩比取决于加载的表数据的分布特征。

      取值范围:

      列存表的有效值为YES/NO和/LOW/MIDDLE/HIGH,默认值为LOW。当设置为YES时,压缩级别默认为LOW。

      • 暂不支持行存表压缩功能。
      • ORC格式迁移到GaussDB(DWS)列存表,如果使用low级别压缩,单副本大小大约是ORC的1.5~2倍;如果使用high级别压缩,单副本大小基本与ORC持平,在新建GaussDB(DWS)集群规模时,需考虑该转换关系。
      • 列存middle压缩固定使用字典压缩,对于数据特征不适合字典压缩的数据,使用middle压缩的结果可能比low压缩更大。

      GaussDB(DWS)内部提供如下压缩算法。

      表2 列存压缩算法

      COMPRESSION

      NUMERIC

      STRING

      INT

      LOW

      delta压缩+RLE压缩

      lz4压缩

      delta压缩(RLE可选)

      MIDDLE

      delta压缩+RLE压缩+lz4压缩

      dict压缩或lz4压缩

      delta压缩或lz4压缩(RLE可选)

      HIGH

      delta压缩+RLE压缩+zlib压缩

      dict压缩或zlib压缩

      delta压缩或zlib压缩(RLE可选)

    • COMPRESSLEVEL

      指定表数据同一压缩级别下的不同压缩水平,它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分,为用户选择压缩比和压缩时间提供了更多的空间。总体来讲,此值越大,表示同一压缩级别下压缩比越大,压缩时间越长;反之亦然。该参数只对列存表有效。

      取值范围:0~3

      默认值:0

    • TTL

      设置分区表定时删除分区task任务。默认不创建删除分区task任务。

      取值范围:1 hour ~ 100 years

    • PERIOD

      设置分区表定时创建分区task任务。如果设置TTL,PERIOD不能大于TTL。

      取值范围:1 hour ~ 100 years

      默认值:1 day

    • MAX_BATCHROW

      指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对列存表有效。

      取值范围:10000~60000

      默认值:60,000

    • PARTIAL_CLUSTER_ROWS

      指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对列存表有效。

      取值范围:600000~2147483647

      默认值:4,200,000

    • time_format

      自增减分区支持INT4/INT8/VARCHAR/TEXT列,存储内容为时间时使用。time_format选项仅当分区键为INT4/INT8/VARCHAR/TEXT时,同时指定period才生效。该参数仅9.1.0.200及以上集群版本支持。

      1. time_format写法遵从postgresql规范,例如:time_format = 'yyyymmdd'。
      2. 分区键的类型

        VARCHAR/TEXT对time_format的规格有如下限制:

        • 精度支持到秒级。
        • 输入内容不包括字母类型,例如month,am,pm等。
        • 时间必须从大到小排列,例如年月日小时分钟秒。

        INT4/INT8对time_format的规格有如下限制:

        • 精度支持到小时级。
        • 输入内容仅包括Y,M,D,HH24。
        • 时间必须从大到小排列,例如年月日小时。
      3. ALTER相关限制
        • 不支持set。
        • 当period被reset时(表示已经关闭自动分区,会报出提示),此时可以reset此选项。
    • enable_delta

      指定了列存表是否开启delta表。该参数只对列存表有效。V3表暂不支持该参数,即COLVERSION为3.0时不支持设置enable_delta为on。

      不推荐使用列存带Delta表,否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。

      默认值:off

    • enable_hstore

      指定了是否创建为HStore表(基于列存表实现)。该参数只对列存表有效。该参数仅8.2.0.100及以上集群版本支持。V3表暂不支持该参数,即COLVERSION为3.0时不支持设置enable_delta为on。

      默认值:off

      打开该参数时必须设置以下GUC参数用于保证HStore表的清理,推荐值如下:

      autovacuum=on,autovacuum_max_workers=6,autovacuum_max_workers_hstore=3。

    • enable_disaster_cstore

      指定了列存表是否开启细粒度容灾功能。该参数仅适用于COLVERSION为2.0的列存表,并且不能和enable_hstore同时打开。该参数仅8.2.0.100及以上集群版本支持。

    • fine_disaster_table_role

      该参数8.2.1版本中已废弃,为兼容历史版本功能保留该参数,当前版本设置无效。

      指定了细粒度容灾表的表角色为主表还是备表。使用该参数时,必须要保证已经开启了enable_disaster_cstore参数。

      取值范围:

      • primary,表示细粒度容灾主表。
      • standby,表示细粒度容灾备表。
    • DELTAROW_THRESHOLD

      指定列存表导入时小于多少行的数据进入delta表,只在表级参数enable_delta开启时生效。该参数只对列存表有效。

      取值范围:0~60000

      默认值:6000

    • COLVERSION

      指定列存存储格式的版本,支持不同存储格式版本之间的切换。

      取值范围:

      1.0:列存表的每列以一个单独的文件进行存储,文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。

      2.0:列存表的每列合并存储在一个文件中,文件名以relfilenode.C1.0命名。

      3.0:列存表的每列合并存储在一个文件中,文件存储在OBS文件系统上,文件名以C1_fileid.0命名。

      默认值:存算一体版本默认值2.0,存算分离版本默认值3.0。

      需注意,OBS冷热表仅支持COLVERSION为2.0格式。

      • 8.1.0集群版本该参数默认值为1.0,8.1.1及以上集群版本该参数默认值为2.0,若集群版本由8.1.0升级至8.1.1或以上版本,该参数默认值也会由1.0变为2.0。
      • 在建列存表时,选择COLVERSION=2.0,相比于1.0存储格式,在以下场景中性能有明显提升:
        • 创建列存宽表场景下,建表时间显著减少。
        • roach备份数据场景下,备份时间显著减少。
        • build、catch up耗时显著减少。
        • 占用磁盘空间大小显著减少。
      • 存算分离版本兼容列存所有版本,建表时需显式指定colversion=1.0/2.0/3.0。当colversion=3.0时建立表为存算分离表,不显式指定colversion时默认创建3.0版本列存表。需注意,创建存算分离表时指定colversion为3.0的同时需要将orientation属性设置为column。
      • V3存算分离表不支持使用ALTER TABLE切换colversion,比如从2.0升级到3.0。
    • analyze_mode

      控制表级自动analyze的方式。

      取值范围:

      • frozen:禁止所有形式的analyze(例外:无统计信息时,还能触发动态采样)。
      • backend:仅允许autovacuum轮询触发的analyze。
      • runtime:仅允许优化器触发的runtime analyze。
      • all:backend和runtime两种自动analyze都允许触发。

      默认值:all

    • incremental_analyze

      控制分区表是否启用增量analyze模式。该参数只对分区表有效,复制表不支持设置。该参数仅9.1.0.100及以上集群版本支持。

      默认值:false

    • SKIP_FPI_HINT

      顺序扫描过程中,若需要写FPW(full page writes)日志时,该参数控制是否跳过设置HintBits操作。

      默认值:false

      设置SKIP_FPI_HINT=true时,在对某表执行checkpoint操作后,若对该表进行顺序扫描,将不再产生Xlog。适用于查询次数较少的中间表,有效减少Xlog的大小,提升查询性能。

    • on_commit_preserve_rows

      ON COMMIT { PRESERVE ROWS | DELETE ROWS } 类似。两者不能同时指定。该参数仅用于全局临时表。

      默认值:true

    • enable_column_autovacuum_garbage

      用于控制是否开启列存表AUTOVACUUM重写CU的逻辑。该参数仅8.2.1.100及以上集群版本支持。

      当前列存轻量化UPDATE与后台列存AUTOVACUUM并发会小概率报错, 可以通过设置该表级参数为off来避免。

      默认值:true

    • secondary_part_column

      指定列存表二级分区列的列名,仅能指定一列作为二级分区列且只适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。

      • 被指定为二级分区的列不能被删除或者修改。
      • 只能在建表时指定二级分区列,建表后不支持修改。
      • 不建议指定分布列作为二级分区列。
      • 指定二级分区列后,该表将会在DN上按照二级分区列进行逻辑上的HASH分区存储,从而有效提升该列等值查询的性能。
    • secondary_part_num

      指定列存表二级分区的数量,仅适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。

      取值范围:1~32

      默认值:8

      • 只有secondary_part_column被指定时,该参数才可以指定。
      • 只能在建表时指定二级分区的数量,建表后不支持修改。
      • 不建议用户随意设置该默认值,否则会影响导入和查询的性能。
    • enable_hstore_opt

      enable_hstore_opt表级参数打开时会默认同时打开enable_hstore表级参数,该参数仅8.3.0及以上集群版本支持。该参数支持v2版本。

      默认值:false

    • bitmap_columns

      bitmap index只适用于新版hstore(hstore_opt表),只有开启表级参数enable_hstore_opt且开启bitmap_columns="指定列",才能生成bitmap index映射关系。该参数仅8.3.0及以上集群版本支持。

    • enable_turbo_store

      用于控制是否创建为turbo表(基于列存表实现)。该参数只对列存表有效。

      默认值:off

      turbo表对部分numeric和varchar数据类型做了整数格式存储优化,以提升对于numeric和varchar数据类型的处理速度。

      • 为获取最佳性能,推荐定义numeric类型时指定precision和scale(p,s)。具体来说,当p小于等于9时,使用4字节整数存储;p小于等于18时,使用8字节整数存储;p小于等于37时,使用16字节整数存储;p大于37或未指定时,则依然使用变长格式存储,其占用较多存储空间,且性能较差。
      • 如果定义varchar类型时指定其最大字节长度n小于等于2字节,使用2字节整数存储;n小于等于4字节时,使用4字节整数存储;依次类推,最大当n小于等于16字节时,使用16字节整数存储。因此对于varchar类型的GROUP BY列或者HashJoin关联列,如果可使用整数存储字符串,建议定义时指定其最大字节长度n小于等于16,可以成倍提升计算性能。
      • turbo表当前不支持时序表、delta表以及轻量化update。
    • cache_policy(该参数仅9.0.2及以上的存算分离版本支持)

      控制了表或者分区表(磁盘)缓存的方式,如果在缓存策略中指定相应参数范围,则会进行热缓存,否则进行冷缓存。热缓存相比冷缓存占用的空间更大,技术上使用更加复杂的替换策略。

      取值范围:

      • ALL: 对整个表进行热缓存。
      • NONE:对整个表进行冷缓存。
      • HPN:N分区表中前N个分区会被热缓存,其余分区进行冷缓存。
      • HPL:P1, P2, ... 分区表中在缓存策略中被指定名称的分区会被热缓存,其余分区进行冷缓存。

      默认值:ALL

      • 对于外表和非分区内表只支持ALL和NONE两种缓存策略。
      • 仅range和list分区内表支持HPN和HPL缓存策略。
  • ON COMMIT { PRESERVE ROWS | DELETE ROWS }

    ON COMMIT选项决定在事务中执行创建临时表操作,当事务提交时,此临时表的后续操作。全局临时表只支持PRESERVE ROWS选项。

    • PRESERVE ROWS(缺省值):提交时不对临时表做任何操作,临时表及其表数据保持不变。
    • DELETE ROWS:提交时删除临时表中数据。
  • COMPRESS | NOCOMPRESS

    创建新表时,需要在CREATE TABLE语句中指定关键字COMPRESS,这样,当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据,生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。

    缺省值:NOCOMPRESS,即不对元组数据进行压缩。

  • DISTRIBUTE BY

    指定表如何在节点之间分布或者复制。

    取值范围:

    • REPLICATION:表的每一行存在所有数据节点(DN)中,即每个数据节点都有完整的表数据。
    • ROUNDROBIN:表的每一行被轮番地发送给各个DN,因此数据会被均匀地分布在各个DN中。(ROUNDROBIN仅8.1.2及以上版本支持)
    • HASH (column_name ) :对指定的列进行Hash,通过映射,把数据分布到指定DN。
      • 当指定DISTRIBUTE BY HASH (column_name)参数时,创建主键和唯一索引必须包含“ column_name”列。
      • 当被参照表指定DISTRIBUTE BY HASH (column_name)参数时,参照表的外键必须包含“ column_name”列。
      • 如果TO GROUP指定为复制表节点组(8.1.2及以上版本支持),DISTRIBUTE BY必须指定为REPLICATION。如果没有指定DISTRIBUTE BY,创建的表会自动设置为复制表。
      • 实时数仓(单机部署)由于只有单DN,因此分布规则会被忽略,也不支持针对分布规则的修改。
    默认值:由GUC参数default_distribution_mode控制。
    • 当default_distribution_mode=roundrobin时,DISTRIBUTE BY的默认值按如下规则选取:
      1. 若建表时包含主键/唯一约束,则选取HASH分布,分布列为主键/唯一约束对应的列。
      2. 若建表时不包含主键/唯一约束,则选取ROUNDROBIN分布。
    • 当default_distribution_mode=hash时,DISTRIBUTE BY的默认值按如下规则选取:
      1. 若建表时包含主键/唯一约束,则选取HASH分布,分布列为主键/唯一约束对应的列。
      2. 若建表时不包含主键/唯一约束,但存在数据类型支持作分布列的列,则选取HASH分布,分布列为第一个数据类型支持作分布列的列。
      3. 若建表时不包含主键/唯一约束,也不存在数据类型支持作分布列的列,选取ROUNDROBIN分布。
    以下数据类型支持作为分布列:
    • INTEGER TYPES:TINYINT,SMALLINT,INT,BIGINT,NUMERIC/DECIMAL
    • CHARACTER TYPES:CHAR,BPCHAR,VARCHAR,VARCHAR2,NVARCHAR2,TEXT
    • DATE/TIME TYPES:DATE,TIME,TIMETZ,TIMESTAMP,TIMESTAMPTZ,INTERVAL,SMALLDATETIME

    在建表时,选择分布列和分区键可对SQL查询性能产生重大影响。因此,需要根据一定策略选择合适的分布列和分区键。

    • 选择合适的分布列
      对于采用散列(Hash)方式的数据分布表,一个合适的分布列应将一个表内的数据,均匀分散存储在多个DN内,避免出现数据倾斜现象(即多个DN内数据分布不均)。请按照如下原则判定合适的分布列:
      1. 判断是否已发生数据倾斜现象。

        连接数据库,执行如下语句,查看各DN内元组数目。命令中的斜体部分tablename,请填入待分析的表名。

        SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM tablename GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count DESC;

        如果各DN内元组数目相差较大(如相差数倍、数十倍),则表明已发生数据倾斜现象,请按照下面原则调整分布列。

      2. 重新选择分布列,可通过ALTER TABLE语句调整分布列,选择原则如下:

        分布列的列值应比较离散,以便数据能够均匀分布到各个DN。例如,考虑选择表的主键为分布列,如在人员信息表中选择身份证号码为分布列。

        在满足上面原则的情况下,考虑选择查询中的连接条件为分布列,以便Join任务能够下推到DN中执行,且减少DN之间的通信数据量。

      3. 如果找不到一个合适的分布列,使数据能够均匀分布到各个DN,那么可以考虑使用REPLICATION或ROUNDROBIN的数据分布方式。由于REPLICATION的数据分布方式会在每个DN中存放完整的数据,因此在表较大且找不到合适的分布列时,推荐使用ROUNDROBIN的数据分布方式。(ROUNDROBIN分布方式8.1.2及以上版本支持)
    • 选择合适的分区键

      数据分区功能,可根据表的一列或者多列,将要插入表的记录分为若干个范围(这些范围在不同的分区里没有重叠)。然后为每个范围创建一个分区,用来存储相应的数据。

      调整分区键,使每次查询结果尽可能存储在相同或者最少的分区内(称为“分区剪枝”),通过获取连续I/O大幅度提升查询性能。

      实际业务中,经常将时间作为查询对象的过滤条件,因此,可考虑选择时间列为分区键,键值范围可根据总数据量、一次查询数据量调整。

  • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

    TO GROUP指定创建表所在的Node Group,目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用,一般用户不应该使用。

    在逻辑集群模式下,如果不指定TO GROUP,表默认会创建在逻辑集群用户关联的节点组中;如果用户没有管理逻辑集群(例如管理员用户或其他普通用户),表默认会创建在第一个逻辑集群中(pgxc_group中oid最小的逻辑集群是第一个逻辑集群)。

    如果TO GROUP指定的节点组是复制表节点组,表将创建在所有CN和DN节点上,但复制表数据将只分布在复制表节点组包含的DN节点上。

    存算分离3.0版本支持只读逻辑集群,如果未绑定到只读逻辑集群的用户建表语句中TO GROUP指定的是只读逻辑集群,则创建表会报错;但绑定到只读逻辑集群的用户创建表会遵循未绑定逻辑集群用户的建表规则,将表创建到由GUC参数default_storage_nodegroup指定的逻辑集群上。如果default_storage_nodegroup是installation, 表会创建在第一个逻辑集群中。

  • COMMENT [=] 'text'

    COMMENT子句可在创建表时指定表注释。

  • CONSTRAINT constraint_name

    列约束或表约束的名字。可选的约束子句用于声明约束,新行或者更新的行必须满足这些约束才能成功插入或更新。

    定义约束有两种方法:

    • 列约束:作为一个列定义的一部分,仅影响该列。
    • 表约束:不和某个列绑在一起,可以作用于多个列。
  • NOT NULL

    字段值不允许为NULL。

  • NULL

    字段值允许为NULL ,这是缺省值。

    这个子句只是为和非标准SQL数据库兼容。不建议使用。

  • CHECK ( expression )

    CHECK约束声明一个布尔表达式,每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功,否则会抛出一个异常并且不会修改数据库。

    声明为字段约束的检查约束应该只引用该字段的数值,而在表约束里出现的表达式可以引用多个字段。

    expression表达式中,如果存在“<>NULL”或“!=NULL”,这种写法是无效的,需要写成“is NOT NULL”。

  • DEFAULT default_expr

    DEFAULT子句给字段指定缺省值。该数值可以是任何不含变量的表达式(不允许使用子查询和对本表中的其他字段的交叉引用)。缺省表达式的数据类型必须和字段类型匹配。

    缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

  • ON UPDATE on_update_expr

    ON UPDATE子句给字段指定时间戳函数。ON UPDATE子句支持指定的字段类型必须为timestamp类型或者timestamptz类型。

    当执行含有update操作的SQL语句时,自动更新此列为时间戳函数所代表的时间。

    on_update_expr时间戳函数仅支持CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, LOCALTIME, LOCALTIMESTAMP。

  • COMMENT 'text'

    COMMENT子句可以指定列的注释。

  • UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters

    UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] ( column_name [, ... ] ) index_parameters

    UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

    其中[ NULLS [ NOT ] DISTINCT | NULLS IGNORE ]字段用来指定Unique唯一索引中索引列NULL值的处理方式。

    默认值:该参数默认缺省,即NULL值可重复插入。

    在对插入的新数据和表中原始数据进行列的等值比较时,对于NULL值有以下三种处理方式:

    • NULLS DISTINCT:NULL值互不相等,即NULL值可重复插入。
    • NULLS NOT DISTINCT:NULL值相等。若索引列全为NULL,则NULL值不可重复插入;部分索引列为NULL,只有非NULL值不相等,才可成功插入数据。
    • NULLS IGNORE:在等值比较时跳过NULL值。若索引列全为NULL,则NULL值可重复插入;部分索引列为NULL,只有非NULL值不相等,才可成功插入数据。

    三种处理方式具体的行为如下表所示:

    表3 唯一索引中索引列NULL值的处理方式

    字段控制

    索引列全为NULL

    部分索引列为NULL

    NULLS DISTINCT

    可重复插入

    可重复插入

    NULLS NOT DISTINCT

    不可重复插入

    非NULL值相等,不可插入;非NULL值不相等,则插入成功

    NULLS IGNORE

    可重复插入

    非NULL值相等,不可插入;非NULL值不相等,则插入成功

    如果没有声明DISTRIBUTE BY REPLICATION,则唯一约束的列集合中必须包含分布列。

  • PRIMARY KEY index_parameters

    PRIMARY KEY ( column_name [, ... ] ) index_parameters

    主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

    一个表只能声明一个主键。

    如果没有声明DISTRIBUTE BY REPLICATION,则主键约束的列集合中必须包含分布列。

  • REFERENCES reftable [ ( refcolumn ) ]

    外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。被参考列应该是被参考表中的唯一字段或主键。

    由于GaussDB(DWS)不会进行外键约束检查,使用外键约束时需通过函数check_foreign_key_constraint来检测外键表中的数据是否满足外键约束。

  • DEFERRABLE | NOT DEFERRABLE

    这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可推迟约束可以推迟到事务结尾使用SET CONSTRAINTS命令检查。缺省是NOT DEFERRABLE。目前,只有行存的UNIQUE约束和主键约束可以接受这个子句。所有其他约束类型都是不可推迟的。

  • PARTIAL CLUSTER KEY

    局部聚簇存储,列存表导入数据时按照指定的列(单列或多列),进行局部排序。

  • INITIALLY IMMEDIATE | INITIALLY DEFERRED

    如果约束是可推迟的,则这个子句声明检查约束的缺省时间。

    • 如果约束是INITIALLY IMMEDIATE(缺省),则在每条语句执行之后就立即检查它;
    • 如果约束是INITIALLY DEFERRED ,则只有在事务结尾才检查它。

    约束检查的时间可以用SET CONSTRAINTS命令修改。

示例

创建一个存算分离V3表(仅存算分离3.0版本支持)

1
2
3
4
5
6
7
8
CREATE TABLE  public.t1 
( 
id integer not null,  
data integer, 
age integer 
)  
WITH (ORIENTATION =COLUMN, COLVERSION =3.0) 
DISTRIBUTE BY ROUNDROBIN;

创建表的时候指定缓存策略(仅存算分离3.0版本集群支持)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE TABLE Sports
(
    N_NATIONKEY  INT NOT NULL
  , N_NAME       CHAR(25) NOT NULL
  , N_REGIONKEY  INT NOT NULL
  , N_COMMENT    VARCHAR(152)
) WITH (orientation = column, colversion = 3.0, cache_policy = 'HPL: Balls, Basketball')
tablespace cu_obs_tbs
DISTRIBUTE BY ROUNDROBIN
partition by list(N_NAME)
(
  partition Balls values ('Basketball', 'football', 'badminton'),
  partition Athletics values ('High jump', 'long jump', 'javelin'),
  partition Water_Sports values ('Surfing', 'diving', 'swimming'),
  partition Shooting values ('air guns', 'Rifles', 'archery'),
  partition rest values (DEFAULT)
);

为表定义唯一列约束:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT          , 
    C_PHONE       CHAR(15)     , 
    C_ACCTBAL     DECIMAL(15,2)  
)
DISTRIBUTE BY HASH(C_CUSTKEY);

为表定义主键表约束。可以在表的一列或多列上定义主键表约束:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT       , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT          , 
    C_PHONE       CHAR(15)     , 
    C_ACCTBAL     DECIMAL(15,2)   , 
    CONSTRAINT C_CUSTKEY_KEY PRIMARY KEY(C_CUSTKEY,C_NAME)
)
DISTRIBUTE BY HASH(C_CUSTKEY,C_NAME);

定义CHECK列约束:

1
2
3
4
5
6
7
8
CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT NOT NULL  CHECK (C_NATIONKEY > 0)  
)
DISTRIBUTE BY HASH(C_CUSTKEY);

定义CHECK表约束:

CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)      , 
    C_ADDRESS     VARCHAR(40)      , 
    C_NATIONKEY   INT              , 
    CONSTRAINT C_CUSTKEY_KEY2 CHECK(C_CUSTKEY > 0 AND C_NAME <> '')
)
DISTRIBUTE BY HASH(C_CUSTKEY);

创建列存表指定存储格式和压缩方式:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE customer_address
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH,COLVERSION=2.0)
DISTRIBUTE BY HASH (ca_address_sk);

使用DEFAULT为列W_STATE声明默认值:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE warehouse_t
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)   UNIQUE DEFERRABLE,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)            DEFAULT 'GA',
    W_ZIP                     CHAR(10)                       
);

以like方式创建一个表CUSTOMER_bk :

1
CREATE TABLE CUSTOMER_bk (LIKE CUSTOMER INCLUDING ALL);

相关文档