PostgreSQL create table as与like用法

下面这篇博客很详细

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

转自：http://www.linuxidc.com/Linux/2016-09/135540.htm

前言

项目中有表复制的需求，而且是动态复制，即在存储过程里根据参数数组的值循环复制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。

 

posted @ 2017-05-19 16:33  狂神314  阅读(10339)  评论(0)  编辑  收藏  举报