postgres与csv

发表于 更新于 阅读次数： 阅读次数：

postgres的copy to

PostgreSQL 的 COPY TO 直接可以干这个事情，而且导出速度是非常快的。下面例子是把 products 表导出成 CSV ：

1
2
3
COPY products
TO '/path/to/output.csv'
WITH csv;

可以导出指定的属性：

1
2
3
COPY products (name, price)
TO '/path/to/output.csv'
WITH csv;

也可以配合查询语句，比如最常见的 SELECT ：

1
2
3
4
5
6
7
COPY (
  SELECT name, category_name
  FROM products
  LEFT JOIN categories ON categories.id = products.category_id
)
TO '/path/to/output.csv'
WITH csv;

导入 CSV
跟上面的导出差不多，只是把 TO 换成 FROM ，举例：

1
2
3
COPY products
FROM '/path/to/input.csv'
WITH csv;

这个命令做导入是非常高效的，在开头那篇博客作者的测试中，COPY 只花了 INSERT 方案 1/3 的时间，而后者还用 prepare statement 优化过。

示例

示例1.将整张表拷贝至标准输出
1
2
3
test=# copy tbl_test1 to stdout;
1    HA    12
2    ha    543
示例2.将表的部分字段拷贝至标准输出，并输出字段名称，字段间使用’,’分隔
1
2
3
4
test=# copy tbl_test1(a,b) to stdout delimiter ',' csv header;
a,b
1,HA
2,ha
示例3.将查询结果拷贝至标准输出
1
2
3
test=# copy (select a,b from tbl_test1 except select e,f from tbl_test2 ) to stdout delimiter ',' quote '"' csv header;
a,b
2,ha

将标准输入拷贝至表中需要注意几点

1.字段间分隔符默认使用【Tab】键
2.换行使用回车键
3.结束使用反斜线+英文据点(.)
4.最好指定字段顺序，要不然可能会错位赋值

示例4.将标准输入拷贝至表中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
test=# copy tbl_test1(a,b,c) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    公举    公主
>> 2    万岁    万万岁
>> \.
COPY 2
test=# select * from tbl_test1 ;
 a |  b   |   c    
---+------+--------
 1 | HA   | 12
 2 | ha   | 543
 1 | 公举 | 公主
 2 | 万岁 | 万万岁
(4 rows)
示例5.从标准输入拷贝至表中，并将标准输入第一行作为字段名(和表中不符也没关系，copy会自动忽略第一行)，字段分隔符为’,’
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
test=# copy tbl_test1(a,b,c) from stdin delimiter ',' csv header;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a,b,c
>> 3,你好,hello
>> 4,超人,super
>> \.
COPY 2
test=# select * from tbl_test1 ;
 a |  b   |   c    
---+------+--------
 1 | HA   | 12
 2 | ha   | 543
 1 | 公举 | 公主
 2 | 万岁 | 万万岁
 3 | 你好 | hello
 4 | 超人 | super
(6 rows)

以上是表与标准输出和标准输入间的相互拷贝，表与文件的拷贝和以上完全相同，只是将标准输出和标准输入换成文件。需要注意的是：

1.数据库用户必须有文件所在的路径的写权限。
2.如果表存在中文字符，导出至csv文件时需要设置编码为GBK，否则使用excel打开是中文显示乱码。
3.将文件导入表中时仍要考虑编码问题

示例6.将表拷贝至csv文件中
1
2
test=# copy tbl_test1 to '/tmp/tbl_test1.csv' delimiter ',' csv header;
COPY 6

使用excel打开文件，中文显示为乱码

示例7. 将表以GBK编码拷贝至csv文件中
1
2
test=# copy tbl_test1 to '/tmp/tbl_test1.csv' delimiter ',' csv header encoding 'GBK';
COPY 6

使用excel打开，中文显示正常

示例8.将刚才导出的文件再次拷贝至表中，使用默认编码UTF8
1
2
3
test=# copy tbl_test1(a,b,c) from '/tmp/tbl_test1.csv' delimiter ',' csv header;
ERROR:  invalid byte sequence for encoding "UTF8": 0xb9
CONTEXT:  COPY tbl_test1, line 4

示例9.将刚才导出的文件再次拷贝至表中，使用GBK编码

1
2
test=# copy tbl_test1(a,b,c) from '/tmp/tbl_test1.csv' delimiter ',' csv header encoding 'GBK';
COPY 6