PostgreSQL COPY 导入/导出数据
COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。
导出表数据到文件或 STDOUT ：
COPY tablename [(column [, ...])]
TO {'filename' | STDOUT}
[
'delimiter']
'null string']
'quote']
'escape']
]
导入文件或者 STDIN 到表中：
COPY tablename [(column [, ...])]
FROM {'filename' | STDIN}
[
'delimiter']
'null string']
'quote']
'escape']
]
导出表 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;
246271 GM100011 Jason Gilmore jason@example.com
246282 RT435234 Robert Treat rob@example.com
246293 GS998909 Greg Sabino Mullane greg@example.com
246304 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;
参考资料：Apress Beginning PHP and PostgreSQL 8: From Novice to Professional
页:
[1]