这篇文章主要讲解了“怎么使用PostgreSQL中的COPY命令”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么使用PostgreSQL中的COPY命令”吧！

Copy命令在PG 12有所增强,在COPY FROM时可添加WHERE条件过滤.

PG 11
Copy命令

testdb=#\helpcopyCommand:COPYDescription:copydatabetweenafileandatableSyntax:COPYtable_name[(column_name[,...])]FROM{'filename'|PROGRAM'command'|STDIN}[[WITH](option[,...])]COPY{table_name[(column_name[,...])]|(query)}TO{'filename'|PROGRAM'command'|STDOUT}[[WITH](option[,...])]whereoptioncanbeoneof:FORMATformat_nameOIDS[boolean]FREEZE[boolean]DELIMITER'delimiter_character'NULL'null_string'HEADER[boolean]QUOTE'quote_character'ESCAPE'escape_character'FORCE_QUOTE{(column_name[,...])|*}FORCE_NOT_NULL(column_name[,...])FORCE_NULL(column_name[,...])ENCODING'encoding_name'

简单使用

testdb=#droptableifexistst_copy;DROPTABLEtestdb=#CREATETABLEt_copy(idint,c1varchar(20));CREATETABLEtestdb=#insertintot_copySELECTx,'c1-'||xFROMgenerate_series(1,1000)ASx;INSERT01000testdb=#testdb=#COPYt_copyTO'/tmp/data/t_copy.txt'withDELIMITER'|';COPY1000testdb=#droptableifexistst_import;DROPTABLEtestdb=#CREATETABLEt_import(idint,c1varchar(20));CREATETABLEtestdb=#COPYt_importFROM'/tmp/data/t_copy.txt'withDELIMITER'|';COPY1000testdb=#select*fromt_importlimit10;id|c1----+-------1|c1-12|c1-23|c1-34|c1-45|c1-56|c1-67|c1-78|c1-89|c1-910|c1-10(10rows)

不支持WHERE条件过滤

testdb=#COPYt_importFROM'/tmp/data/t_copy.txt'withDELIMITER'|'whereid<5;ERROR:syntaxerroratornear"where"LINE1:...tFROM'/tmp/data/t_copy.txt'withDELIMITER'|'whereid<...

PG 12
COPY命令语法

[local]:5432pg12@testdb=#\helpcopyCommand:COPYDescription:copydatabetweenafileandatableSyntax:COPYtable_name[(column_name[,...])]FROM{'filename'|PROGRAM'command'|STDIN}[[WITH](option[,...])][WHEREcondition]COPY{table_name[(column_name[,...])]|(query)}TO{'filename'|PROGRAM'command'|STDOUT}[[WITH](option[,...])]whereoptioncanbeoneof:FORMATformat_nameFREEZE[boolean]DELIMITER'delimiter_character'NULL'null_string'HEADER[boolean]QUOTE'quote_character'ESCAPE'escape_character'FORCE_QUOTE{(column_name[,...])|*}FORCE_NOT_NULL(column_name[,...])FORCE_NULL(column_name[,...])ENCODING'encoding_name'URL:https://www.postgresql.org/docs/12/sql-copy.html

支持WHERE条件过滤

[local]:5432pg12@testdb=#droptableifexistst_copy;DROPTABLETime:50.327ms[local]:5432pg12@testdb=#CREATETABLEt_copy(idint,c1varchar(20));CREATETABLETime:5.038ms[local]:5432pg12@testdb=#insertintot_copySELECTx,'c1-'||xFROMgenerate_series(1,1000)ASx;INSERT01000Time:16.422ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#COPYt_copyTO'/tmp/data/t_copy.txt'withDELIMITER'|';COPY1000Time:4.795ms[local]:5432pg12@testdb=#droptableifexistst_import;DROPTABLETime:4.798ms[local]:5432pg12@testdb=#CREATETABLEt_import(idint,c1varchar(20));CREATETABLETime:2.462ms[local]:5432pg12@testdb=#COPYt_importFROM'/tmp/data/t_copy.txt'withDELIMITER'|'WHEREid<5;COPY4Time:4.842ms[local]:5432pg12@testdb=#select*fromt_import;id|c1----+------1|c1-12|c1-23|c1-34|c1-4(4rows)Time:6.103ms

感谢各位的阅读，以上就是“怎么使用PostgreSQL中的COPY命令”的内容了，经过本文的学习后，相信大家对怎么使用PostgreSQL中的COPY命令这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！