网络
虚拟私有云 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-24 GMT+08:00
分享

ALTER TABLE

功能描述

修改表,包括修改表的定义、重命名表、重命名表中指定的列、重命名表的约束、设置表的所属模式、添加/更新多个列、打开/关闭行访问控制开关

注意事项

  • 只有表的所有者或者被授予了表ALTER权限的用户有权限执行ALTER TABLE命令,系统管理员默认拥有此权限。若要修改表的所有者或者修改表的模式,当前用户必须是该表的所有者或者系统管理员,且该用户是新的所有角色的直接或间接成员。
  • 不支持修改存储参数ORIENTATION。
  • SET SCHEMA操作不支持修改为系统内部模式,当前仅支持用户模式之间的修改。
  • 列存表支持PARTIAL CLUSTER KEY,不支持外键表级约束。列存表从8.1.1版本开始支持主键和唯一表级约束。
  • 行存REPLICATION分布表不支持将系统列设置为主键。
  • 列存表支持添加字段ADD COLUMN,修改字段的数据类型ALTER TYPE,设置单个字段的收集目标SET STATISTICS,支持更改表名字,支持删除字段DROP COLUMN;对于添加的字段和修改的字段类型要求是列存支持的数据类型;ALTER TYPE的USING选项只支持常量表达式和涉及本字段的表达式,暂不支持涉及其他字段的表达式。
  • 列存表支持的字段约束包括NULL、NOT NULL和DEFAULT常量值;对字段约束的修改当前支持对DEFAULT值的修改(SET DEFAULT)、删除(DROP DEFAULT)和NOT NULL约束的删除;支持对非空约束NULL/NOT NULL的删除(DROP NOT NULL),暂不支持对非空约束NULL/NOT NULL的修改(SET NOT NULL)。
  • 修改列存表存储参数COLVERSION或者enable_delta时,不能与其他ALTER操作同时进行。
  • 不支持增加自增列,或者增加DEFAULT值中包含nextval()表达式的列。
  • 不支持对HDFS表、外表、临时表开启行访问控制开关。
  • 通过约束名删除PRIMARY KEY约束时,不会删除NOT NULL约束。如有需要,可手动删除NOT NULL约束。
  • OBS冷热表不支持ALTER RESET的cold_tablespace,storage_policy参数,不支持修改COLVERSION为1.0。
  • 可将原列存COLVERSION为2.0表ALTER为OBS冷热表,需同时增加cold_tablespace和storage_policy参数。
  • ALTER TABLE支持针对RELOPTIONS的storage_policy的选项更改。冷热切换策略发生更改后,不改变现有冷数据的冷热属性,只改变下一次执行冷热切换的规则,下一次执行冷热切换命令时将按新规则进行冷热切换。
  • 对表执行以下ALTER TABLE操作时会触发表重建(表重建过程中会先把数据转储到一个新的数据文件中,重建完成之后会删除原始文件),当表比较大时,重建会消耗较多的磁盘空间。当磁盘空间不足时,要谨慎对待大表ALTER TABLE操作,防止触发集群只读。
    • 修改列数据类型。
    • 行存表增加列(包括oid列)。
    • 列存表修改COLVERSION。
    • 列存表增加列指定DEFAULT常量值,DEFAULT值中包含volatile函数以及DEFAULT值非NULL且不属于特定数据类型中的任何一个。
  • 避免在业务高峰期执行ALTER TABLE/ALTER TABLE PARTITION(增删改查、DROP PARTITION)、TRUNCATE操作,避免有长SQL阻塞AlTER、TRUNCATE操作或SQL业务被ALTER、TRUNCATE阻塞。
  • 更多开发设计规范参见总体开发设计规范

语法格式

  • 修改表的定义。
    1
    2
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
        action [, ... ];
    
    其中具体表操作action可以是以下子句之一:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    column_clause
        | ADD table_constraint [ NOT VALID ]
        | ADD table_constraint_using_index
        | VALIDATE CONSTRAINT constraint_name
        | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
        | CLUSTER ON index_name
        | SET WITHOUT CLUSTER
        | SET ( {storage_parameter = value} [, ... ] )
        | RESET ( storage_parameter [, ... ] )
        | OWNER TO new_owner
        | SET TABLESPACE new_tablespace
        | SET {COMPRESS|NOCOMPRESS}
        | DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } }
        | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
        | ADD NODE ( nodename [, ... ] )
        | DELETE NODE ( nodename [, ... ] )
        | DISABLE TRIGGER [ trigger_name | ALL | USER ]
        | ENABLE TRIGGER [ trigger_name | ALL | USER ]
        | ENABLE REPLICA TRIGGER trigger_name
        | ENABLE ALWAYS TRIGGER trigger_name
        | DISABLE ROW LEVEL SECURITY
        | ENABLE ROW LEVEL SECURITY
        | FORCE ROW LEVEL SECURITY
        | NO FORCE ROW LEVEL SECURITY
        | REFRESH STORAGE
    
    • ADD table_constraint [ NOT VALID ]

      给表增加一个新的约束。与NOT VALID选项组合时,这种约束仅在对外键和CHECK类约束有效。 如果约束条件增加了NOT VALID选项后,表中已有记录是否满足初始约束检查会被跳过。

    • ADD table_constraint_using_index

      根据已有唯一索引为表增加主键约束或唯一约束。

    • VALIDATE CONSTRAINT constraint_name

      验证一个外键或是一个使用NOT VALID选项创建的检查类约束,通过扫描全表来保证所有记录都符合约束条件。如果约束已标记为有效时,什么操作也不会发生。

    • DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

      删除一个表上的约束。

    • CLUSTER ON index_name

      为将来的CLUSTER操作选择默认索引。实际上并没有重新盘簇化处理该表。

    • SET WITHOUT CLUSTER

      从表中删除最新使用的CLUSTER索引。这样会影响将来那些没有声明索引的集群操作。

    • SET ( {storage_parameter = value} [, ... ] )

      修改表的一个或多个存储参数。

    • RESET ( storage_parameter [, ... ] )

      重置表的一个或多个存储参数。与SET一样,根据参数的不同可能需要重写表才能获得想要的效果。

    • OWNER TO new_owner

      将表、序列、视图的属主改变成指定的用户。

    • SET {COMPRESS|NOCOMPRESS}

      修改表的压缩特性。表压缩特性的改变只会影响后续批量插入的数据的存储方式,对已有数据的存储毫无影响。也就是说,表压缩特性的修改会导致该表中同时存在着已压缩和未压缩的数据。

    • DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } }

      修改表的分布方式,在修改表分布信息的同时会将表数据在物理上按新分布方式重新分布,修改完成后建议对被修改表执行ANALYZE,以便收集全新的统计信息。

      • 本操作属于重大变更操作,涉及表分布信息的修改以及数据的物理重分布,修改过程中会阻塞业务,修改完成后原有业务的执行计划会发生变化,请按照正规变更流程进行。
      • 本操作属于资源密集操作,针对大表的分布方式修改,建议在计算和存储资源充裕情况下进行,保证整个集群和原表所在表空间有足够的剩余空间能存储一张与原表同等大小且按照新分布方式进行分布的表。
    • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

      此语法仅在扩展模式(GUC参数support_extended_features为on时)下可用。该模式谨慎打开,主要供内部扩容工具使用,一般用户不应使用该模式。

    • ADD NODE ( nodename [, ... ] )

      此语法主要供内部扩容工具使用,一般用户不建议使用。

    • DELETE NODE ( nodename [, ... ] )

      此语法主要供内部缩容工具使用,一般用户不建议使用。

    • DISABLE TRIGGER [ trigger_name | ALL | USER ]

      禁用trigger_name所表示的单个触发器,或禁用所有触发器,或仅禁用用户触发器(此选项不包括内部生成的约束触发器,例如,可延迟唯一性和排除约束的约束触发器)。

      应谨慎使用此功能,因为如果不执行触发器,则无法保证原先期望的约束的完整性。

    • ENABLE TRIGGER [ trigger_name | ALL | USER ]

      启用trigger_name所表示的单个触发器,或启用所有触发器,或仅启用用户触发器。

    • ENABLE REPLICA TRIGGER trigger_name

      触发器触发机制受配置变量session_replication_role的影响,当复制角色为“origin”(默认值)或“local”时,将触发简单启用的触发器。

      配置为ENABLE REPLICA的触发器仅在会话处于“replica”模式时触发。

    • ENABLE ALWAYS TRIGGER trigger_name

      无论当前复制模式如何,配置为ENABLE ALWAYS的触发器都将触发。

    • DISABLE/ENABLE ROW LEVEL SECURITY

      开启或关闭表的行访问控制开关。

      当开启行访问控制开关时,如果未在该数据表定义相关行访问控制策略,数据表的行级访问将不受影响;如果关闭表的行访问控制开关,即使定义了行访问控制策略,数据表的行访问也不受影响。详细信息参见CREATE ROW LEVEL SECURITY POLICY章节。

    • NO FORCE/FORCE ROW LEVEL SECURITY

      强制开启或关闭表的行访问控制开关。

      默认情况,表所有者不受行访问控制特性影响,但当强制开启表的行访问控制开关时,表的所有者(不包含系统管理员用户)会受影响。系统管理员可以绕过所有的行访问控制策略,不受影响。

    • REFRESH STORAGE

      根据OBS冷热表storage_policy所定义的规则,将符合条件的本地热分区切换为存储在OBS上的冷分区。

      例如创建OBS冷热表时,设置storage_policy 为 'LMT:10',则在执行该操作时可将10日前无修改的分区切为冷存储,存至OBS中。

    • 其中列相关的操作column_clause可以是以下子句之一:
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]    
          | MODIFY [ COLUMN ] column_name data_type
          | MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
          | MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NULL    
          | MODIFY [ COLUMN ] column_name DEFAULT default_expr
          | MODIFY [ COLUMN ] column_name COMMENT comment_text
          | DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]    
          | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]    
          | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }    
          | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL    
          | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer    
          | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))    
          | ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ( { { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] } [, ...] ) [ USING method ] [ COMMENT 'text' ] LOCAL [ ( { PARTITION index_partition_name } [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ]
          | ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ USING method ] [ COMMENT 'text' ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ WHERE predicate ]
          | DROP { INDEX | KEY } index_name
          | CHANGE [ COLUMN ] old_column_name new_column_name data_type [ [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] |
              [ CONSTRAINT constraint_name ] NULL | DEFAULT default_expr | COMMENT 'text' ]
          | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))    
          | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )    
          | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )    
          | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
      
      • ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]

        向表中增加一个新的字段。用ADD COLUMN增加一个字段,所有表中现有行都初始化为该字段的缺省值(如果没有声明DEFAULT子句,值为NULL)。

      • ADD ( { column_name data_type [ compress_mode ] } [, ...] )

        向表中增加多列。

      • MODIFY [ COLUMN ] column_name data_type

        修改表已存在字段的数据类型。需注意:不支持修改分布列数据类型。

      • MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]

        为表的某列添加not null约束,列存表暂不支持。

      • MODIFY [ COLUMN ] column_name [ CONSTRAINT constraint_name ] NULL

        为表的某列移除not null约束。

      • MODIFY [ COLUMN ] column_name DEFAULT default_expr

        修改表的default值。

      • MODIFY [ COLUMN ] column_name COMMENT comment_text

        修改表的注释信息。

      • DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

        从表中删除一个字段,和这个字段相关的索引和表约束也会被自动删除。如果任何表之外的对象依赖于这个字段,必须声明CASCADE ,比如外键参考、视图等。

        DROP COLUMN命令并不是物理上把字段删除,而只是简单地把它标记为对SQL操作不可见。随后对该表的插入和更新将在该字段存储一个NULL。因此,删除一个字段是很快的,但是它不会立即释放表在磁盘上的空间,因为被删除了的字段占据的空间还没有回收。这些空间将在执行VACUUM时而得到回收。

      • ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

        改变表字段的数据类型,只允许相同大类的类型转换(数值之间,字符串之间,时间之间等)。该字段涉及的索引和简单的表约束将被自动地转换为使用新的字段类型,方法是重新分析最初提供的表达式。

        ALTER TYPE要求重写整个表的特性有时候是一个优点,因为重写的过程消除了表中没用的空间。比如,要想立刻回收被一个已经删除的字段占据的空间,最快的方法是

        1
        ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
        

        这里的anycol是任何在表中还存在的字段,而anytype是和该字段的原类型一样的类型。这样的结果是在表上没有任何可见的语意的变化,但是这个命令强制重写,这样就删除了不再使用的数据。

      • ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }

        为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令,它们不会修改表中已经存在的行。也可以为视图创建缺省,这个时候它们是在视图的ON INSERT规则应用之前插入到INSERT句中的。

      • ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

        修改一个字段是否允许NULL值或者拒绝NULL值。如果表在字段中包含非NULL,则只能使用SET NOT NULL。

      • ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer

        为随后的ANALYZE操作设置针对每个字段的统计收集目标。目标的范围可以在0到10000之内设置。设置为-1时表示重新恢复到使用系统缺省的统计目标。

      • {ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, ...]))

        用于添加和删除多列统计信息声明(不实际进行多列统计信息收集),以便在后续进行全表或全库analyze时进行多列统计信息收集。每组多列统计信息最多支持32列。不支持添加/删除多列统计信息声明的表:系统表、外表。

      • ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ( { { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] } [, ...] ) [ USING method ] [ COMMENT 'text' ] LOCAL [ ( { PARTITION index_partition_name } [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ]

        为表的分区表创建索引,具体参数可参考CREATE INDEX

      • ADD { INDEX | UNIQUE [ INDEX ] } [ index_name ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ USING method ] [ COMMENT 'text' ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ WHERE predicate ]

        在表上创建索引,具体参数可参考CREATE INDEX

      • DROP { INDEX | KEY } index_name

        删除一个表上的索引。

      • CHANGE [ COLUMN ] old_column_name new_column_name data_type [ [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] |

        [ CONSTRAINT constraint_name ] NULL | DEFAULT default_expr | COMMENT 'text' ]

        修改表中列信息,可将旧列名修改成新列名,以及修改列字段信息。

      • ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )

        ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

        设置/重置属性选项。

        属性选项定义的参数有:n_distinct、n_distinct_inherited和cstore_cu_sample_ratio。n_distinct设置并固定表的distinct值统计信息,n_distinct_inherited设置并固定继承表的distinct值统计信息,cstore_cu_sample_ratio设置对cstore列存表进行analyze时所选CU的比例。目前,禁止SET/RESET n_distinct_inherited参数。

        • n_distinct

          手动设置该列的distinct值统计信息。

          取值范围:-1.0 ~ INT_MAX

          默认值:0,表示不设置。

        • n_distinct_inherited

          手动设置继承表的该列的distinct值统计信息。

          取值范围:-1.0 ~ INT_MAX

          默认值:0,表示不设置。

        • cstore_cu_sample_ratio

          设置列存表执行analyze,计算需要采样的CU个数时,需要扩大的倍数。

          取值范围:1.0 ~ 10000.0

          默认值:1.0

      • ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

        为一个字段设置存储模式。这个设置控制这个字段是内联保存还是保存在一个附属的表里,以及数据是否要压缩。仅支持对行存表的设置;对列存表没有意义,执行时报错。SET STORAGE本身并不改变表上的任何东西,只是设置将来的表操作时,建议使用的策略。

      • 其中列约束column_constraint为:
        1
        2
        3
        4
        5
        6
        7
        8
        [ CONSTRAINT constraint_name ]
            { NOT NULL |
              NULL |
              CHECK ( expression ) |
              DEFAULT default_expr  |
              UNIQUE index_parameters |
              PRIMARY KEY index_parameters }
            [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
        
      • 其中列的压缩可选项compress_mode为:
        1
        [ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ]
        
    • 其中根据已有唯一索引为表增加主键约束或唯一约束table_constraint_using_index为:
      1
      2
      3
      [ CONSTRAINT constraint_name ]
          { UNIQUE | PRIMARY KEY } USING INDEX index_name
          [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
      
    • 其中表约束table_constraint为:
      1
      2
      3
      4
      5
      6
      [ CONSTRAINT constraint_name ]
          { CHECK ( expression ) |
            UNIQUE ( column_name [, ... ] ) index_parameters |
            PRIMARY KEY ( column_name [, ... ] ) index_parameters }
           
          [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
      
      其中索引参数index_parameters为:
      1
      2
      [ WITH ( {storage_parameter = value} [, ... ] ) ]
          [ USING INDEX TABLESPACE tablespace_name ]
      
  • 修改表已存在字段的数据类型,可同时修改空约束,default值和注释信息,只允许相同大类的类型转换(数值之间,字符串之间,时间之间等)。
    1
    2
    3
    ALTER TABLE [ IF EXISTS ] table_name
        MODIFY ( { column_name data_type | [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] |
            [ CONSTRAINT constraint_name ] NULL | DEFAULT default_expr | COMMENT 'text' } [, ...] );
    
  • 重命名表。对名字的修改不会影响所存储的数据。暂不支持新表名前带有原表的schema名。
    1
    2
    ALTER TABLE [ IF EXISTS ] table_name 
        RENAME TO new_table_name;
    
  • 重命名表中指定的列。
    1
    2
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
        RENAME [ COLUMN ] column_name TO new_column_name;
    
  • 重命名表的约束。
    1
    2
    ALTER TABLE { table_name [*] | ONLY table_name | ONLY ( table_name ) }
        RENAME CONSTRAINT constraint_name TO new_constraint_name;
    
  • 设置表的所属模式。
    1
    2
    ALTER TABLE [ IF EXISTS ] table_name 
        SET SCHEMA new_schema;
    
    • 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列,需要将序列删除,重建,或者取消拥有关系, 才能将表schema更改成功。
    • 要修改一个表的模式,用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表,用户必须同时又是父表的所有者。要修改所有者,用户还必须是新的所有角色的直接或间接成员,并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做出了重建和删除表之外的事情。不过,系统管理员可以以任何方式修改任意表的所有权限。
    • 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如,可以在一个命令里增加几个字段或修改几个字段的类型。对于大表,此种操作带来的效率提升更明显,原因在于只需要对该大表做一次处理。
    • 增加一个CHECK或NOT NULL约束将会扫描该表,以保证现有的行符合约束要求。
    • 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说,这个操作可能会花很长时间,并且它还临时需要两倍的磁盘空间。
  • 添加多个列。
    1
    2
    ALTER TABLE [ IF EXISTS ] table_name
        ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
    
  • 更新多个列。
    1
    2
    ALTER TABLE [ IF EXISTS ] table_name 
        MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
    

参数说明

  • IF EXISTS

    如果不存在相同名称的表,不会抛出一个错误,而会发出一个通知,告知表不存在。

  • table_name [*] | ONLY table_name | ONLY ( table_name )

    table_name是需要修改的表名。

    若声明了ONLY选项,则只有那个表被更改。若未声明ONLY,该表及其所有子表都将会被更改。另外,可以在表名称后面显示地增加*选项来指定包括子表,即表示所有后代表都被扫描,这是默认行为。

  • constraint_name

    约束的名字。约束名长度不超过63个字符。

  • index_name

    索引名称。

  • storage_parameter

    表的存储参数的名字。

    分区管理新增的两个选项:

    • PERIOD(interval类型)

      设置分区管理中自动创建分区的周期。

      PERIOD的范围要求以及开启该功能的约束请参考▪PERIOD

      • 在建表时,如果没有设置该参数,可以通过set的方式添加该参数,并开启自动创建分区功能;如果之前已经设置该参数,则通过set的方式修改该参数。
      • 用户可以通过reset该参数的方式关闭自动创建分区功能,但是在自动删除分区功能存在的情况下,不支持关闭自动创建分区功能。
    • TTL(interval类型)

      设置分区管理中自动删除分区的分区过期时间。

      TTL的范围要求以及开启该功能的约束请参考▪TTL

      • 在建表时,如果没有设置该参数,可以通过set的方式添加该参数,并开启自动删除分区功能;如果之前已经设置该参数,则通过set的方式修改该参数。
      • 用户可以通过reset该参数的方式关闭自动删除分区功能。
  • new_owner

    表所属新的拥有者的名字。

  • new_tablespace

    表所属新的表空间名字。

  • column_namecolumn_1_name, column_2_name

    现存的或新字段的名称。

  • data_type

    新字段的类型,或者现存字段的新类型。

  • compress_mode

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

  • collation

    字段排序规则名称。可选字段COLLATE指定了新字段的排序规则,如果省略,排序规则为新字段的默认类型。

  • USING expression

    USING子句声明如何从旧的字段值里计算新的字段值;如果省略,缺省从旧类型向新类型的赋值转换。如果从旧数据类型到新类型没有隐含或者赋值的转换,则必须提供一个USING子句。

    ALTER TYPE的USING选项实际上可以声明涉及该行旧值的任何表达式,即它可以引用除了正在被转换的字段之外其他的字段。这样,就可以用ALTER TYPE语法做非常普遍性的转换。因为这个灵活性,USING表达式并没有作用于该字段的缺省值(如果有的话),结果可能不是缺省表达式要求的常量表达式。这就意味着如果从旧类型到新类型没有隐含或者赋值转换的话,即使存在USING子句,ALTER TYPE也可能无法把缺省值转换成新的类型。在这种情况下,应该用DROP DEFAULT先删除缺省,执行ALTER TYPE,然后使用SET DEFAULT增加一个合适的新缺省值。类似的考虑也适用于涉及该字段的索引和约束。

  • NOT NULL | NULL

    设置列是否允许空值。

  • integer

    带符号的整数常值。当使用PERCENT时表示按照表数据的百分比收集统计信息,integer的取值范围为0-100。

  • attribute_option

    属性选项。

  • PLAIN | EXTERNAL | EXTENDED | MAIN

    字段存储模式。

    • PLAIN必须用于定长的数值(比如integer)并且是内联的、不压缩的。
    • MAIN用于内联、可压缩的数据。
    • EXTERNAL用于外部保存、不压缩的数据。使用EXTERNAL将令在text和bytea字段上的子字符串操作更快,但付出的代价是增加了存储空间。
    • EXTENDED用于外部的压缩数据,EXTENDED是大多数支持非PLAIN存储的数据的缺省。
  • CHECK ( expression )

    每次将要插入的新行或者将要被更新的行必须使表达式结果为真才能成功,否则会抛出一个异常并且不会修改数据库。

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

    目前,CHECK表达式不能包含子查询也不能引用除当前行字段之外的变量。

  • DEFAULT default_expr

    给字段指定缺省值。

    缺省表达式的数据类型必须和字段类型匹配。

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

    default_expr中若使用后缀操作符(如!),需使用括号括起来。

  • UNIQUE index_parameters

    UNIQUE ( column_name [, ... ] ) index_parameters

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

  • PRIMARY KEY index_parameters

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

    主键约束表明表中的一个或者一些字段只能包含唯一(不重复)的非NULL值。

  • DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

    设置该约束是否可推迟,列存暂不支持。

    • DEFERRABLE:可以推迟到事务结尾使用SET CONSTRAINTS命令检查。
    • NOT DEFERRABLE:在每条命令之后马上检查。
    • INITIALLY IMMEDIATE:那么每条语句之后就立即检查它。
    • INITIALLY DEFERRED:只有在事务结尾才检查它。
  • WITH ( {storage_parameter = value} [, ... ] )

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

  • COMPRESS|NOCOMPRESS
    • NOCOMPRESS:如果指定关键字NOCOMPRESS则不会修改表的现有压缩特性。
    • COMPRESS:如果指定COMPRESS关键字,则对该表进行批量插入元组时触发该特性。
  • new_table_name

    修改后新的表名称。

  • new_column_name

    表中指定列修改后新的列名称。

  • new_constraint_name

    修改后表约束的新名称。

  • new_schema

    修改后新的模式名称。

  • CASCADE

    级联删除依赖于被依赖字段或者约束的对象(比如引用该字段的视图)。

  • RESTRICT

    如果字段或者约束还有任何依赖的对象,则拒绝删除该字段。这是缺省行为。

  • schema_name

    表所在的模式名称。

表操作示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DROP TABLE IF EXISTS CUSTOMER;
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)
)
DISTRIBUTE BY HASH(C_CUSTKEY);

根据已有唯一索引为表增加主键约束或唯一约束。

先给表CUSTOMER创建唯一索引CUSTOMER_constraint1,然后根据已有唯一索引增加主键约束,并对前面创建的索引rename:

1
2
CREATE UNIQUE INDEX CUSTOMER_constraint1 ON CUSTOMER(C_CUSTKEY);
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_constraint2 PRIMARY KEY USING INDEX CUSTOMER_constraint1;

重命名表约束:

1
ALTER TABLE CUSTOMER RENAME CONSTRAINT CUSTOMER_constraint2 TO CUSTOMER_constraint;

删除表约束:

1
ALTER TABLE CUSTOMER DROP CONSTRAINT CUSTOMER_constraint;

给表增加一个索引:

1
ALTER TABLE CUSTOMER ADD INDEX CUSTOMER_index(C_CUSTKEY);

删除表索引:

1
2
ALTER TABLE CUSTOMER DROP INDEX CUSTOMER_index;
ALTER TABLE CUSTOMER DROP KEY CUSTOMER_index;

给表中某一列添加索引:

1
ALTER TABLE CUSTOMER ADD c_address_id varchar(20) CONSTRAINT ca_address_index CHECK (c_address_id > 0);

给表新增主键约束:

1
ALTER TABLE CUSTOMER ADD PRIMARY KEY(C_CUSTKEY);

重命名表:

1
ALTER TABLE CUSTOMER RENAME TO CUSTOMER_t;

创建列存表:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
DROP TABLE IF EXISTS customer_address;
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);

向在一个列存表中添加局部聚簇列:

1
ALTER TABLE customer_address ADD CONSTRAINT customer_address_cluster PARTIAL CLUSTER KEY(ca_address_sk);

删除一个列存表中的局部聚簇列:

1
ALTER TABLE customer_address DROP CONSTRAINT customer_address_cluster;

切换列存表的存储格式:

1
ALTER TABLE customer_address SET (COLVERSION = 1.0);

修改表的分布方式:

1
ALTER TABLE customer_address DISTRIBUTE BY REPLICATION;
修改表模式:
1
2
CREATE SCHEMA tpcds;
ALTER TABLE customer_address SET SCHEMA tpcds;

单表冷热切换:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS cold_hot_table;
CREATE TABLE cold_hot_table(
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_ID               CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      )
WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:30')
DISTRIBUTE BY HASH (W_WAREHOUSE_ID)
PARTITION BY RANGE(W_STREET_ID)(
    PARTITION P1 VALUES LESS THAN(100000),
    PARTITION P2 VALUES LESS THAN(200000),
    PARTITION P3 VALUES LESS THAN(300000),
    PARTITION P4 VALUES LESS THAN(MAXVALUE)
)ENABLE ROW MOVEMENT;

ALTER TABLE cold_hot_table REFRESH STORAGE;

列存分区表修改为冷热表:

1
2
3
4
5
6
7
CREATE table test_1(id int,d_time date)
WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (id)
PARTITION BY RANGE (d_time)
(PARTITION p1 START('2022-01-01') END('2022-01-31') EVERY(interval '1 day'));

ALTER TABLE test_1 SET (storage_policy = 'LMT:100');

列操作示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
DROP TABLE IF EXISTS warehouse_t;
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)                       
);

向表中增加一个新的字段:

1
ALTER TABLE warehouse_t ADD W_GOODS_CATEGORY int;

修改表中列名信息以及列字段信息:

1
ALTER TABLE warehouse_t CHANGE W_GOODS_CATEGORY W_GOODS_CATEGORY2 DECIMAL NOT NULL COMMENT 'W_GOODS_CATEGORY';

给已创建好的表增加主键:

1
ALTER TABLE warehouse_t ADD PRIMARY KEY(w_warehouse_name);

重命名列:

1
ALTER TABLE warehouse_t RENAME W_ZIP TO new_W_ZIP;

向表中增加多列:

1
ALTER TABLE warehouse_t ADD (W_COMMENT VARCHAR(117) NOT NULL, W_COUNT int);

修改表中已存在字段的数据类型,并将字段约束设置为非空:

1
ALTER TABLE warehouse_t MODIFY W_WAREHOUSE_SQ_FT varchar(20) NOT NULL;

为表的某列添加not null约束:

1
ALTER TABLE warehouse_t ALTER COLUMN W_COUNTY SET NOT NULL;

从表中删除一个字段:

1
ALTER TABLE warehouse_t DROP COLUMN W_STATE;

相关文档