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所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)