PostgreSQL 动态表复制（CREATE TABLE AS&CREATE TABLE LIKE）

共计 6870 个字符，预计需要花费 18 分钟才能阅读完成。

前言

项目中有表复制的需求，而且是动态复制，即在存储过程里根据参数数组的值循环复制 n 张结构（约束、索引等）等一致的一组表，PostgreSQL 提供了两种语法来进行表复制，分别是：

1. CREATE TABLE AS
2. CREATE TABLE LIKE

下面就通过一个例子来看看究竟哪一种更好或者说更符合我们的需求。

CREATE TABLE AS

首先看看 CREATE TABLE AS 的用法，在这之前结合一个具体的例子看看，我们需要复制的是这样一张表：

如上图所示，在 PowerDesigner 的物理模型（pdm）中我们可以看到这张表定义了主键和一个外键，再看看它的 ddl 语句：

drop table t_key_event_file_student;

/*==============================================================*/
/* Table: t_key_event_file_student                              */
/*==============================================================*/
create table t_key_event_file_student (id                   SERIAL not null,
key_event_score_student_id INT4                 not null,
file_name            varchar(100)         not null,
file_path            varchar(100)         not null,
constraint PK_T_KEY_EVENT_FILE_STUDENT primary key (id)
);

comment on table t_key_event_file_student is
'关键事件业务表(附件)';

comment on column t_key_event_file_student.id is
'主键';

comment on column t_key_event_file_student.key_event_score_student_id is
'关键事件录入 ID';

comment on column t_key_event_file_student.file_name is
'附件文件名称';

comment on column t_key_event_file_student.file_path is
'附件文件路径';

alter table t_key_event_file_student
   add constraint FK_T_KEY_EV_REF16_T_KEY_EV foreign key (key_event_score_student_id)
      references t_key_event_score_student (id)
      on delete restrict on update restrict;

如上所示，首先理一下这张表都包含了什么东西，我们复制表的同时应带上什么东西。
首先，id 定义成了 SERIAL 类型，那就意味着建表的同时会为我们自动创建一个序列，那么这个 序列在表复制的时候是肯定不能 copy 的 ，因为那样的话将意味着原表和复制的表公用一个序列，明显不合理。其次是约束，我们可以看到上面的 DDL 语句中出现了三种约束，分别是：主键（Primary Key）约束、外键（Foreign Key）约束以及非空（Not Null）约束，很显然，表复制的同时这三种约束 都应存在 ，中间的语句还有若干条 comment（注释），理论上注释内容在表复制的同时 也应该存在，所以简单总结一下我们做表复制的取舍：

1. 所有约束、索引和注释在复制时都应被拷贝。
2. 序列不应拷贝，应当为每一张复制的表单独创建一个新的序列。

搞清楚这些问题后接下来看看 PostgreSQL 的相关支持能为我们实现什么，首先看一下 CREATE TABLE AS，官方是这样描述的：

如上图所示，CREATE TABLE AS 主要做两件事情，分别是建表（CREATE DATA）和填充数据（FILL DATA），下面我们就通过 CREATE TABLE AS 复制一张表试试看。本篇 blog 的示例都会用 t_key_event_file_student 这张表，首先给这张表插入 3 条数据：

接下来运行 CREATE TABLE AS 来复制该表：

create table t_key_event_file_student_100 as select * from t_key_event_file_student;

创建成功后看看它的 DDL 语句：

再看一下这张表的数据：

如上图，首先第一张图可以看到拷贝后的表结构，那我们再回头看看原始表的表结构好做对比：

如上图，这样一比较发现差距还挺大的，CREATE TABLE AS 复制出来的表，所有约束、注释和序列都没有被拷贝，但数据成功拷贝了，就如同官方文档中的描述，显而易见，这与我们的预期相差甚远，所以就不做过多考虑了，接下来看看第二种复制方式——CREATE TABLE LIKE。

CREATE TABLE LIKE

如题，LIKE 不同于 CREATE TABLE AS 语句，它是标准 CREATE TABLE 语句的一个参数项，在官方文档中可以看到：

后面还有对 like_options 的参数值枚举：

如上图，用法很简单，即 INCLUDING 后面 6 个值或者 EXCLUDING 后面 6 个值，例如：INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING COMMENTS，这就是一种配置方式。直观起见我们依旧通过举例说明，下面通过 CREATE TABLE LIKE 来完成复制：

create table t_key_event_file_student_101 (like t_key_event_file_student);

复制成功后再看一下表结构的 DDL 语句和数据：

如上图，同 CREATE TABLE AS 不同的是这次复制成功拷贝了所有 NOT-NULL 约束，并且没有拷贝表数据，这也渐渐接近了我们的需求，并且验证了一点，就是CREATE TABLE LIKE 并不会复制任何数据，而 CREATE TABLE AS 则会复制数据。回顾一下我们的需求：

1. 所有约束、索引和注释在复制时都应被拷贝。
2. 序列不应拷贝，应当为每一张复制的表单独创建一个新的序列。

接下来就要通过 LIKE 选项的 INCLUDING 关键字来实现了后续需求了，官方文档中对于 CREATE TABLE 的 like_options 有几小段详细的解释：

LIKE source_table [like_option …]
The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

如上所示，当使用 LIKE 子句做表复制时，默认会自动拷贝所有字段、字段类型以及它们的 NOT-NULL 约束，这也就解释了刚才为什么会成功复制 NOT-NULL 约束。

Default expressions for the copied column definitions will only be copied if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having null defaults.

如上所示，当指定了 INCLUDING DEFAULTS 时默认的列定义均会被拷贝，这么说的话由于原始表的主键是 SERIAL 类型，创建后 id 会绑定序列，那么序列是否也会被拷贝呢？测试一下：

create table t_key_event_file_student_102 (like t_key_event_file_student INCLUDING DEFAULTS);

接下来看一下 DDL 语句：

没错，与官方的说法一致，由于序列是指定在了列定义（column definitions）上，所以当使用了 INCLUDING DEFAULTS 时它自然会被复制，但这与我们的需求不符，因为我们的需求是每张被复制的表都应创建一个其专属的唯一序列，所以 结论就是不能用 INCLUDING DEFAULTS，继续往下看：

Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified. Indexes, PRIMARY KEY, and UNIQUE constraints on the original table will be created on the new table only if the INCLUDING INDEXES clause is specified. No distinction is made between column constraints and table constraints.

如上所示，NOT-NULL 约束 always copied to the new table，这一点在上面也提过了，它总会被复制。CHECK 约束只有在指定了 INCLUDING CONSTRAINTS 时才会被拷贝，这很好理解，由于我们的原始表并没有 CHECK 约束，所以暂不考虑。 如果希望索引、主键约束和唯一约束被复制的话，那么需要指定 INCLUDING INDEXES，显然这是我们需要的，因为我们的原始表指定了主键约束，还有最后一段：

Comments for the copied columns, constraints, and indexes will only be copied if INCLUDING COMMENTS is specified. The default behavior is to exclude comments, resulting in the copied columns and constraints in the new table having no comments.

如果希望复制注释，那么需要指定 INCLUDING COMMENTS，很明显，这也是我们需要的。至此我们已经可以筛选出我们需要的东西了，下面通过标���看一下：

1. including constraints：没有 CHECK 约束，所以不考虑
2. including indexes：需要主键约束
3. including comments：需要注释
4. including defaults：不需要复制序列，所以不要

结论是我们的 LIKE 选项为：INCLUDING INDEXES INCLUDING COMMENTS，所以这次就能复制一个“最贴近我们需求”的表了：

create table t_key_event_file_student_103 (like t_key_event_file_student INCLUDING INDEXES INCLUDING COMMENTS);

依旧看一下 DDL 语句：

如上图，可以看到这次复制的有 NOT-NULL 约束、主键约束以及注释，这样就完成了我们的表复制，可刚才为什么说创建的是 最贴近我们需求 的表呢？因为到此为止对比需求发现我们可能还少了点东西，原始表中有外键约束，那么该如何复制呢？答案是无法复制，PostgreSQL 官方并不提供外键约束的复制，所以只能自己通过 alter 语句去添加外键约束了，同样序列也是，通过语句手动创建即可，最后就看一下通过 PostgreSQL 的自定义函数完成动态表复制的全过程。

自定义函数实现动态复制

如题，需求是传入一个字符串数组，根据数组的大小（n）来动态复制 n 张表，接下来直接看一下完整的自定义函数代码：

CREATE OR REPLACE FUNCTION "public"."f_inittables1"(arr _text)
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
     scount INTEGER;
   rownum integer := 1;
   currsnum text;
   strSQL text;
BEGIN
        scount:=array_length(arr,1);
      while rownum <= scount LOOP
            currsnum:=arr[rownum];
            RAISE NOTICE '这里是 %', currsnum;
          -- 开始复制
      ---- 建表
      strSQL := 'CREATE TABLE t_self_evaluation'||'_'||currsnum||'
                        (like t_self_evaluation including constraints including indexes including comments);';
      EXECUTE strSQL;
      ---- 添加外键约束
      strSQL :='alter table t_self_evaluation'||'_'||currsnum||'
                                add constraint FK_T_SELF_E_REF12_T_EVALUA_'||currsnum||' foreign key (scheme_id)
                                references t_evaluation_scheme (id)
                                on delete restrict on update restrict;';
            EXECUTE strSQL;
      ---- 指定序列
      strSQL :='create sequence t_self_evaluation_'||currsnum||'_id_seq increment by 1
                                minvalue 1 maxvalue 9223372036854775807 start with 1
                                owned by t_self_evaluation_'||currsnum||'.id';
            EXECUTE strSQL;
            rownum := rownum + 1;
    end LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

ALTER FUNCTION "public"."f_inittables1"(arr _text) OWNER TO "postgres";

如上所示，遍历参数数组，根据数组的值拼接构造表名，同时构造外键名和序列名，在循环的 n 次中 通过 EXECUTE 关键字执行建表语句实现动态建表，下面调用一下试试，传入一个 5 个字符串的数组：

select f_inittables1('{"021","270","271","070","150"}');

运行结束后可以看到控制台成功打印了 RAISE NOTICE 信息：

最后再看一下复制的表：

如上图，可以看到已经完全满足我们的需求了，至此我们的动态表复制就算全部结束了。

总结

简单记录一下 PostgreSQL 中实现动态表复制的全过程，希望对遇到同样问题的朋友有所帮助，The End。

———————————— 华丽丽的分割线 ————————————

在 CentOS 6.5 上编译安装 PostgreSQL 9.3 数据库 http://www.linuxidc.com/Linux/2016-06/132272.htm

CentOS 6.3 环境下 yum 安装 PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htm

PostgreSQL 缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htm

Windows 平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm

Ubuntu 下 LAPP（Linux+Apache+PostgreSQL+PHP)环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htm

Ubuntu 上的 phppgAdmin 安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htm

CentOS 平台下安装 PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htm

PostgreSQL 配置 Streaming Replication 集群 http://www.linuxidc.com/Linux/2014-05/101724.htm

———————————— 华丽丽的分割线 ————————————

PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135540.htm