COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。
导出表数据到文件或 STDOUT ：
COPY tablename [(column [, ...])]
TO {'filename' | 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 {'filename' | 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 [email protected] 2 RT435234 Robert Treat [email protected] 3 GS998909 Greg Sabino Mullane [email protected] 4 MW777983 Matt Wade [email protected]
导出表 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 | [email protected]
2 | RT435234 | Robert Treat | [email protected]
3 | GS998909 | Greg Sabino Mullane | [email protected]
4 | MW777983 | Matt Wade | [email protected]
(4 rows)
输出对象ID（OIDS）：
psql> COPY employee TO STDOUT OIDS; 24627 1 GM100011 Jason Gilmore [email protected] 24628 2 RT435234 Robert Treat [email protected] 24629 3 GS998909 Greg Sabino Mullane [email protected] 24630 4 MW777983 Matt Wade [email protected]
指定导出间隔符，默认是 \t ，这里为 | ：
psql>COPY employee TO STDOUT DELIMITER '|'; 1|GM100011|Jason Gilmore|[email protected] 2|RT435234|Robert Treat|[email protected] 3|GS998909|Greg Sabino Mullane|[email protected] 4|MW777983|Matt Wade|[email protected]
导入文件数据，指定间隔符为 | ：
psql> COPY employeenew FROM '/home/smallfish/employee.sql' DELIMITER |;
导出指定字段的数据：
psql> COPY employee (name,email) TO STDOUT; Jason Gilmore [email protected] Robert Treat [email protected] Greg Sabino Mullane [email protected] Matt Wade [email protected]
为 NULL 字段设置默认值：
psql> COPY employee TO STDOUT NULL 'no email'; Jason Gilmore no email Robert Treat [email protected] Greg Sabino Mullane [email protected] Matt Wade no email
导出为CVS格式：
psql> COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;
参考资料：Apress Beginning PHP and PostgreSQL 8: From Novice to Professional
If you enjoyed this article, please consider sharing it!
博客不错
围观一下，欢迎回访~
这个是什么现象
你的博客写的不错，向你学习~~~，介意我转到我的博客上吗？
有机会多交流
你的博客写的不错，向你学习~~~，介意我转到我的博客上吗？
+1
博客文采写的不错！我还喜欢你其他文章
呵呵，新的一周开始了，祝博主，新的一周里，开心快乐~
博主的文章不错 感谢回访
我会持续关注中
有意转载博主这篇文章，请问是否允许，盼回复 谢谢