ALTER TABLE
功能描述
修改表,包括修改表的定义、重命名表、重命名表中指定的列、设置表的所属模式、添加/更新多个列。
注意事项
- 表的所有者、被授予了表ALTER权限的用户或被授予ALTER ANY TABLE权限的用户有权限执行ALTER TABLE命令,系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式,当前用户必须是该表的所有者或者系统管理员,且该用户是新所有者角色的成员。
- 不能修改分区表的TABLESPACE,但可以修改分区的TABLESPACE。
- SET SCHEMA操作不支持修改为系统内部模式,当前仅支持用户模式之间的修改。
- 不支持增加DEFAULT值中包含nextval()表达式的列。
- 通过约束名删除PRIMARY KEY约束时,不会删除NOT NULL约束,如果有需要,请手动删除NOT NULL约束。
- 使用JDBC时,支持通过PrepareStatement对DEFAULT值进行参数化设置。
- 如果用ADD COLUMN增加一个字段,那么所有表中现有行都初始化为该字段的缺省值(如果没有声明DEFAULT子句,默认值为NULL)。
- 新增列没有声明DEFAULT值时,默认值为NULL,不会触发全表更新。
- 新增列如果有DEFAULT值,必须符合以下所有要求,否则会带来全表更新的操作,影响在线业务:
- 数据类型必须为以下类型:TINYINT、SMALLINT、BIGINT、INTEGER、NUMERIC、DECIMAL、BOOL、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、TIMESTAMP、DATE、TIME。
- 新增列的DEFAULT值长度不超过128个字节。
- 新增列DEFAULT值不包含易变(volatile)函数。
- 新增列设置有DEFAULT值,且DEFAULT值不为NULL。
- 如果不确定是否满足新增列DEFAULT值不包含易变(volatile)函数的条件,可以查询pg_proc系统表中函数的provolatile属性是否为'v'进行判断。
- 使用FIRST | AFTER column_name新增列或修改列,或修改字段的字符集,会带来全表更新的操作,影响在线业务。
- 在数据库管理系统中,ALTER TABLE操作可能会涉及对目标表的存在性的校验、用户权限的校验以及记录审计日志等。当在ALTER TABLE操作中未指定具体的动作action时,系统通常会进行一些基本的校验,但不会对表结构进行任何实际修改。
语法格式
- 修改表的定义。
ALTER TABLE { table_name [*] | ONLY table_name | ONLY ( table_name ) } [action [, ... ]];其中具体表操作action可以是以下子句之一:column_clause | ADD [CONSTRAINT] table_constraint | DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] | SET ( { storage_parameter = value} [, ... ] ) | RESET ( storage_parameter [, ... ] ) | OWNER TO new_owner | TO { GROUP groupname | NODE ( nodename [, ... ] ) } | ADD NODE ( nodename [, ... ] ) | DELETE NODE ( nodename [, ... ] ) | AUTO_INCREMENT [ = ] value | COMMENT [ = ] 'string' | [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] charset_name] [ [ DEFAULT ] COLLATE [ = ] collation_name] | CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset_name [COLLATE collation_name ] | ADD index_clause | DROP {INDEX | KEY} index_name | DROP PRIMARY KEY | DROP FOREIGN KEY fk_name | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } | ENCRYPTION KEY ROTATION
- ADD [CONSTRAINT] table_constraint
给表增加一个新的约束。
- DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
删除一个表上的约束。
- SET ( { storage_parameter = value} [, ... ] )
修改表的存储参数。
- RESET ( storage_parameter [, ... ] )
重置表的存储参数。
- OWNER TO new_owner
将表、序列、视图的所有者改变成指定的用户,用户名要求具体请参见CREATE USER章节中的user_name。
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
此语法仅在扩展模式(GUC参数support_extended_features为on时)下可用。该模式谨慎打开,主要供内部扩容工具使用,一般用户不应使用该模式。
- ADD NODE ( nodename [, ... ] )
此语法主要供内部扩容工具使用,一般用户不建议使用。
- DELETE NODE ( nodename [, ... ] )
此语法主要供内部缩容工具使用,一般用户不建议使用。
- AUTO_INCREMENT [ = ] value
设置自动增长列下一次的自增值。设置的值只有大于当前自增计数器时才会生效。
value必须是非负数,且不得大于2127-1。
- [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]
将表的默认字符集和默认字符序修改为指定的值。修改不会影响表中当前已经存在的列。
- CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset [ COLLATE collation ]
将表的默认字符集和默认字符序修改为指定的值,同时将表中的所有字符类型的字段的字符集和字符序设置为指定的值,并将字段里的数据转换为新字符集编码。
该语法不支持分区表修改字符序。
- DROP {INDEX | KEY} index_name
删除表上的索引。INDEX和KEY为同义词。
- DROP PRIMARY KEY
删除表上的主键约束。
- DROP FOREIGN KEY fk_name
删除指定的外键约束。
- REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } 在逻辑复制场景下,指定该表的UPDATE和DELETE操作中旧元组的记录级别。
- DEFAULT记录主键的列的旧值,没有主键则不记录。
- USING INDEX记录命名索引覆盖的列的旧值,这些值必须是唯一的、不局部的、不可延迟的,并且仅包括标记为NOT NULL的列。
- FULL记录该行中所有列的旧值。
- NOTHING不记录有关旧行的信息。
在逻辑复制场景,解析该表的UPDATE和DELETE操作语句时,解析出的旧元组由以此方法记录的信息组成。对于有主键表该选项可设置为DEFAULT或FULL。对于无主键表该选项需设置为FULL,否则解码时旧元组将解析为空。一般场景不建议设置为NOTHING,旧元组会始终解析为空,建议用户表定义主键或唯一约束,以保证回放的确定性,否则回放SQL语句时可能会导致多行修改影响数据正确性。
针对ustore表,选项NOTHING无效,实际效果等同于FULL;DEFAULT没有主键时,记录该行所有列。
- ENCRYPTION KEY ROTATION
透明数据加密密钥轮转。只有在数据库开启透明加密功能(即已通过GUC参数enable_tde开启透明加密功能,并通过GUC参数tde_key_info设置访问密钥服务的信息)并且表的ENABLE_TDE选项设置为on时,可以指定该选项。
指定该选项时,系统会自动向KMS申请创建新的密钥。密钥轮转后,使用旧密钥加密的数据仍使用旧密钥解密,新写入的数据使用新密钥加密。为保证加密数据安全,用户可根据加密表的新增数据量大小定期更新密钥。
- 其中列相关的操作column_clause可以是以下子句之一:
ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ][ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name ] | ADD ({ column_name data_type } [, ...] ) | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | MODIFY column_name [ CONSTRAINT constraint_name ] NULL | MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ] | CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ] | DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] | ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT } | MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED | VIRTUAL]
- ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name]
向表中增加一个新的字段。用ADD COLUMN增加一个字段,所有表中现有行都初始化为该字段的缺省值(如果没有声明DEFAULT子句,值为NULL)。其中FIRST | AFTER column_name表示新增字段到某个位置。
- ADD ( { column_name data_type } [, ...] )
向表中增加多列。
- MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] )
修改表已存在字段的数据类型。此命令会导致该字段的统计信息清空,建议在修改后重新收集该列的统计信息。
- MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
- 修改表已存在字段的定义,将用新定义替换字段原定义,原字段上的索引、独立对象约束(例如:主键、唯一键、CHECK约束等)不会被删除。[FIRST | AFTER column_name]语法表示修改字段定义的同时修改字段在表中的位置。
- 不支持修改分区键信息,即不支持指定column_name为分区键。
- 被修改数据类型或排序规则的字段如果被一个生成列引用,这个生成列的数据将会重新生成。
- 被修改字段若被一些对象依赖(比如:索引、独立对象约束、视图等),修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束,修改操作会失败,比如:修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。
- 被修改字段若被一些对象调用,修改字段不会处理这些对象。修改字段完毕后,这些对象有可能出现不可用的情况,请修改字段前评估这类影响。
- 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。
- 此命令会导致该字段的统计信息清空,建议在修改后重新收集该列的统计信息。
- CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
- 修改表已存在字段的名称和定义,字段新名称不能是已有字段的名称,将用新名称和定义替换字段原名称和定义。原字段上的索引、独立对象约束(例如:主键、唯一键、CHECK约束)等不会被删除。[FIRST | AFTER column_name]语法表示修改字段名称和定义的同时修改字段在表中的位置。
- 不支持修改分区键字段的数据类型和排序规则,不支持修改规则引用的字段的数据类型和排序规则。
- 被修改数据类型或排序规则的字段如果被一个生成列引用,这个生成列的数据将会重新生成。
- 被修改字段若被一些对象依赖(比如:索引、独立对象约束、视图等),修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束,修改操作会失败,比如:修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。
- 被修改字段若被一些对象调用,修改字段不会处理这些对象。修改字段名称后,这些对象有可能出现不可用的情况,请修改字段前评估这类影响。
- 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。
- DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ]
- 从表中删除一个字段,和这个字段相关的索引和表约束也会被自动删除。CASCADE选项在数据库M-compatibility模式兼容版本控制开关s1及以上版本(如m_format_dev_version = 's1')时仅语法支持,但实际不生效。
- DROP COLUMN命令并不是物理上把字段删除,而只是简单地把它标记为对SQL操作不可见。随后对该表的插入和更新将在该字段存储一个NULL。因此,删除一个字段是很快的,但是它不会立即释放表在磁盘上的空间,因为被删除了的字段占据的空间还没有回收。这些空间将在执行VACUUM时而得到回收。
- ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }
为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令,它们不会修改表中已经存在的行。也可以为视图创建缺省,这个时候它们是在视图的ON INSERT规则应用之前插入到INSERT句中的。此处的default_expr支持范围请参见CREATE TABLE章节中的DEFAULT default_expr。
- MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED | VIRTUAL]
修改生成列的数据类型和表达式,具体请参见[GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]。
- 其中列约束column_constraint为:
AUTO_INCREMENT | COMMENT 'string' |[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | ON UPDATE update_expr | [GENERATED ALWAYS] AS ( generation_expr ) [STORED] | UNIQUE [KEY] index_parameters | [PRIMARY] KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } - 其中update_expr为:
{ CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }
- ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name]
- index_clause
{INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]参数说明,具体请参见CREATE INDEX语法。
- 其中表约束table_constraint为:
[ CONSTRAINT [ constraint_name ] ] { CHECK ( expression ) | UNIQUE [INDEX|KEY][index_name] [USING access_method] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [USING access_method] [comment 'string']| PRIMARY KEY [ USING access_method ] [index_name] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [USING access_method] [comment 'string'] } FOREIGN KEY [ idx_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }access_method可取值为:
BTREE
其中索引参数index_parameters为:[ WITH ( {storage_parameter = value} [, ... ] ) ]
- ADD [CONSTRAINT] table_constraint
- 重命名表。对名称的修改不会影响所存储的数据。
ALTER TABLE table_name RENAME [TO | AS | =] new_table_name; - 重命名表上的索引。
ALTER TABLE table_name RENAME {INDEX | KEY} old_index_name TO new_index_name - 设置表的所属模式。
ALTER TABLE [ IF EXISTS ] table_name SET SCHEMA new_schema;
- 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列,需要将序列删除,重建,或者取消拥有关系, 才能将表schema更改成功。
- 要修改一个表的模式,用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表,用户必须同时又是父表的所有者。要修改所有者,用户还必须是新的所有角色的直接或间接成员,并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做除了重建和删除表之外的事情。不过,系统管理员可以以任何方式修改任意表的所有权限。
- 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如,可以在一个命令里增加几个字段或修改几个字段的类型。对于大表,此种操作带来的效率提升更明显,原因在于只需要对该大表做一次处理。
- 增加一个CHECK或NOT NULL约束将会扫描该表,以保证现有的行符合约束要求。
- 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说,这个操作可能会花很长时间,并且它还临时需要两倍的磁盘空间。
- 添加多个列。
ALTER TABLE table_name ADD ( { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
参数说明
- IF EXISTS
如果不存在相同名称的表,不会抛出一个错误,而会发出一个通知,告知表不存在。
- table_name [*] | ONLY table_name | ONLY ( table_name )
table_name是需要修改的表名。
若声明了ONLY选项,则只有该表被更改。若未声明ONLY,该表及其所有子表都将会被更改。另外,可以在表名称后面显式增加*选项,以实现指定包括子表,即表示所有后代表都被扫描,这是默认行为。目前ONLY和增加*选项为保留语法选项,但功能并不支持。
- constraint_name
- 在DROP CONSTRAINT操作中表示要删除的现有约束的名称。
- 在ADD CONSTRAINT操作中表示新增的约束名称。
- index_name
索引名称。
在ADD CONSTRAINT操作中:
- 对于外键约束,constraint_name和index_name同时指定时,索引名为constraint_name。
- 对于唯一键约束,constraint_name和index_name同时指定时,索引名为index_name。
- USING method
指定创建索引的方法。
取值范围:具体请参见CREATE INDEX章节中的USING method。
在ADD CONSTRAINT操作中:
- 未指定USING method时,对于ASTORE的存储方式,默认索引方法为btree;对于USTORE的存储方式,默认索引方法为ubtree。
- 当表的存储方式为USTORE时,SQL语句中约束指定为using btree,底层会自动将约束建立为using ubtree。
- ASC | DESC
ASC表示指定按升序排序(默认)。DESC指定按降序排序。
- expression
创建一个基于该表的一个或多个字段的表达式索引约束,必须写在圆括号中。
- storage_parameter
存储参数的名称。当前支持修改的参数描述如下:
- FILLFACTOR
数据页填充率(填充因子)。在Ustore存储引擎下,该参数的默认值为92,在Astore存储引擎下,该参数的默认值为100(完全填充)。如果指定了较小的填充因子,INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行,这就使得UPDATE有机会在同一页上放置同一条记录的新版本,这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择,但是对于频繁更新的表,选择较小的填充因子则更加合适。
取值范围:10~100(百分比)
- INIT_TD
创建Ustore表时,用于指定初始化的TD数量。该参数直接影响数据页中单个元组的最大存储空间,具体的计算关系为MAX_TUPLE_SIZE = BLCKSZ - INIT_TD * TD_SIZE。当调整INIT_TD值时,单个元组的最大存储空间将随之变化,例如:将INIT_TD从4增加到8时,单个元组的最大存储空间将减少4*TD_SIZE的存储空间。
取值范围:[2, 128]的int类型值。
默认值:4
- PARALLEL_WORKERS
控制创建索引时启用的后台工作线程(bgworker)数量。未显式指定该参数时,默认不启用并行索引创建功能。
取值范围:[0, 32]的int类型值。当参数值为0时,表示不启用并行索引创建功能。
- ENABLE_TDE
是否将表设置为加密表,即是否开启透明数据加密功能。本参数仅支持行存表、段页式表、临时表和unlogged表。
取值范围:on或off,on表示开启透明数据加密,off表示关闭透明数据加密。
- 从on切换为off后,插入或更新数据在写入旧页面时仍加密,写入切换后新生成的页面时不加密。
- 从off切换为on后,插入或更新数据在写入旧页面时不加密,写入切换后新生成的页面时会自动加密。
- 仅在GUC参数enable_tde开启透明加密功能,并通过GUC参数tde_key_info设置访问密钥服务的信息后支持将该选项设置为on。
- 透明数据加密状态切换后,生成的新数据页面加密状态与切换后状态保持一致,旧数据页面加密状态与切换前状态保持一致。当透明数据加密状态发生切换时,建议用户手动对表执行VACUUM FULL操作,确保所有数据页面加密状态保持一致。
- ENCRYPT_ALGO
取值范围:字符串,有效值为AES_128_CTR,SM4_CTR、AES_128_CTR_SHA256_HMAC、SM4_CTR_SM3_HMAC或AES_128_GCM。
默认值:
- ENABLE_TDE设置为on时(即开启透明数据加密),受GUC参数tde_encrypt_config影响。当GUC参数tde_encrypt_config中子参数table_algorithm不为空时,默认值与table_algorithm相同;如果table_algorithm为空,默认值为AES_128_CTR。
- ENABLE_TDE设置为off时(即关闭透明数据加密),默认值为空。
- 与透明数据加密选项ENABLE_TDE类似,切换加密表的加密算法后,插入或更新数据在写入旧页面时仍使用旧加密算法,写入切换后新生成的页面时则使用新加密算法。
- 当加密表的加密算法发生切换时,建议用户手动对表执行VACUUM FULL操作,使所有数据页面均使用新算法加密。
- FILLFACTOR
- new_owner
表新拥有者的名称,用户名要求具体请参见CREATE USER章节中的user_name。
- column_name,column_1_name, column_2_name
现存的或新字段的名称。
- data_type
新字段的类型,或者现存字段的新类型。
- charset
指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。
- collation
字段排序规则(字符序)名称。可选字段COLLATE指定了新字段的排序规则,如果省略,排序规则为新字段的默认类型。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询,默认的排序规则为查询结果中以default开始的行。
还支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序,请参见表级字符集和字符序。
- 仅字符类型支持指定字符集,指定为BINARY字符集或字符序实际是将字符类型转化为对应的二进制类型,若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
- 除BINARY字符集、字符序外,当前仅支持指定与数据库编码相同的字符集。
- 字段字符集或字符序未显式指定时,若指定了表的默认字符集或字符序,字段字符集和字符序将从表上继承。
- 当修改的字符集或字符序对应的字符集与当前字段字符集不同时,会将字段中的数据转换为指定的字符集进行编码。
- NOT NULL | NULL
设置列是否允许空值。
- ENABLE
表示启动该约束,缺省时默认启用。
- CHECK ( expression )
每次将要插入的新行或者将要被更新的行必须使表达式结果为真才能成功,否则会抛出一个异常并且不会修改数据库。
目前,CHECK表达式不能包含子查询。
- DEFAULT default_expr
为字段指定缺省表达式default_expr,当插入操作中未明确指定字段的值时,将自动使用缺省表达式。如果未指定缺省值则缺省默认值为NULL。
缺省表达式可以使用default_expr格式和(default_expr)格式进行限定:
- 当使用default_expr(未嵌套括号)格式时,支持指定以下内容:常量、带正负号的数值常量、update_expr。
- 当使用(default_expr)(嵌套括号)格式时,支持指定以下内容:常量、带正负号的数值常量、update_expr、CURRENT_TIME/CURTIME函数、CURRENT_DATE/CURDATE函数(CURRENT_TIME函数/CURRENT_DATE函数支持不带括号调用)。
- 仅支持在TIMESTAMP以及DATETIME类型的字段上指定update_expr作为默认值,且字段的精度与update_expr的精度须保持一致。
update_expr的语法格式:CURRENT_TIMESTAMP ['(' [precision_num] ')'] | LOCALTIMESTAMP ['(' [precision_num] ')'] | LOCALTIME ['(' [precision_num] ')'] | NOW '(' [precision_num] ')'precision_num:可选,表示指定的精度,取值范围[0, 6]。
不带括号或空括号时精度为0,其中NOW关键字不支持不带括号。
- ON UPDATE update_expr
ON UPDATE子句为字段的一种属性约束。
当对表中某元组执行UPDATE操作时,若更新字段的新值和表中旧值不相同,则表中该元组上具有该属性且不在更新字段内的字段值自动更新为当前时间戳;若更新字段的新值和表中旧值相同,则表中该元组上具有该属性且不在更新字段内的字段值不变,保持原有值;若具有该属性的字段在更新字段内,则对应这些字段值直接按指定更新的值更新。
- 语法上update_expr支持CURRENT_TIMESTAMP 、LOCALTIMESTAMP 、LOCALTIME、NOW()四种关键字,也支持关键字带括号指定或不指定精度。例如:ON UPDATE CURRENT_TIMESTAMP()、ON UPDATE CURRENT_TIMESTAMP(5)、ON UPDATE LOCALTIMESTAMP()、ON UPDATE LOCALTIMESTAMP(6)等。不带括号或空括号时精度为0,其中NOW关键字不支持不带括号。四种关键字互为同义词,属性效果相同。
- 该属性支持在如下类型的列上指定: TIMESTAMP、DATETIME。
- 该属性指定的精度和对应列上类型指定的精度必须一致,否则会触发报错。例如:CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6)); 若精度不一致,会产生ERROR: Invalid ON UPDATE clause for "col1" 报错。
- 该属性和生成列约束不能同时指定同一列。
- 分区表中的分区键不支持指定该属性。
- [GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]
该子句将字段创建为生成列,通过指定STORED和VIRTUAL关键字表明生成列的列值存储方式:
- STORED:创建存储生成列,列值需要存储,在插入或更新元组时,由生成列表达式计算存储生成列的列值。
- VIRTUAL:创建虚拟生成列,列值不需要存储,在查询语句涉及虚拟生成列时,由生成列表达式计算列值。
- STORED和VIRTUAL关键字可省略,缺省为STORED,在参数m_format_dev_version为s2条件下缺省为VIRTUAL。
- 生成表达式不能以任何方式引用当前行以外的其他数据。生成表达式支持引用早定义于当前生成列的其他生成列,不能引用系统列。生成列表达式不能引用系统变量、用户自定义变量、存储过程中的变量。生成列表达式不支持引用自增列,且生成列不支持定义自增列属性。生成表达式不能返回结果集,不能使用子查询,不能使用聚集函数、窗口函数和自定义函数。生成表达式调用的函数只能是不可变(IMMUTABLE)函数。
- 不支持为生成列指定默认值。
- 存储生成列支持作为分区键的一部分,当作为分区键的一部分时,不支持ALTER TABLE修改存储生成列。
- 虚拟生成列不支持作为分区键的一部分。
- 虚拟生成列不支持被外键约束引用。
- 不支持在虚拟生成列上创建索引。
- 存储生成列不能和ON UPDATE约束子句的CASCADE、SET NULL、SET DEFAULT动作同时指定。存储生成列不能和ON DELETE约束子句的SET NULL、SET DEFAULT动作同时指定。
- 在参数m_format_dev_version为s2条件下,存储生成列的基列不能和ON UPDATE或者ON DELETE约束子句的CASCADE、SET NULL、SET DEFAULT动作同时指定。
- 修改和删除生成列的方法和普通列相同。删除生成列依赖的基列,生成列被自动删除。在参数m_format_dev_version为s2条件下,删除生成列依赖的基列之前需要先删除对应的生成列。
- 生成列不能被直接写入。在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。向可被更新的视图中插入生成列数据时,不能为生成列指定值,但是可以指定关键字DEFAULT。
- 当存储生成列带CHECK约束时,在参数m_format_dev_version为s2的条件下,ALTER TABLE修改存储生成列定义,如果存在生成列不满足CHECK约束,ALTER TABLE语句执行失败,系统报ERROR。
- 生成列的权限控制和普通列一样。
- 不开启精度传递开关(m_format_behavior_compat_options不开启enable_precision_decimal选项)时创建的带有生成列的表,与开启精度传递开关后创建的带有生成列的表在浮点数的比较结果等方面可能出现不一致的情况。如果用户需要在开启精度传递开关的情况下使用未开启精度开关时创建的带有生成列的表,建议在开启精度传递开关后使用ALTER TABLE语法重新定义生成列。
m_db=# SET m_format_behavior_compat_options=''; SET m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED); CREATE TABLE m_db=# INSERT INTO mm1 VALUES(1.2, 0.7); INSERT 0 1 m_db=# SELECT * FROM mm1; a | b | c --------+-------+--- 1.2000 | 0.700 | 0 (1 row) m_db=# SET m_format_behavior_compat_options='enable_precision_decimal'; SET m_db=# CREATE TABLE mm2(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED); CREATE TABLE m_db=# INSERT INTO mm1 VALUES(1.2, 0.7); INSERT 0 1 m_db=# INSERT INTO mm2 VALUES(1.2, 0.7); INSERT 0 1 m_db=# SELECT * FROM mm1; a | b | c --------+-------+--- 1.2000 | 0.700 | 0 1.2000 | 0.700 | 1 (2 rows) m_db=# SELECT * FROM mm2; a | b | c --------+-------+--- 1.2000 | 0.700 | 0 (1 row) m_db=# DROP TABLE mm1, mm2; DROP TABLE
- AUTO_INCREMENT
指定列为自动增长列。
具体请参见CREATE TABLE章节中的AUTO_INCREMENT。
- COMMENT [ = ] 'string'
- COMMENT [ = ] 'string'子句表示给表添加注释。
- 在column_constraint中的COMMENT 'string'表示给列添加注释。
- 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。
具体请参见CREATE TABLE章节中的COMMENT [ = ] 'string'。
- 列级唯一约束:UNIQUE [KEY] index_parameters
UNIQUE KEY与UNIQUE语义相同。
表示表里的一个或多个字段的组合必须在全表范围内唯一。
- 表级唯一约束:UNIQUE [INDEX | KEY] [ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters
表示表里的一个或多个字段的组合必须在全表范围内唯一。
column_name (length)是前缀键,具体请参见CREATE INDEX章节中的column_name ( length )。
index_name为索引名。
对于唯一键约束,constraint_name和index_name同时指定时,索引名为index_name。
- 列级主键约束:[PRIMARY] KEY index_parameters
表级主键约束:PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters
主键约束表明表中的一个或者一些字段只能包含唯一(不重复)的非NULL值。
- 列级外键约束:REFERENCES reftable [ ( refcolum ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]
表级外键约束:FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]
表列字段约束REFERENCES使用时语法不报错也没有提示,但实际不生效。
外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。若省略refcolumn,则将使用reftable的主键。被参考列应该是被参考表中的唯一字段或主键。外键约束不能被定义在临时表和永久表之间。
参考字段与被参考字段之间存在三种类型匹配,分别是:
- MATCH FULL:不允许一个多字段外键的字段为NULL,除非全部外键字段都是NULL。
- MATCH SIMPLE(缺省):允许任意外键字段为NULL。
另外,当被参考表中的数据发生改变时,某些操作也会在新表对应字段的数据上执行。ON DELETE子句声明当被参考表中的被参考行被删除时要执行的操作。ON UPDATE子句声明当被参考表中的被参考字段数据更新时要执行的操作。对于ON DELETE子句、ON UPDATE子句的可能动作:
- NO ACTION(缺省):删除或更新时,创建一个表明违反外键约束的错误。
- RESTRICT:删除或更新时,创建一个表明违反外键约束的错误。与NO ACTION相同。
- CASCADE:删除新表中任何引用了被删除行的行,或更新新表中引用行的字段值为被参考字段的新值。
- SET NULL:设置引用字段为NULL。
- SET DEFAULT:设置引用字段为它们的缺省值。
外键约束的完整性检查由GUC参数foreign_key_checks进行控制,foreign_key_checks可以设置为on/off,分别表示启用/关闭外键约束的完整性检查,默认情况下为on。foreign_key_checks参数的具体影响请参见foreign_key_checks。
- WITH ( {storage_parameter = value} [, ... ] )
索引指定一个可选的存储参数。
- new_table_name
修改后新的表名称。
- new_column_name
表中指定列修改后新的列名称。
- new_index_name
修改后表上索引的新名称。
- new_schema
修改后新的模式名称。
- CASCADE | RESTRICT
CASCADE: 级联删除依赖于被依赖字段或者约束的对象(比如引用该字段的视图)。
RESTRICT:如果字段或者约束还有任何依赖的对象,则拒绝删除该字段。这是缺省行为。
该属性在数据库M-compatibility模式兼容版本控制开关s1及以上版本(如m_format_dev_version = 's1')时仅语法支持,但实际不生效。
- FIRST
新增列或修改列到第一位。
- AFTER column_name
新增列或修改列到column_name之后。
有规则依赖的表不支持改变表列的位置(包括新增和修改导致列位置的变化)。
- schema_name
表所在的模式名称。
修改表示例
- 重命名表
m_db=# CREATE TABLE aa(c1 int, c2 int); m_db=# ALTER TABLE aa RENAME TO test_alt1;
- 修改表所属模式
--创建模式test_schema。 m_db=# CREATE SCHEMA test_schema; --把表test_alt1的所属模式修改为test_schema。 m_db=# ALTER TABLE test_alt1 SET SCHEMA test_schema; --查询表信息。 m_db=# SELECT schemaname,tablename FROM pg_tables WHERE tablename = 'test_alt1'; schemaname | tablename -------------+----------- test_schema | test_alt1 (1 row)
- 修改表的所有者
--创建用户test_user。 m_db=# CREATE USER test_user PASSWORD 'XXXXXXXXXX'; -- 修改test_alt1表的所有者为test_user。 m_db=# ALTER TABLE test_schema.test_alt1 OWNER TO test_user; -- 查看。 m_db=# SELECT tablename, schemaname, tableowner FROM pg_tables WHERE tablename = 'test_alt1'; tablename | schemaname | tableowner -----------+-------------+------------ test_alt1 | test_schema | test_user (1 row)
修改列示例
- 修改列名
-- 建表。 m_db=# CREATE TABLE test_alt2(c1 INT,c2 INT); -- 修改列名。 m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c1 id INT; m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c2 areaid INT; -- 查看。 m_db=# \d test_alt2 Table "public.test_alt2" Column | Type | Modifiers --------+---------+----------- id | integer | areaid | integer |
- 增加列
-- 表test_alt2增加列。 m_db=# ALTER TABLE test_alt2 ADD COLUMN name VARCHAR(20); -- 查看。 m_db=# \d test_alt2 Table "public.test_alt2" Column | Type | Modifiers ----------+-----------------------+----------- id | integer | areacode | integer | name | varchar(20) | - 增加AUTO_INCREMENT自增列
-- 建表,新增自增列。 m_db=# CREATE TABLE test_autoinc(col1 int); -- 插入一条数据。 m_db=# INSERT INTO test_autoinc(col1) VALUES(1); -- 添加一个本地自增列,从1开始自增。 m_db=# ALTER TABLE test_autoinc ADD COLUMN col int AUTO_INCREMENT; m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1; col | col1 -----+------ 1 | 1 (1 row) -- 将下一个自增值设为10。 m_db=# ALTER TABLE test_autoinc AUTO_INCREMENT = 10; -- NULL触发自增,自增值为10。 m_db=# INSERT INTO test_autoinc(col, col1) VALUES(NULL,2); -- 0触发自增,自增值为11。 m_db=# INSERT INTO test_autoinc(col, col1) VALUES(0,3); m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1; col | col1 -----+------ 1 | 1 10 | 2 11 | 3 (3 rows)
- 修改列的数据类型
-- 修改test_alt2表中name字段的类型。 m_db=# ALTER TABLE test_alt2 MODIFY name VARCHAR(50); -- 查看。 m_db=# \d test_alt2 Table "public.test_alt2" Column | Type | Modifiers --------+-----------------------+----------- c1 | integer | c2 | integer | name | varchar(50) | - 删除列
-- 删除test_alt2中areaid字段。 m_db=# ALTER TABLE test_alt2 DROP COLUMN areaid; -- 查看 m_db=# \d test_alt2 Table "public.test_alt2" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | varchar(50) |
修改约束示例
- 修改字段默认值
--建表。 m_db=# CREATE TABLE test_alt3(pid INT, areaid CHAR(5), name VARCHAR(20)); --修改test_alt1表中id的默认值。 m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid SET DEFAULT '00000'; --查看。 m_db=# \d test_alt3 Table "public.test_alt3" Column | Type | Modifiers --------+-----------------------+------------------------- pid | integer | areaid | char(5) | default '00000'::bpchar name | varchar(20) |--删除id的默认值。 m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid DROP DEFAULT; --查看。 m_db=# \d test_alt3 Table "public.test_alt3" Column | Type | Modifiers --------+-----------------------+----------- pid | integer | areaid | char(5) | name | varchar(20) | - 添加表级约束 直接添加约束
--给表添加主键约束。 m_db=# ALTER TABLE test_alt3 ADD CONSTRAINT pk_test3_pid PRIMARY KEY (pid); --查看。 m_db=# \d test_alt3 Table "public.test_alt3" Column | Type | Modifiers --------+-----------------------+----------- pid | integer | not null areaid | integer | name | varchar(20) | Indexes: "pk_test3_pid" PRIMARY KEY, btree (pid) TABLESPACE pg_default
修改索引示例
-- 创建表和索引
m_db=# CREATE TABLE t01(c1 int PRIMARY KEY, c2 int UNIQUE);
m_db=# CREATE INDEX idx1 ON t01(c1);
m_db=# CREATE INDEX idx2 ON t01(c1,c2);
m_db=# \d+ t01
Table "public.t01"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | integer | not null | plain | |
c2 | integer | | plain | |
Indexes:
"t01_pkey" PRIMARY KEY, ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
"t01_c2_key" UNIQUE CONSTRAINT, ubtree ON t01 (c2) WITH (storage_type=USTORE) TABLESPACE pg_default
"idx1" ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
"idx2" ubtree ON t01 (c1, c2) WITH (storage_type=USTORE) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off
-- 删除表中索引
m_db=# ALTER TABLE t01 DROP INDEX idx1, DROP INDEX idx2;
m_db=# ALTER TABLE t01 DROP INDEX t01_pkey, DROP INDEX t01_c2_key;
m_db=# \d+ t01
Table "public.t01"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | integer | not null | plain | |
c2 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off
m_db=# DROP TABLE t01;