PostgreSQL COPY 导入/导出数据

来源：互联网发布：日本人长相知乎编辑：程序博客网时间：2024/05/16 01:03

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         jason@example.com2       RT435234        Robert Treat          rob@example.com3       GS998909        Greg Sabino Mullane   greg@example.com4       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.com24628  2       RT435234        Robert Treat          rob@example.com24629  3       GS998909        Greg Sabino Mullane   greg@example.com24630  4       MW777983        Matt Wade             matt@example.com

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

psql>COPY employee TO STDOUT DELIMITER '|';1|GM100011|Jason Gilmore|jason@example.com2|RT435234|Robert Treat|rob@example.com3|GS998909|Greg Sabino Mullane|greg@example.com4|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.comRobert Treat          rob@example.comGreg Sabino Mullane   greg@example.comMatt Wade             matt@example.com

为 NULL 字段设置默认值：

psql> COPY employee TO STDOUT NULL 'no email';Jason Gilmore         no emailRobert Treat          rob@example.comGreg Sabino Mullane   greg@example.comMatt Wade             no email

导出为CVS格式：

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

0 0