PostgreSQL copy

概述COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。 导出表数据到文件或 STDOUT ： COPY tablename [(column [, ...])] TO {'filename' | STDOUT} [[WITH] [BINARY] [OIDS] [DELIMIT

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]       RT435234        Robert Treat          [email protected]       GS998909        Greg Sabino Mullane   [email protected]       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]  2       RT435234        Robert Treat          [email protected]  3       GS998909        Greg Sabino Mullane   [email protected]  4       MW777983        Matt Wade             [email protected]

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

psql>copY employee TO STDOUT DEliMITER '|';1|GM100011|Jason Gilmore|[email protected]|RT435234|Robert Treat|[email protected]|GS998909|Greg Sabino Mullane|[email protected]|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] Treat          [email protected] Sabino Mullane   [email protected] Wade             [email protected]

为 NulL 字段设置默认值：

psql> copY employee TO STDOUT NulL 'no email';Jason Gilmore         no emailRobert Treat          [email protected] Sabino Mullane   [email protected] Wade             no email

导出为CVS格式：

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

总结

以上是内存溢出为你收集整理的PostgreSQL copy全部内容，希望文章能够帮你解决PostgreSQL copy所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。