文本导出导入之PG:copy与Oracle:sqlldr

文本文件是人类友好的信息表示形式，但是由于各个品牌的数据库由于存储引擎的不同，内部数据的存储形式也各有不同，主流数据库都提供了从文本文件载入数据到表（关系）的工具，对于Oracle数据库，原生提供的工具是sqlldr（用于将文本文件的数据导入表中，反向来用于将表导出表文本文件的是sqluldr2（非官方），默认是没有的，需要自行下载安装）。

由于sqlldr和PG的COPY命令的基本用法类似，以下主要演示PG的COPY命令将表（视图）的数据导出到文本文件，以及将文本文件的数据导入表中的过程。

查看COPY命令的用法：

mydb-# \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 [, …] ) ]

COPY { table_name [ ( column_name [, …] ) ] | ( query ) }
TO { ‘filename’ | PROGRAM ‘command’ | STDOUT }
[ [ WITH ] ( option [, …] ) ]

where option can be one of:

FORMAT format_name
OIDS [ boolean ]
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 [, ...] )
ENCODING 'encoding_name'
创建一个测试表test，插入一条数据：

mydb=# create table test(c1 varchar(10),c2 int);
CREATE TABLE
mydb=# \d+ test
Table “public.test”
Column | Type | Modifiers | Storage | Stats target | Description
——–+———————–+———–+———-+————–+————-
c1 | character varying(10) | | extended | |
c2 | integer | | plain | |
Has OIDs: no

mydb=# insert into test values(‘aaabbbcc’,99);
INSERT 0 1

在postgres用户的家目录下新建一个文本文件test.txt，内容如下：

[postgres@pg93 ~]$ cat test.txt
pppppp,20
ggggggg,48

以下命令将test.txt中的数据导入到test表中（由于COPY命令预设的分隔符是制表符，但我们的数据里面是逗号，所以指定DELIMITER ‘,’参数）：

mydb=# copy test from ‘/home/postgres/test.txt’ DELIMITER ‘,’;
COPY 2
mydb=# select * from test ;
c1 | c2
———-+—-
aaabbbcc | 99
pppppp | 20
ggggggg | 48
(3 rows)

接下来，我们将test表内的数据导出到test1.txt中，并且使用csv格式：

mydb=# copy test to ‘/home/postgres/test1.txt’ with csv header;
COPY 3

[postgres@pg93 ~]$ cat test1.txt
c1,c2
aaabbbcc,99
pppppp,20
ggggggg,48

另外，我们可以按需要导出表的一部分数据，而不是全部导出（由于COPY命令不能直接作用于视图，对视图的数据进行导出时，也可以使用该方法来实现）：

mydb=# copy (select * from test where c2>20) to ‘/home/postgres/test2.csv’ with csv header;
COPY 2

[postgres@pg93 ~]$ cat test2.csv
c1,c2
aaabbbcc,99
ggggggg,48

COPY命令也可以使用操作系统的标准输入输出，通过与操作系统的衔接来实现更多的数据处理操作，例如对导出数据进行排序

[postgres@pg93 ~]$ psql mydb -c ‘copy test to STDOUT’|sort
aaabbbcc 99
ggggggg 48
pppppp 20

实际使用中可以发现，PG的COPY命令有比Oracle的sqlldr更好的灵活性，它不仅可以支持文本的导出导入，还支持标准输出输入，另外导出格式也可以设置为二进制（仅只可用于PG之间使用）来加速导出的速度，可以认为在该功能上，PG毫不逊色，甚至更优。