CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表，该表由命令执行者所有。

Vastbase在PostgreSQL兼容模式下，支持以下用法：

本文档仅介绍CREATE TABLE在PostgreSQL兼容模式下的特性用法，创建表的更多用法介绍详见开发者指南中的SQL语法CREATE TABLE。

增加了对普通行存表的继承功能，新创建的表可以自动继承指定的表的所有列。父表可以是普通表，也可以是外部表。

父表上所有检查约束和非空约束都将自动被后代继承，其他类型的约束，比如：唯一约束，主键和外键约束则不会被继承。
支持指定Identity列，由此会自动创建一个Sequence（自增序列），这个自增序列可以自动生成Identity字段的值，并且被指定Identity的字段是NOT NULL字段。
支持CREATE TABLE ... LIKE INCLUDING IDENTITY语句拷贝表中的Identity字段。新表创建成功后会自动创建一个新的Sequence序列。

注意事项

上述功能仅在数据库兼容模式为PostgreSQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='PG'）。
仅对普通行存表支持继承功能，禁止分区表、ustore表、列存表、行压缩表、全局临时表或外部表使用继承功能。
不能对有继承关系的父表或者子表的约束做启用或禁用操作。
子表不能修改从父表继承的属性和约束。
子表存在的情况下，删除父表会报错，但是删除父表时使用cascade则可删除成功。
对父表的数据查询、数据修改或者模式修改的命令（SELECT、UPDATE、DELETE、大部分ALTER TABLE的变体，但是INSERT或者ALTER TABLE …RENAME不在此范围内）默认会将子表包含在内，支持使用ONLY选项来排除子表。
指定Identity的字段带有NOT NULL约束。
一个表只能有一个IDENTITY标识列。当删除了identity字段，则对应的序列也会被自动删除。
对IDENTITY列进行INSERT和UPDATE修改的具体行为受CREATE TABLE时指定的GENERATED {ALWAYS | BY DEFAULT } AS的影响，详情请参见本文的参数说明。

语法格式

CREATE TABLE table_name[INHERITS(parent_table) (column_name data_type
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
);

其中sequence_options可以是：

[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE | NOMINVALUE]
[ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE]
[ START [ WITH ] start ]
[ CACHE cache ]
[ [ NO ] CYCLE | NOCYCLE]
[ OWNED BY { table_name.column_name | NONE } ]

参数说明

table_name

要创建的表名。
column_name

新表中要创建的字段名。
data_type

字段的数据类型。

为标识列指定的有效数据类型应该为整型。
parent_table

要继承的父表，可以是普通表也可以是外部表。
GENERATED {ALWAYS | BY DEFAULT } AS

该子句将列创建为标识列，由此会自动创建一个Sequence（自增序列），这个自增序列可以自动生成字段的值。
- GENERATED ALWAYS AS
  
  如果指定了ALWAYS，对于INSERT语句包含 OVERRIDING SYSTEM VALUE子句的情况下，才会接受用户自定义的值，否则都会使用自增序列的值。详情可见示例5。
  
  对于UPDATE语句，那么只会接受自增序列的值。
- GENERATED BY DEFAULT AS
  
  如果指定了BY DEFAULT，INSERT语句和UPDATE语句可以接受用户自定义的值。详情可见示例6
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不应该从属于特定表。

示例

示例1 继承普通行存表功能。

1、创建父表和子表。

create table parent(id int);
create table child1() inherits(parent);
create table child2() inherits(parent);

2、插入数据。

insert into parent values(1);
insert into child1 values(2);
insert into child2 values(3);

3、查询父表。

select * from parent;

返回结果为如下：

 id
----
  1
  2
  3
(3 rows)

4、使用ONLY选项查询父表。

select * from only parent;

返回结果为如下：

 id
----
  1
(1 row)

5、给父表添加属性。

alter table parent add name text;

6、查询表结构。

\d+ parent
\d+ child1

返回结果为如下：

                        Table "public.parent"
 Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              |
 name   | text    |           | extended |              |
Child tables: child1,
              child2
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

                        Table "public.child1"
 Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              |
 name   | text    |           | extended |              |
Inherits: parent
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

7、修改子表中继承的属性。

alter table child1 alter column name type varchar(10);

返回结果为报错，表示子表无法修改从父表继承的属性:

ERROR:  cannot alter inherited column "name"

8、删除父表。

drop table parent;

返回结果报错，即当任何一个子表存在时，父表都不能被删除，但是可以使用cascade选项删除父表。

ERROR:  cannot drop table parent because other objects depend on it
DETAIL:  table child1 depends on table parent
table child2 depends on table parent
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

9、使用cascade选项删除父表。

drop table parent cascade;

示例3 建表时将字段id指定为always属性的自增列。

1、创建测试表并查看表结构。

create table tb_1162754(id int GENERATED ALWAYS AS IDENTITY,name text);
\d+ tb_1162754;

返回结果为如下：

                                    Table "public.tb_1162754"
 Column |  Type   |               Modifiers               | Storage  | Sta
ts target | Description
--------+---------+---------------------------------------+----------+----
----------+-------------
 id     | integer | not null generated always as identity | plain    |
          |
 name   | text    |                                       | extended |
          |
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

2、向测试表中插入数据。

未指定overriding system value，则插入失败。

insert into tb_1162754 values(2,'bbb');

插入失败，返回结果为如下：

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

指定overriding system value，插入成功。

insert into tb_1162754 overriding system value values(2,'bbb');
select * from tb_1162754;

返回结果如下：

id | name
----+------
2 | bbb
(1 row)

示例2： 建表时将字段id指定identify属性，由用户为列指定值。

1、创建测试表并查看表结构。

create table tb_1162758(id int GENERATED BY DEFAULT AS IDENTITY,name text);
\d+ tb_1162758;

返回结果为如下：

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

2、查看测试表插入数据。

insert into tb_1162758 values(2,'bbb');
insert into tb_1162758 overriding system value values(2,'bbb');
insert into tb_1162758(name) values('aaa'),(null),('');
select * from tb_1162758;

返回结果如下：

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

3、更新表字段。

update tb_1162758 set id=5 where name='';
select * from tb_1162758;

更新成功，查询返回结果为如下：

----+------
  2 | bbb
  2 | bbb
  1 | aaa
  2 |
  5 |
(5 rows)

示例4： 创建表指定BY DEFAULT，且指定起始值和步长，步长为负数。

1、创建测试表并查看表结构。

create table tb_1162759(id int GENERATED BY DEFAULT AS IDENTITY(START WITH -2 INCREMENT BY -2),name text);
\d+ tb_1162759;

返回结果为如下：

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

2、查看测试表插入数据。

insert into tb_1162759 values(-2,'bbb');
insert into tb_1162759 overriding system value values(-2,'bbb');
select * from tb_1162759;
insert into tb_1162759(name)values('aaa'),(null),('');
select * from tb_1162759;

返回结果如下：

 id | name
----+------
 -2 | bbb
 -2 | bbb
(2 rows)

 id | name
----+------
 -2 | bbb
 -2 | bbb
 -2 | aaa
 -4 |
 -6 |
(5 rows)

3、更新表字段。

update tb_1162759 set id=3;
select * from tb_1162759;

更新成功，查询返回结果为如下：

 id | name
----+------
  3 | bbb
  3 | bbb
  3 | aaa
  3 |
  3 |
(5 rows)

示例5 创建GENERATED ALWAYS AS IDENTITY字段。

1、创建测试表。

CREATE TABLE tab_id (
     id    integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name   varchar(40)
);

2、当INSERT命令使用Default值时，会使用自增序列的值。

INSERT INTO tab_id(name) VALUES('Li');
INSERT INTO tab_id VALUES(DEFAULT, 'Liu');
SELECT * FROM tab_id;

结果为如下：

 id | name 
----+------
  1 | Li
  2 | Liu
(2 rows)

3、当INSERT命令制定了用户自定义的值时，并且没有加OVERRIDING 时，会报错并提示使用OVERRIDING SYSTEM VALUE：

INSERT INTO tab_id VALUES(100, 'Wang');

返回结果为如下：

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

4、当INSERT命令指定了用户自定义的值，并且增加了OVERRIDING SYSTEM VALUE，会接受用户自定义的值：

INSERT INTO tab_id OVERRIDING SYSTEM VALUE VALUES(100 , 'Wang');
SELECT * FROM tab_id;

返回结果为如下：

 id  | name 
-----+------
   1 | Li
   2 | Liu
 100 | Wang
(3 rows)

5、INSERT命令还可以指定OVERRIDING USER VALUE，这时会忽略用户自定义的值，使用自增序列的值：

INSERT INTO tab_id OVERRIDING USER VALUE VALUES(300 , 'Zhang');
SELECT * FROM tab_id;

返回结果为如下：

 id  | name  
-----+-------
   1 | Li
   2 | Liu
 100 | Wang
   3 | Zhang
(4 rows)

6、当UPDATE命令修改Identity字段，Identity字段只能被update成DEFAULT的值，也就是自增序列的值，否则报错：

UPDATE tab_id SET id=400 WHERE id=100;
UPDATE tab_id SET id=DEFAULT WHERE id=100;
SELECT * FROM tab_id;

返回结果为如下：

ERROR:  column "id" can only be updated to DEFAULT
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

 id | name  
----+-------
  1 | Li
  2 | Liu
  3 | Zhang
  4 | Wang
(4 rows)

7、DROP掉该表后，自增序列也会自动被DROP。

DROP TABLE tab_id;
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

返回结果为如下：

 relname 
---------
(0 rows)

示例6 创建GENERATED BY DEFAULT AS IDENTITY字段。

1、创建测试表。

CREATE TABLE tab_id (
    id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name   varchar(40)
);

2、当INSERT命令使用Default值时，会使用自增序列的值：

INSERT INTO tab_id(name) VALUES('Li');
INSERT INTO tab_id VALUES(DEFAULT, 'Liu');
SELECT * FROM tab_id;

返回结果为如下：

 id | name 
----+------
  1 | Li
  2 | Liu
(2 rows)

3、当INSERT命令制定了用户自定义的值时，并且没有加OVERRIDING 时，会直接接受用户自定义的值。与使用OVERRIDING SYSTEM VALUE是等效：

INSERT INTO tab_id VALUES(100, 'Wang');
INSERT INTO tab_id OVERRIDING SYSTEM VALUE VALUES(100 , 'Wang');
INSERT INTO tab_id OVERRIDING SYSTEM VALUE VALUES(200 , 'Wang');
SELECT * FROM tab_id;

返回结果为如下：

ERROR:  duplicate key value violates unique constraint "tab_id_pkey"
DETAIL:  Key (id)=(100) already exists.

 id  | name 
-----+------
   1 | Li
   2 | Liu
 100 | Wang
 200 | Wang
(4 rows)

4、当INSERT命令指定OVERRIDING USER VALUE时，会使用自增序列的值，忽略用户自定义的值：

INSERT INTO tab_id OVERRIDING USER VALUE VALUES(300 , 'Zhang');
SELECT * FROM tab_id;

返回结果为如下：

 id  | name  
-----+-------
   1 | Li
   2 | Liu
 100 | Wang
 200 | Wang
   3 | Zhang
(5 rows)

5、对于UPDATE操作，当定义了BY DEFAULT，可以接受用户自定义的值：

UPDATE tab_id SET id=400 WHERE id=100;
SELECT * FROM tab_id;

返回结果为如下：

 id  | name  
-----+-------
   1 | Li
   2 | Liu
 200 | Wang
   3 | Zhang
 400 | Wang
(5 rows)

6、也可UPDATE为DEFAULT的操作，使用自增序列的值：

UPDATE tab_id SET id=DEFAULT WHERE id=400;
SELECT * FROM tab_id;

返回结果为如下：

 id  | name  
-----+-------
   1 | Li
   2 | Liu
 200 | Wang
   3 | Zhang
   4 | Wang
(5 rows)

VastbaseG100

CREATE TABLE

功能描述

注意事项

语法格式

参数说明

示例

相关链接

弹框头部