在很多场景下，我们经常会遇到将某个Excel或Csv文件中的数据，插入到Postgresql。对于这个需求，我们常规的处理办法就是将文件中的数据，按照文件表头名称转换成集合对象然后插入到数据库，当然这对于数据体量不大的文件而言，很显眼没有任何问题，但是如果数据体量一旦上来，将面临如下问题：

将文件中的数据转换为集合对象，就有可能造成内存溢出；就算内存没有溢出，那么这么大的数据，分批插入效率也能难满足需求；

针对上述问题，Postgresql提供了批量插入神器，官方文档对copy给出了如下解释，认为COPY命令针对加载大量行进行了优化;它不如INSERT灵活，但对于大数据负载的开销要小得多。

也就是COPY命令比较适合大量行数据的快速插入，但是灵活度上没有Insert方便，下面主要说明下COPY命令的用法。

(资料图片)

COPY命令用法：导入命令如下:       Copy table_name[(column_name…)] from "filename" [ [WITH] (option [, ...]) ] [where condition]导出命令如下      COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO  "filename"  [ [ WITH ] ( option [, ...] ) ]其中table_name为数据库表名称，我们可以根据文件名称和文件列表头来创建一个合适的表数据。column_name为列名称，也就是我们可指定将数据填充到指定的某些列，如果不指定，则默认将数据列从左至右依次填充到每一个列中。FILE 文件名称，即就是文件的绝对路径。WITH 后面的option有如下选项：FORMAT format_name    文件格式名称，主要有csv、txt、二进制文件FREEZE [ boolean ]           不常用DELIMITER "delimiter_character"     分隔符，比如CSV默认分隔符逗号，txt默认是制表符，二进制文件中禁止使用此选项NULL "null_string"   指定表示空值的字符串。在文本格式中默认为\N，在CSV格式中默认为不加引号的空字符串HEADER [ boolean ]   指定文件包含一个头行，其中包含文件中每个列的名称，一般用在csv文件中，值可以是true、on、1；QUOTE "quote_character"   指定在引用数据值时使用的引号字符，默认是双引号。ESCAPE "escape_character"   FORCE_QUOTE { ( column_name [, ...] ) | * }   FORCE_NOT_NULL ( column_name [, ...] )FORCE_NULL ( column_name [, ...] )ENCODING "encoding_name"    指定对文本的编码格式，默认是客户端编码格式，可通过show client_encoding查看客户端编码；WHERE condition    任何不满足此条件的行都不会被插入到表中。如果将实际行值替换为任何变量引用时该行返回true，则该行满足条件。

比如要将一个test.csv文件导入到数据库，首先根据CSV的表头信息创建数据库表test，字段包括gid、zipcode、telephone、name、address创建成功后，使用copy导入数据：

Copy test from ‘D:/test.csv’ with header DELIMITER ‘,’

注意：小编在使用导入命令时，发现windows操作系统下，文件路径中如果有中文，无法导入，系统会报could not open file for reading: Invalid argument这个错误。

如果gid是自增字段，数据中只有后续的几个字段，则我们可以在插入时，增加列名称：

Copy test(zipcode,telephone,name,address) from ‘D:/test.csv’ with header true DELIMITER ‘,’

此时如果需要将test表中的数据导出，则使用如下命令：

Copy test to ‘D:/test_new.csv’ csv header DELIMITER ‘,’ ENCODING "UTF8"；

对于导出数据中有中文的，特别说明下，如果我们导出的csv中的中文，试用excel查看是乱码的，但是试用其它文本编辑器(比如nodepad++)是好的，这时候只需要在文本编辑器中，将文件编码格式改为UTF-8-BOM就行，这应该是微软自己对文件编码格式的判断问题。

对于上面官方给出的说明文档，需要澄清以下几点：

官方文档的这个说明不知道是我理解错了，还是本身有问题，我按照它的说明使用不分参数，就会报错，比如：

Copy test to ‘D:/test_new.csv’ format csv header DELIMITER ‘,’ ENCODING "UTF8"；

意思是不需要加这个format的key，加上就会报语法错误；

再比如，官方文档指出header后面可以跟true,on,1表示开启，但实际加上就会报错，比如如果你写成下面的方式就会报语法错误：

Copy test to ‘D:/test_new.csv’ csv header true DELIMITER ‘,’ ENCODING "UTF8"；

因此只需要加上header，默认就是true，不加就是false。

而其它的key,比如DELIMITER，ENCODING就必须输入前面的key，所以不知道是自己哪里没理解还是本身这个就有问题，麻烦了解的朋友给说明下。

标签：