前言:
通常情况下,我们对数据库的增删改查的时候,为了确保数据的安全,需要备份表,那么,一种方法是通过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
);
-- 插入测试数据 —— dept
INSERT 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');
-- 插入测试数据 —— emp
INSERT 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);
-- 插入测试数据 —— salgrade
INSERT 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=# \c
You 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 AS
test=#
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 14
test=# \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 TABLE
test=# \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 TABLE
test=# 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 TABLE
test=# \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: emp
test=# select * from emp6;
empno | ename | job | mgr | hiredate | sal | comm | deptno | note
-------+-------+-----+-----+----------+-----+------+--------+------
(0 rows)
此时对父表的更改将会作用到继承表:
test=# alter table emp add column city varchar;
ALTER TABLE
test=# \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: emp
test=# 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 it
DETAIL: table emp6 depends on table emp
HINT: 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复制了。
总结:
复制表推荐使用第四个方法和第六个方法结合,会省掉一点点麻烦,如果需要复制表强一致的话。
需要注意,表复制会导致锁表,因此,大表需要谨慎操作。