ALTER TABLE

功能描述

修改表，包括修改表的定义、重命名表、重命名表中指定的列、设置表的所属模式、添加/更新多个列。

注意事项

• 表的所有者、被授予了表ALTER权限的用户或被授予ALTER ANY TABLE权限的用户有权限执行ALTER TABLE命令，系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式，当前用户必须是该表的所有者或者系统管理员，且该用户是新所有者角色的成员。
• 不能修改分区表的TABLESPACE，但可以修改分区的TABLESPACE。
• 不支持修改存储参数ORIENTATION。
• SET SCHEMA操作不支持修改为系统内部模式，当前仅支持用户模式之间的修改。

语法格

• 修改表的定义。

  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 [, ... ] )
      | UPDATE SLICE LIKE table_name
      | COMMENT [ = ] 'string'
      | [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] charset_name] [ [ DEFAULT ] COLLATE [ = ] collation_name]
      | ADD index_clause
      | DROP PRIMARY KEY
      | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } 

  

  • ADD [CONSTRAINT] table_constraint

    给表增加一个新的约束。

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

    删除一个表上的约束。

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

    修改表的一个或多个存储参数。当table_name为索引名时，ACTIVE_PAGES表示索引的页面数量，可能比实际的物理文件页面少，可以用于优化器调优。目前只对Ustore的分区表LOCAL索引生效，且会被VACUUM、ANALYZE更新（包括AUTO VACUUM）。不建议用户手动设置该参数，该参数在分布式下无效。

  • RESET ( storage_parameter [, ... ] )

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

  • OWNER TO new_owner

    将表、序列、视图的所有者改变成指定的用户，用户名要求详见•user_name。

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

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

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

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

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

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

  • UPDATE SLICE LIKE table_name

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

  • [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]

    将表的默认字符集和默认字符序修改为指定的值。修改不会影响表中当前已经存在的列。

  • DROP PRIMARY KEY

    删除表上的主键约束。

  • REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
    在逻辑复制场景下，指定该表的UPDATE和DELETE操作中旧元组的记录级别。

    • DEFAULT记录主键的列的旧值，没有主键则不记录。
    • USING INDEX记录命名索引覆盖的列的旧值，这些值必须是唯一的、不局部的、不可延迟的，并且仅包括标记为NOT NULL的列。
    • FULL记录该行中所有列的旧值。
    • NOTHING不记录有关旧行的信息。

    在逻辑复制场景，解析该表的UPDATE和DELETE操作语句时，解析出的旧元组由以此方法记录的信息组成。对于有主键表该选项可设置为DEFAULT或FULL。对于无主键表该选项需设置为FULL，否则解码时旧元组将解析为空。一般场景不建议设置为NOTHING，旧元组会始终解析为空。

    针对ustore表，选项NOTHING无效，实际效果等同于FULL；DEFAULT没有主键时，记录该行所有列。

  • 其中列相关的操作column_clause可以是以下子句之一：

    ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ][ COLLATE collation ] [ column_constraint [ ... ] ]    
    | ADD ({ column_name data_type [ compress_mode ]} [, ...] )
    | DROP [ COLUMN ]  column_name [ RESTRICT | CASCADE ]    
    | ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }  

    

    • ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ]

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

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

      向表中增加多列。

    • 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支持范围请参见•DEFAULT default_expr

    • 其中列约束column_constraint为：

      COMMENT 'string'
      |[ CONSTRAINT constraint_name ]
          { NOT NULL |
            NULL |
            CHECK ( expression ) |
            DEFAULT default_expr  |
            ON UPDATE update_expr |
            UNIQUE [KEY] index_parameters |
            [PRIMARY] KEY index_parameters
       }

    • 其中列的压缩可选项compress_mode为：

      [ DICTIONARY ]

    • 其中update_expr为：

      { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

  • 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'] [ BY GLOBAL INDEX ]|
          PRIMARY KEY [ USING access_method ] [index_name] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [USING access_method] [comment 'string']
    }
            }  

    access_method可取值为：

    BTREE

    其中索引参数index_parameters为：

    [ WITH ( {storage_parameter = value} [, ... ] ) ]   

    指定是否创建全局二级索引

    [BY GLONAL INDEX]

参数说明

• 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同时指定时，索引名为index_name。

• USING method

  指定创建索引的方法。

  取值范围参考参数说明中的USING method。

  

  在ADD CONSTRAINT操作中：

  • 未指定USING method时，对于ASTORE的存储方式，默认索引方法为btree；对于USTORE的存储方式，默认索引方法为ubtree。
  • 当表的存储方式为USTORE时，SQL语句中约束指定为using btree，底层会自动将约束建立为using ubtree。
• ASC | DESC

  ASC表示指定按升序排序（默认）。DESC指定按降序排序。

• expression

  创建一个基于该表的一个或多个字段的表达式索引约束，必须写在圆括弧中。

• storage_parameter

  存储参数的名称。

  透明数据加密选项：

  • enable_tde（BOOLEAN类型）

    是否将表设置为加密表。本参数仅支持行存表、段页式表、临时表和unlogged表。设置enable_tde=on时，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息。在《特性指南》中“透明数据加密”章节可获取该参数的详细使用方法。

    取值范围：on/off。

    • on：表示开启透明数据加密。
    • off：表示关闭透明数据加密。
      

      • 从on切换为off后，插入或更新数据在写入旧页面时仍加密，写入切换后新生成的页面时会不加密。
      • 从off切换为on后，插入或更新数据在写入旧页面时不加密，写入切换后新生成的页面时会自动加密。

      即：加密开关切换后，生成的新数据页面加密状态与开关切换后状态保持一致，旧数据页面加密状态与开关切换前状态保持一致。建议用户在切换加密开关后，手动对表执行VACUUM FULL操作，使所有数据页面加密状态保持一致。

    默认值：off

  • encrypt_algo（string类型）

    指定加密表的加密算法。

    取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR。

    默认值：如果enable_tde=on，GUC参数tde_encrypt_config中子参数table_algorithm不为空，默认值与table_algorithm相同；如果table_algorithm为空，默认值为AES_128_CTR。如果enable_tde=off，默认值为空。

  创建索引新增一个选项：

  • parallel_workers（int类型）

    表示创建索引时起的bgworker线程数量，例如2就表示将会起2个bgworker线程并发创建索引。

    取值范围：[0,32]，0表示关闭并行建索引。

    默认值：不设置该参数，表示未开启并行建索引功能。

  • hasuids（bool类型）

    默认值：off

    参数开启：更新表元组时，为元组分配表级唯一标识id。

• new_owner

  表新拥有者的名称，用户名要求详见•user_name。

• column_name，column_1_name， column_2_name

  现存的或新字段的名称。

• data_type

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

• compress_mode

  表字段的压缩可选项。该子句指定该字段优先使用的压缩算法。行存表不支持压缩，当前选项无法使用。

• 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子句给字段指定缺省表达式，缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。
  • 支持指定以下内容：常量、带正负号的数值常量、update_expr。
  • 仅支持在TIMESTAMP、DATETIME类型的字段上指定update_expr作为默认值，且字段的精度与update_expr的精度须保持一致。
• 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" 报错。
  • 该属性和生成列约束不能同时指定同一列。
  • 分区表中的分区键不支持指定该属性。
• COMMENT [ = ] 'string'
  • COMMENT [ = ] 'string'子句表示给表添加注释。
  • 在column_constraint中的COMMENT 'string'表示给列添加注释。
  • 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。

  具体请参见：•COMMENT [ = ] 'string'

• 列级唯一约束：UNIQUE [KEY] index_parameters

  UNIQUE KEY与UNIQUE语义相同。

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

  

  对于列级唯一键约束，如果原表是哈希分布，创建列级唯一键约束会失败。可以用CREATE TABLE ADD 表级唯一约束实现。表级唯一约束支持BY GLOBAL INDEX选项。

• 表级唯一约束：UNIQUE [INDEX | KEY] [ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters [ BY GLOBAL INDEX]

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

  column_name (length)是前缀键，详见：•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值。

• 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'）时仅语法支持，但实际不生效。

• 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(id INT,areaid INT);
  -- 表test_alt2增加列。
  m_db=# ALTER TABLE test_alt2 ADD COLUMN name VARCHAR(20);
  -- 查看。
  m_db=# \d test_alt2
             Table "public.test_alt1"
    Column  |         Type          | Modifiers 
  ----------+-----------------------+-----------
   id       | integer               | 
   areacode | integer               | 
   name     | varchar(20)           | collate utf8mb4_general_ci

• 删除列

  -- 删除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

相关链接

CREATE TABLE，DROP TABLE