批量copy_详解PostgreSQL批量导入数据--COPY命令

概述

最近有需要对数据进行迁移的需求，主要是从excel表导入到PG数据库表，这里我是从postgres本身支持的copy命令来实现的，所以主要介绍一下COPY的命令..

官方文档：http://postgres.cn/docs/11/sql-copy.html

---

一、copy命令

COPY在PostgreSQL表和文件之间交换数据。 COPY TO把一个表的所有内容都拷贝到一个文件，而COPY FROM从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。 COPY TO还能拷贝SELECT查询的结果。

如果声明了一个字段列表，COPY将只在文件和表之间拷贝已声明字段的数据。 如果表中有任何不在字段列表里的字段，那么COPY FROM将为那些字段插入缺省值。

带文件名的COPY指示PostgreSQL服务器直接从文件中读写数据。 如果声明了文件名，那么服务器必须可以访问该文件，而且文件名必须从服务器的角度声明。 如果使用了PROGRAM选项，则服务器会从指定的这个程序进行输入或是写入该程序作为输出。 如果使用了STDIN 或STDOUT选项，那么数据将通过客户端和服务器之间的连接来传输。

copy命令可以操作的文件类型有：txt、sql、csv、压缩文件、二进制格式

1、导出CSV命令

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

2、导入CSV命令

COPY table_name [ ( column_name [, ...] ) ]    FROM { 'filename' | PROGRAM 'command' | STDIN }    [ [ WITH ] ( option [, ...] ) ]

3、常用参数说明

注意：

1)COPY只能用于表，不能用于视图。当然也可以用于COPY (SELECT * FROM viewname) TO ...

2)COPY仅仅处理已指定的特定表；它将不复制数据到子表或从子表中复制数据。 因此比如COPY table TO显示与SELECT * FROM ONLY table相同的数据。 但是COPY (SELECT * FROM table) TO ...可以用于转储在继承层次结构的所有数据。

3)对任何要COPY TO出来的数据必须有查询的权限，对任何要COPY FROM入数据的表必须有插入权限。 对列在命令中的字段拥有列权限也是必须的。

4)COPY命令里面的文件必须是由服务器直接读或写的文件，而不是由客户端应用读写。 因此，它们必须位于数据库服务器上或者可以被数据库服务器所访问，而不是客户端程序。 它们必须被运行PostgreSQL服务器的用户可读或写，而不是客户端程序。 由PROGRAM选项指定的命令必须是由服务器来执行的，而不是客户端程序，必须是由PostgreSQL所属的用户。 COPY在指定一个程序或是命令时只允许数据库超级用户来执行，因为它允许读写任意服务器有权限访问的文件。

5)不要混淆COPY和 psql应用程序中的copy指令。 copy调用COPY FROM STDIN或COPY TO STDOUT，然后把数据抓取/存储到一个psql客户端可以访问的文件中。 因此，使用copy的时候，文件访问权限是由客户端应用程序而不是服务器端决定的。

6)COPY FROM在执行时会触发目标表上所有触发器和检查约束。不过，不会执行规则。

---

二、实例

1、导出demo_employee_target表

$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgresdn   --list schemasset search_path to 'fslvis_schema';  --切换schema,默认publicdt   --list tablescopy demo_employee_source to '/tmp/test.csv' with csv;copy demo_employee_target to '/tmp/test.csv' with csv;

2、导出指定字段

d demo_employee_target  --查看表结构copy demo_employee_target(c_oid,c_code,c_operatetime) to '/tmp/test2.csv' with csv;

3、导出select语句

 COPY (select c_oid,c_code,c_operatetime,c_status from demo_employee_target) TO '/tmp/test3.csv' WITH csv;

4、导入test.csv

 COPY t1 FROM '/tmp/test.csv' WITH csv;

如果导出的时候，指定了header属性，那么在导入的时候，也需要指定：

 COPY t1 FROM '/tmp/test.csv' WITH csv header;

5、 copy命令导入导出数据为sql格式

COPY t1 TO '/tmp/t1.sql';COPY t1 FROM '/tmp/t1.sql';

6、将excel表中的数据导入到Postgresql数据库的某张表中

步骤：

1)将excel表格字段，按照postgresql数据库中表的字段顺序来整理数据，并保存为csv文件。

2)用记事本打开csv文件，另存为UTF-8格式。

3)使用客户端链接postgresql数据库，执行如下脚本，导入csv文件到Postgresql数据表：

 copy t1  from  'd:/test/testdata.csv' delimiter as',' csv quote as '"'

注意:

1)test目录需要赋予postgresql用户可读写的权限，否则会有如下报错信息：ERROR: could not open file "d:/testdata.csv" forwriting:Permission denied

2)csv文件要为utf-8格式，否则导入时可能会有报错：ERROR:invalid bytesequence for encoding "UTF8": 0xcdf5

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~