PostgreSQL COPY 导入/导出数据

 

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

9 Comments

  1. 博客不错
    围观一下,欢迎回访~

  2. 这个是什么现象

  3. 你的博客写的不错,向你学习~~~,介意我转到我的博客上吗?

  4. 你的博客写的不错,向你学习~~~,介意我转到我的博客上吗?
    +1

  5. 博客文采写的不错!我还喜欢你其他文章

  6. 呵呵,新的一周开始了,祝博主,新的一周里,开心快乐~

  7. 博主的文章不错 感谢回访
    我会持续关注中

  8. 有意转载博主这篇文章,请问是否允许,盼回复 谢谢

评论