postgresql数据库|数据库实操----表复制详解

前言：

通常情况下，我们对数据库的增删改查的时候，为了确保数据的安全，需要备份表，那么，一种方法是通过pg_dump 这个工具做SQL转储操作，此方法比较复杂，麻烦，但十分的安全，可靠性也基本是百分百，但对于大表备份，十分耗时并且可能有锁表的情况发生，另一种常用方法是复制原表，当表数据不是很大的时候，此方式非常方便，快捷。

postgresql的表复制是和oracle或者MySQL有所不同的，但，差异不是太大。

本文将就表复制这一操作做一个详细的解读，计划使用常用的emp表来做示例。

一，

示例的emp表的建立

####注：此建表SQL语句适用于postgresql

-- 创建数据表CREATE TABLE dept (deptno    NUMERIC CONSTRaiNT PK_DEPT PRIMARY KEY,dname    VARCHAR(14) ,loc        VARCHAR(13) ) ;CREATE TABLE emp (empno    NUMERIC CONSTRAINT PK_EMP PRIMARY KEY,ename    VARCHAR(10),job        VARCHAR(9),mgr        NUMERIC,hiredate    DATE,sal        NUMERIC(7,2),comm    NUMERIC(7,2),deptno    NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT );CREATE TABLE bonus (enamE    VARCHAR(10)    ,job        VARCHAR(9)  ,sal        NUMERIC,comm    NUMERIC ) ;CREATE TABLE salgrade ( grade        NUMERIC,losal        NUMERIC,hisal        NUMERIC );-- 插入测试数据 —— deptINSERT INTO dept VALUES    (10,'ACCOUNTING','NEW YORK');INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');INSERT INTO dept VALUES    (30,'SALES','CHICAGo');INSERT INTO dept VALUES    (40,'OPERATioNS','BOSTON');-- 插入测试数据 —— empINSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);-- 插入测试数据 —— salgradeINSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);-- 事务提交COMMIT;

二，

示例表的介绍

观察emp表，关注此表都有哪些约束，外键，主键

emp表创建在test这个数据库内

test=# \cYou are now connected to database "test" as user "postgres".test=# \dt          List of relations Schema |   Name   | Type  |  Owner   --------+----------+-------+---------- public | bonus    | table | postgres public | dept     | table | postgres public | emp      | table | postgres public | salgrade | table | postgres public | tmp      | table | postgres(5 rows)

有两个索引：

test=# \di              List of relations Schema |  Name   | Type  |  Owner   | Table --------+---------+-------+----------+------- public | pk_dept | index | postgres | dept public | pk_emp  | index | postgres | emp

emp表有一个btree主键，一个关联dept（部门）的外键，empno字段有一个非空约束。

test=# \d emp                        Table "public.emp"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | Indexes:    "pk_emp" PRIMARY KEY, btree (empno)Foreign-key constraints:    "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

OK，emp的结构现在基本是清晰明了的了。

三，

复制表的六种方式

第一种

as select 

 全表复制----复制表结构和表数据，但不包括约束，主键，索引，外键

###此种方法会锁表，请谨慎操作大表

test=# create table emp1 as select * from emp;SELECT 14

查看复制出来的emp1表各项数据：

test=# select * from emp1; empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno -------+--------+-----------+------+------------+---------+---------+--------  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10(14 rows)

 可以看到，emp1复制成功了emp的表结构和表数据，但索引，主键，外键，约束没有了

由此，我们可以得出结论，该表复制方法并不能做到百分百的复制，索引和主外键以及约束是不复制的，表数据和表结构都会完整复制。

究其原因，索引和主外键以及约束的名称是不可重复的，postgresql是没有办法复制的。

test=# \d emp1                        Table "public.emp1"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           |          |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | 

test=# alter table emp1 add constraint pk_emp primary key(empno);ERROR:  relation "pk_emp" already exists

第二种表复制

as table

全表复制-----复制表结构和表数据，但不包括约束，主键，索引，外键

test=# create table emp2 as table emp;SELECT 14

和as select基本没有区别，可以只复制表结构而不复制表数据---with no data：

test=# create table emp3 as  table emp with no data;CREATE TABLE AStest=# test=# \dt          List of relations Schema |   Name   | Type  |  Owner   --------+----------+-------+---------- public | bonus    | table | postgres public | dept     | table | postgres public | emp      | table | postgres public | emp1     | table | postgres public | emp2     | table | postgres public | emp3     | table | postgres public | salgrade | table | postgres public | tmp      | table | postgres(8 rows)test=# select * from emp3; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+--------(0 rows)

第三种表复制：

into复制 

全表复制----复制表结构和表数据，但不包括约束，主键，索引，外键

###此种方法会锁表，请谨慎操作大表

test=# select * into emp4 from emp;SELECT 14test=# \d emp4                        Table "public.emp4"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           |          |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | test=# select * from emp4; empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno -------+--------+-----------+------+------------+---------+---------+--------  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10(14 rows)

第四种表复制：

llike复制

----此方法非常重要，可以include 指定复制索引，主键，约束，但不包含外键，只复制表结构，不复制表数据

est=# create table emp5 (like emp);CREATE TABLEtest=# \d emp5                        Table "public.emp5"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | test=# select * from emp5; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+--------(0 rows)

如果希望索引、主键约束和唯一约束被复制的话，那么需要指定INCLUDING INDEXES

• including constraints ：CHECK约束
• including indexes ：主键约束 和索引约束
• including comments：注释
• including defaults：复制序列 （复制default now(),sequence这类）

test=# create table emp7 (like emp INCLUDING INDEXES INCLUDING DEFAULTS);CREATE TABLEtest=# select * from emp7; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+--------(0 rows)test=# \d emp7                        Table "public.emp7"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | Indexes:    "emp7_pkey" PRIMARY KEY, btree (empno)

第五种复制

继承复制

这种复制表的方法和其他方法有所区别，任何针对父表的修改通常也会自动修改子表，可以简单理解为单向映射，不可对子表更改，子表存在的时候不可以删除父表。

test=# create table emp6 (note text not null) inherits (emp); CREATE TABLEtest=# \d emp6                        Table "public.emp6"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          |  note     | text                  |           | not null | Inherits: emptest=# select * from emp6; empno | ename | job | mgr | hiredate | sal | comm | deptno | note -------+-------+-----+-----+----------+-----+------+--------+------(0 rows)

此时对父表的更改将会作用到继承表：

test=# alter table emp add column city varchar;ALTER TABLEtest=# \d emp6                        Table "public.emp6"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          |  note     | text                  |           | not null |  city     | character varying     |           |          | Inherits: emptest=# alter table emp6 drop column city;ERROR:  cannot drop inherited column "city"test=# drop table emp;ERROR:  cannot drop table emp because other objects depend on itDETAIL:  table emp6 depends on table empHINT:  Use DROP ... CASCADE to drop the dependent objects too.

第六种表复制：

insert 复制

----也就是填充表数据的方法，此方法需要复制目标存在，因此，可以利用第四种方法 ，  先  生成表结构，然后填充数据

以上面第四种方法创建的emp7表为例，填充emp表的数据到emp7表内：

test=# insert into emp7 select * from emp;INSERT 0 14

test=# select * from emp7; empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno -------+--------+-----------+------+------------+---------+---------+--------  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10  7566 | JONES1 | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20(14 rows)test=# \d emp7                        Table "public.emp7"  Column  |         Type          | Collation | Nullable | Default ----------+-----------------------+-----------+----------+--------- empno    | numeric               |           | not null |  ename    | character varying(10) |           |          |  job      | character varying(9)  |           |          |  mgr      | numeric               |           |          |  hiredate | date                  |           |          |  sal      | numeric(7,2)          |           |          |  comm     | numeric(7,2)          |           |          |  deptno   | numeric(2,0)          |           |          | Indexes:    "emp7_pkey" PRIMARY KEY, btree (empno)

可以看到，主键名称是表名，是btree类型的，约束存在的，只是缺少一个外键，因此，重新创建一个外键，就基本是%100复制了。

总结：

复制表推荐使用第四个方法和第六个方法结合，会省掉一点点麻烦，如果需要复制表强一致的话。

需要注意，表复制会导致锁表，因此，大表需要谨慎操作。

来源地址：https://blog.csdn.net/alwaysbefine/article/details/129832906