COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。

导出表数据到文件或 STDOUT ：

COPY tablename [(column [, ...])] TO STDOUT [[WITH] [BINARY] [OIDS] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [CSV [HEADER] [QUOTE [AS] 'quote'] [ESCAPE [AS] 'escape'] [FORCE NOT NULL column [, ...]]

导入文件或者 STDIN 到表中：

COPY tablename [(column [, ...])] FROM STDIN [[WITH] [BINARY] [OIDS] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [CSV [HEADER] [QUOTE [AS] 'quote'] [ESCAPE [AS] 'escape'] [FORCE QUOTE column [, ...]]

导出表 employee 到默认输出 STDOUT：

psql> COPY employee TO STDOUT; 1 JG100011 Jason Gilmore jason@example.com 2 RT435234 Robert Treat rob@example.com 3 GS998909 Greg Sabino Mullane greg@example.com 4 MW777983 Matt Wade matt@example.com

导出表 employee 到 sql 文件：

psql> COPY employee TO '/home/smallfish/employee.sql';

从文件导入数据：

psql> COPY employeenew FROM '/home/smallfish/employee.sql'; psql> SELECT * FROM employeenew; employeeid | employeecode | name | email ------------+--------------+---------------------+--------------- 1 | JG100011 | Jason Gilmore | jason@example.com 2 | RT435234 | Robert Treat | rob@example.com 3 | GS998909 | Greg Sabino Mullane | greg@example.com 4 | MW777983 | Matt Wade | matt@example.com (4 rows)

输出对象ID（OIDS）：

psql> COPY employee TO STDOUT OIDS; 24627 1 GM100011 Jason Gilmore jason@example.com 24628 2 RT435234 Robert Treat rob@example.com 24629 3 GS998909 Greg Sabino Mullane greg@example.com 24630 4 MW777983 Matt Wade matt@example.com

指定导出间隔符，默认是 \t ，这里为 | ：

psql>COPY employee TO STDOUT DELIMITER '|'; 1|GM100011|Jason Gilmore|jason@example.com 2|RT435234|Robert Treat|rob@example.com 3|GS998909|Greg Sabino Mullane|greg@example.com 4|MW777983|Matt Wade|matt@example.com

导入文件数据，指定间隔符为 | ：

psql> COPY employeenew FROM '/home/smallfish/employee.sql' DELIMITER |;

导出指定字段的数据：

psql> COPY employee (name,email) TO STDOUT; Jason Gilmore jason@example.com Robert Treat rob@example.com Greg Sabino Mullane greg@example.com Matt Wade matt@example.com

为 NULL 字段设置默认值：

psql> COPY employee TO STDOUT NULL 'no email'; Jason Gilmore no email Robert Treat rob@example.com Greg Sabino Mullane greg@example.com Matt Wade no email

导出为CVS格式：

psql> COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;

psql> COPY (select * frin employee where name = 'abc') TO '/home/smallfish/employee.csv' CSV HEADER;

参考资料：Apress Beginning PHP and PostgreSQL 8: From Novice to Professional