PostgreSQL-copy 复制数据

原创

小宝大人 2023-05-15 16:37:56 博主文章分类：PostgreSQL ©著作权

文章标签 postgresql copy 表复制 文章分类 Oracle 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者小宝大人的原创作品，请联系作者获取转载授权，否则将追究法律责任

官方文档是这么说的：COPY — copy data between a file and a table.翻译过来就是，在一个文档和一个表之间复制数据。

关于官方文档的详细描述，这里不做叙述。以下是帮助：

1 官方帮助

postgres=# \h copy
Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

URL: https://www.postgresql.org/docs/14/sql-copy.html

postgres=#

2 一个粟子

2.1 准备数据

postgres=# truncate table t_serial;
TRUNCATE TABLE
postgres=# select setval('t_serial_id_seq',1);
 setval
--------
      1
(1 row)

postgres=# insert into t_serial(name) select name from (select generate_series(1,1000),substring('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜',(random()*5)::int,(random()*20)::int) as name) a;
INSERT 0 1000
postgres=# select * from t_serial;
  id  |                   name
------+------------------------------------------
    2 | 赵钱孙李周吴郑
    3 | 钱孙李周吴
    4 | 赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨
    5 | 赵
    6 | 李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
    7 | 李周吴郑王冯陈褚卫蒋沈韩杨
    8 | 孙李周吴郑王冯陈褚卫蒋沈韩杨
    9 | 钱孙李周吴郑王冯陈褚卫蒋沈
   10 | 孙李周
   11 | 孙李周吴郑
   12 | 赵钱孙李周吴郑王冯陈褚卫
   13 | 李周吴郑王冯陈褚卫蒋沈韩杨
   14 | 李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
   15 | 李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
   16 | 赵钱
   17 | 周吴郑
   18 | 李周
   19 | 李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施
   20 |
   21 | 孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
   22 | 周吴郑王
   23 | 孙李周吴郑王冯陈
   24 | 孙李周吴郑王冯陈褚卫蒋沈
   25 | 赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱
   26 | 李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
   27 | 孙李周吴郑王冯陈褚卫蒋沈
   28 | 李周
   29 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
   30 | 赵
   31 | 钱孙李周吴郑王冯
... ...

2.2 把一个表复制到客户端

postgres=# copy t_serial to '/home/postgres/t_serial.sql';
COPY 1000
postgres=# \q
[postgres@pgccc01 ~]$ pwd
/home/postgres
[postgres@pgccc01 ~]$ ll
total 40
-rw-------. 1 postgres postgres  2018 May 12 16:52 logfile
-rw-r--r--  1 postgres postgres 34281 May 15 16:19 t_serial.sql
[postgres@pgccc01 ~]$ more t_serial.sql
2       赵钱孙李周吴郑
3       钱孙李周吴
4       赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨
5       赵
6       李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
7       李周吴郑王冯陈褚卫蒋沈韩杨
8       孙李周吴郑王冯陈褚卫蒋沈韩杨
9       钱孙李周吴郑王冯陈褚卫蒋沈
10      孙李周
11      孙李周吴郑
12      赵钱孙李周吴郑王冯陈褚卫
13      李周吴郑王冯陈褚卫蒋沈韩杨
14      李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
15      李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
16      赵钱
17      周吴郑
18      李周
19      李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施
20
21      孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
22      周吴郑王
23      孙李周吴郑王冯陈
24      孙李周吴郑王冯陈褚卫蒋沈
25      赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱
26      李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
27      孙李周吴郑王冯陈褚卫蒋沈
28      李周
29      钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
30      赵
31      钱孙李周吴郑王冯
32      孙李周吴郑王
33      钱孙李周吴郑王
... ...
[postgres@pgccc01 ~]$

2.3 使用竖线（|）作为域定界符把一个表复制到客户端

postgres=# copy t_serial to '/home/postgres/t_serial.sql' (DELIMITER '|');
COPY 1000
postgres=# exit
[postgres@pgccc01 ~]$ ll
total 40
-rw-------. 1 postgres postgres  2018 May 12 16:52 logfile
-rw-r--r--  1 postgres postgres 34281 May 15 16:24 t_serial.sql
[postgres@pgccc01 ~]$ more t_serial.sql
2|赵钱孙李周吴郑
3|钱孙李周吴
4|赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨
5|赵
6|李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
7|李周吴郑王冯陈褚卫蒋沈韩杨
8|孙李周吴郑王冯陈褚卫蒋沈韩杨
9|钱孙李周吴郑王冯陈褚卫蒋沈
10|孙李周
11|孙李周吴郑
12|赵钱孙李周吴郑王冯陈褚卫
13|李周吴郑王冯陈褚卫蒋沈韩杨
14|李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
15|李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何
16|赵钱
17|周吴郑
18|李周
19|李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施
20|
21|孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
22|周吴郑王
23|孙李周吴郑王冯陈
24|孙李周吴郑王冯陈褚卫蒋沈
25|赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱
26|李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
27|孙李周吴郑王冯陈褚卫蒋沈
28|李周
29|钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
30|赵
31|钱孙李周吴郑王冯
32|孙李周吴郑王
33|钱孙李周吴郑王
[postgres@pgccc01 ~]$

2.4 只把名称以 '钱' 开头的复制到一个文件中

postgres=# COPY (SELECT * FROM t_serial WHERE name LIKE '钱%') TO '/home/postgres/t_serial_qian.copy';
COPY 203
postgres=# \q
[postgres@pgccc01 ~]$ pwd
/home/postgres
[postgres@pgccc01 ~]$ ll
total 48
-rw-------. 1 postgres postgres  2018 May 12 16:52 logfile
-rw-r--r--  1 postgres postgres  7157 May 15 16:26 t_serial_qian.copy
-rw-r--r--  1 postgres postgres 34281 May 15 16:24 t_serial.sql
[postgres@pgccc01 ~]$ more t_serial_qian.copy
3       钱孙李周吴
9       钱孙李周吴郑王冯陈褚卫蒋沈
29      钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
31      钱孙李周吴郑王冯
33      钱孙李周吴郑王
34      钱孙李周吴郑王冯陈褚卫蒋
51      钱孙李周吴郑
55      钱孙李周
60      钱孙李周
65      钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
69      钱孙李
74      钱孙李周
78      钱孙李周吴郑王冯陈褚卫蒋
79      钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦
81      钱孙李周吴郑王冯
88      钱孙李
93      钱孙李周吴郑王冯陈褚卫
94      钱孙李周吴郑王冯陈褚卫
100     钱孙李周吴郑王
102     钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦
104     钱孙李周吴郑王冯陈褚卫蒋
105     钱孙李
116     钱孙李周吴郑王冯陈褚卫蒋沈
118     钱孙李周吴郑王冯
242     钱孙李周吴郑王冯陈褚
135     钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
136     钱孙李周吴郑王冯陈褚卫蒋沈韩
139     钱孙李周吴郑王冯陈褚卫蒋沈韩杨
142     钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤
154     钱孙李周吴郑王冯陈褚
156     钱孙李周吴郑王冯陈褚卫
157     钱孙李周吴郑
[postgres@pgccc01 ~]$

2.5 从一个文件中复制数据到 t_serial 表中

postgres=# truncate table t_serial;
TRUNCATE TABLE
postgres=# select * from t_serial;
 id | name
----+------
(0 rows)

postgres=# copy t_serial from '/home/postgres/t_serial_qian.copy';
COPY 203
postgres=# select * from t_serial;
 id  |                   name
-----+------------------------------------------
   3 | 钱孙李周吴
   9 | 钱孙李周吴郑王冯陈褚卫蒋沈
  29 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
  31 | 钱孙李周吴郑王冯
  33 | 钱孙李周吴郑王
  34 | 钱孙李周吴郑王冯陈褚卫蒋
  51 | 钱孙李周吴郑
  55 | 钱孙李周
  60 | 钱孙李周
  65 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
  69 | 钱孙李
  74 | 钱孙李周
  78 | 钱孙李周吴郑王冯陈褚卫蒋
  79 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦
  81 | 钱孙李周吴郑王冯
  88 | 钱孙李
  93 | 钱孙李周吴郑王冯陈褚卫
  94 | 钱孙李周吴郑王冯陈褚卫
 100 | 钱孙李周吴郑王
 102 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦
 104 | 钱孙李周吴郑王冯陈褚卫蒋
 105 | 钱孙李
 116 | 钱孙李周吴郑王冯陈褚卫蒋沈
 118 | 钱孙李周吴郑王冯
 242 | 钱孙李周吴郑王冯陈褚
 135 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许
 136 | 钱孙李周吴郑王冯陈褚卫蒋沈韩
 139 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨
 142 | 钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤
 154 | 钱孙李周吴郑王冯陈褚
postgres=#