ALTER TABLE

功能描述

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

本文档仅列出ALTER TABLE在PostgreSQL兼容模式下的特性用法，更多语法及参数说明详见开发者指南中的SQL语法ALTER TABLE。

Vastbase在PostgreSQL兼容模式下支持ALTER TABLE修改表时使用以下特性用法：

支持使用ALTER TABLE ADD COLUMN为已存在的表增加serial类型的自增列。
支持ALTER TABLE ADD COLUMN新增字段时使用[ IF NOT EXISTS ]选项，如果已经存在相同名称的字段，不会抛出一个错误，而会发出一个通知，告知字段已存在。
支持根据ALTER TABLE ALTER COLUMN子句指定的Identity字段，由此会自动创建一个Sequence（自增序列），此序列自动为这个Identity生成值。在指定Identity字段之前，表中已存在的数据不受影响，只有后续的INSERT和UPDATE操作才会根据Identity属性来设置列的值。

注意事项

上述功能仅在数据库兼容模式为PG时能够使用（即创建DB时DBCOMPATIBILITY='PG'），在其他数据库兼容模式下不能使用该特性。
指定Identity字段功能仅在V2.2 Build 10 (Patch No.17)及以后版本支持。
增加一列自增列的前提是表已经存在。
不支持在列存表、临时表中添加serial自增列。
一个表只能有一个IDENTITY标识列。
不支持以下情况对列增加Identity属性：
- 若列已带有Identity属性，则无法再增加Identity属性。即同一个字段不可重复设置Identity属性。
- 若列为serial自增列，则无法再设置Identity属性。即不可对serial列设置identity属性。
若父表中存在Identity字段，子表中不会继承Identity字段，子表可以单独设置Identity属性。
对IDENTITY列进行INSERT和UPDATE修改的具体行为受CREATE TABLE时指定的GENERATED {ALWAYS | BY DEFAULT } AS的影响，详情请参见CREATE TABLE的参数说明。

语法格式

ALTER TABLE 新增字段的语法如下：

ALTER TABLE table_name ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ , ... ] ;

ALTER TABLE指定的Identity字段的相关语法如下：

新增IDENTITY属性列：

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

修改IDENTITY列sequence_option属性：

ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]

删除IDENTITY属性列：

ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

参数说明

table_name

需要执行修改操作的表名。
[ COLUMN ]

非必选。修饰字段的关键字，可在新增字段时指定。
[ IF NOT EXISTS ]

可选项。在新增字段时声明此选项，表示如果已经存在相同名称的字段，不会抛出一个错误，而会发出一个通知，告知字段已存在。
column_name

新增字段的名称。
data_type

新字段的数据类型。

如需指定新增字段为自增列，支持的数据类型包括：serial，bigserial，smallserial。
GENERATED {ALWAYS | BY DEFAULT } AS

该子句将列创建为标识列，由此会自动创建一个Sequence（自增序列），这个自增序列可以自动生成字段的值。
- GENERATED ALWAYS AS
  
  如果指定了ALWAYS，对于INSERT语句包含 OVERRIDING SYSTEM VALUE子句的情况下，才会接受用户自定义的值，否则都会使用自增序列的值。
  
  对于UPDATE语句，那么只会接受自增序列的值。
- GENERATED BY DEFAULT AS
  
  如果指定了BY DEFAULT，INSERT语句和UPDATE语句可以接受用户自定义的值。
IDENTITY

为字段指定IDENTITY属性，由此会自动创建一个Sequence（自增序列），这个自增序列可以自动为IDENTITY字段生成值。
sequence_options

可选设置，在设置之后可以覆盖自增序列的options。
- INCREMENT [ BY ] increment
  
  可选的子句INCREMENT BY increment指定为了创建新值会把哪个值加到当前序列值上。一个正值会创建一个升序序列，负值会创建一个降序序列。默认值为1。
- MINVALUE minvalue | NO MINVALUE | NOMINVALUE
  
  可选的子句MINVALUE minvalue决定一个序列能产生的最小值。如果未指定MINVALUE minvalue或指定了NO MINVALUE|NOMINVALUE则会使用默认值。
  
  升序序列的默认值为1，降序序列的默认值为数据类型的最小值。
- MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE
  
  可选的子句MAXVALUE maxvalue决定该序列的最大值。如果未指定MAXVALUE maxvalue或者指定了NO MAXVALUE|NOMAXVALUE，那么将会使用默认值。
  
  升序序列的默认值是数据类型的最大值。降序序列的默认值是-1。
- START [ WITH ] start
  
  可选的子句START WITH start表示允许序列从任何地方开始。
  
  对于升序序列和降序序列来说，默认的开始值分别是minvalue和maxvalue。
- CACHE cache
  
  可选的子句CACHE cache指定要预分配多少个序列数并且把它们放在内存中以便快速访问。最小值为1（一次只生成一个值，即没有缓存），默认值也是1。
- [ NO ] CYCLE | NOCYCLE
  
  对于升序序列和降序序列，CYCLE选项允许序列在分别达到maxvalue和minvalue时循环。
  
  如果达到该限制，下一个产生的数字将分别是minvalue和maxvalue。
  
  如果指定了NO CYCLE，当序列到达其最大值后任何nextval调用将返回一个错误。
  
  如果 CYCLE和NO CYCLE都没有被指定，则默认为NO CYCLE。
- OWNED BY { table_name.column_name | NONE }
  
  OWNED BY选项导致序列被与一个特定表的列关联在一起，这样如果该列（或者整个表）被删除，该序列也将被自动删除。指定的表必须和序列具有相同的拥有者并且在同一个模式中。
  
  默认选项OWNED BY NONE指定该序列不与某个列关联。
  
  通过OWNED BY创建的Sequence不建议用于其他表，如果希望多个表共享Sequence，该Sequence不应该从属于特定表。

示例

前置步骤：创建并切换至兼容模式为PG的数据库。

CREATE DATABASE db_pg DBCOMPATIBILITY 'PG';
\c db_pg

示例1： ALTER TABLE为表新增自增列。

1、创建测试表。

CREATE TABLE test(a int);

2、ALTER TABLE为表增加一个自增列。

ALTER TABLE test ADD COLUMN id1 serial;

3、插入数据并查看测试表。

insert into test values(1);
insert into test values(0);
insert into test values(2);
select * from test;

返回结果如下：

 a | id1
---+-----
 1 |   1
 0 |   2
 2 |   3
(3 rows)

4、为测试表插入两个自增字段。

ALTER TABLE test ADD (id2 bigserial,id3 smallserial);

5、查看测试表数据。

select * from test;

返回结果如下：

 a | id1 | id2 | id3
---+-----+-----+-----
 1 |   1 |   1 |   1
 0 |   2 |   2 |   2
 2 |   3 |   3 |   3
(3 rows)

示例2： ALTER TABLE新增字段时指定IF NOT EXISTS选项。

1、创建测试表。

CREATE TABLE tb1_1156542 (id_1 int2);

2、新增字段。

ALTER TABLE tb1_1156542 ADD COLUMN IF NOT EXISTS id_2 int1;
ALTER TABLE tb1_1156542 ADD COLUMN IF NOT EXISTS id_3 int2;
ALTER TABLE tb1_1156542 ADD COLUMN IF NOT EXISTS id_4 int4;

3、表中不不存在同名字段，修改成功。执行如下命令查看表结构。

\d tb1_1156542

返回结果如下：

  Table "public.tb1_1156542"
 Column |   Type   | Modifiers
--------+----------+-----------
 id_1   | smallint |
 id_2   | tinyint  |
 id_3   | smallint |
 id_4   | integer  |

4、使用表中已存在的字段名作为新增字段的名称，不指定IF NOT EXISTS。

ALTER TABLE tb1_1156542 ADD COLUMN id_2 int2;

执行失败，报错信息如下：

ERROR:  column "id_2" of relation "tb1_1156542" already exists

5、使用表中已存在的字段名作为新增字段的名称，同时指定IF NOT EXISTS。

ALTER TABLE tb1_1156542 ADD COLUMN IF NOT EXISTS id_2 int2;

表中已存在id_2字段，跳过此步骤，返回NOTICE信息如下：

NOTICE:  column "id_2" of relation "tb1_1156542" already exists, skipping

示例3： 为行存表指定和删除IDENTITY字段。

1、创建测试表

create table tb_1162787a(id int4 not null,name char(20));
create table tb_1162787b(id int4,name varchar(20));
insert into tb_1162787b values(1,'aaa');

2、指定identity字段。

alter table tb_1162787a alter column id add generated always as identity;
\d+ tb_1162787a;

返回结果为如下：

                         Table "public.tb_1162787a"
 Column |     Type      | Modifiers | Storage  | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              |
 name   | character(20) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

3、为测试表插入数据并查看结果。

insert into tb_1162787a(name) values('aaa');
select * from tb_1162787a;

查询结果为如下：

 id |         name
----+----------------------
  1 | aaa
(1 row)

4、修改IDENTITY字段。

set generated

alter table tb_1162787a alter column id set generated BY DEFAULT;
\d+ tb_1162787a;
insert into tb_1162787a values(6,'bbb');
insert into tb_1162787a(name) values('ccc');
select * from tb_1162787a;

查询结果为如下：

                                        Table "public.tb_1162787a"
Column |     Type      |                 Modifiers                 | Storage  | Stats target | Description
--------+---------------+-------------------------------------------+----------+--------------+-------------
id     | integer       | not null generated by default as identity | plain    |              |
name   | character(20) |                                           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

 id |         name
----+----------------------
1 | aaa
6 | bbb
2 | ccc
(3 rows)

set sequence_option

alter table tb_1162787a alter column id set MINVALUE 0;
select * from all_sequences where sequence_name like'%1162787A%';

查询结果为如下：

sequence_owner |   sequence_name    | min_value | max_value  | increment_by | cycle_flag | cache_size | last_number | scale_flag | extend_flag | session_flag | keep_value
---------------+--------------------+-----------+------------+--------------+------------+------------+-------------+------------+-------------+--------------+------------
VB214          | TB_1162787A_ID_SEQ |         0 | 2147483647 |            1 | N          |          1 |           2 |             |             |       |
(1 row)

5、删除字段identity属性。

alter table tb_1162787a alter column id drop IDENTITY;
\d+ tb_1162787a

返回结果为如下：

                         Table "public.tb_1162787a"
 Column |     Type      | Modifiers | Storage  | Stats target | Description
--------+---------------+-----------+----------+--------------+------------
 id     | integer       | not null  | plain    |              |
 name   | character(20) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

VastbaseG100

ALTER TABLE

功能描述

注意事项

语法格式

参数说明

示例

相关链接

弹框头部