526互联

KingbaseES V8R6 几种不同的表复制方式

发布时间 2023-06-06 15:38:02作者: KINGBASE研究院

前言

当数据库遇到未知问题，有时候无法入手分析，在非生产数据库或者征得客户同意获得特殊时间，需要重建表解决，下面提供了多种不同的复制表的方法，我们了解一下他们的差异。

测试

1、CREATE TABLE AS SELECT 语句用于复制表结构和数据，但是不会复制索引。

我们可以使用以下语句基于 t1表 复制一个新表 emp1：

CREATE TABLE emp1
AS
SELECT * FROM t1;

TEST=# \d emp1
                Table "public.emp1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

TEST=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "pk_t1" PRIMARY KEY, btree (id)


如果只复制表结构，不复制数据，可以增加 WITH NO DATA 子句：
CREATE TABLE emp2
AS
SELECT * FROM t1
WITH NO DATA;

或者如下语法，例如：这种复制方法不会创建任何索引或者约束。
CREATE TABLE emp2
AS
SELECT * FROM t1
WHERE FALSE;
或
CREATE TABLE emp2
AS
SELECT * FROM t1
where 1=2;


2、CREATE TABLE LIKE 语句也可以用于复制表结构，这种方法也不会复制数据，会复制字段的 NOT NULL 约束。

CREATE TABLE emp2
(LIKE t1);

TEST=# \d emp2;
                Table "public.emp2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |


3、CREATE TABLE AS TABLE 语句可以复制表结构和数据，这种语法不会复制索引、约束等。如果不复制数据，使用 WITH NO DATA子句

CREATE TABLE emp2
AS
TABLE t1
WITH NO DATA;

TEST=# \d emp2;
                Table "public.emp2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |



4、SELECT INTO 语句可以复制表结构和数据，但是不包含索引，约束等。例如：

SELECT * INTO emp2 FROM t1;

TEST=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "pk_t1" PRIMARY KEY, btree (id)

TEST=# \d emp2
                Table "public.emp2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |


 CREATE TABLE AS 和 SELECT INTO 语句实现同样的效果，推荐使用CREATE TABLE AS，它更常见。

5、CREATE TABLE INHERITS 语句，用于继承表结构。数据不会复制，任何针对父表的修改，子表也会自动修改。

另外，这种方法还可以为子表定义额外的字段。例如：
增加约束：
ALTER TABLE t1 ADD CHECK (id <> '');

CREATE TABLE emp2 (
     name text NOT NULL
)
INHERITS ( t1 );
其中，name 是我们额外定义的字段，其他字段继承 t1表。\d查看，emp2表有关键字Inherits: t1。t1表有关键字Number of child tables: 1 (Use \d+ to list them.)
check约束，非空约束继承到了子表，主键不能继承到子表。

TEST=# \d emp2;
                Table "public.emp2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           | not null |
Check constraints:
    "t1_id_check" CHECK (id <> NULL::integer)
Inherits: t1


TEST=# \d t1;
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "pk_t1" PRIMARY KEY, btree (id)
Check constraints:
    "t1_id_check" CHECK (id <> NULL::integer)
Number of child tables: 1 (Use \d+ to list them.)

父表增加列应用到了子表上
alter table t1 ADD COLUMN price int;
ALTER TABLE t1 DROP CONSTRAINT t1_id_check;

TEST=# \d emp2
                Table "public.emp2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           | not null |
 price  | integer |           |          |
Inherits: t1

TEST=# \d t1;
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 price  | integer |           |          |
Indexes:
    "pk_t1" PRIMARY KEY, btree (id)
Number of child tables: 1 (Use \d+ to list them.)