Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use the COPY command in PostgreSQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use the COPY command in PostgreSQL".

The Copy command has been enhanced in PG 12 to add WHERE conditional filtering when COPY FROM.

PG 11

Copy command

Testdb=#\ help copyCommand: COPYDescription: copy data between a file and a tableSyntax:COPY table_name [(column_name [,...])] FROM {'filename' | PROGRAM' command' | STDIN} [[WITH] (option [,...])] COPY {table_name [(column_name [ ])] | (query)} TO {'filename' | PROGRAM' command' | STDOUT} [[WITH] (option [,...])] where option can be one of: FORMAT format_name OIDS [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'

Easy to use

Testdb=# drop table if exists tweak copyleft drop TABLEtestdb=# CREATE TABLEt _ copy (id int,c1 varchar (20)); CREATE TABLEtestdb=# insert into t_copy SELECT x-stamped C1 TABLEtestdb=# CREATE TABLEt'| | x FROM generate_series (1, 1000) AS x-match insert 0 1000testdb=# testdb=# COPY t_copy TO'/ tmp/data/t_copy.txt' with DELIMITER'|'; COPY 1000testdb=# drop table if exists tasking importance drop drop _ import (id int,c1 varchar (20)) CREATE TABLEtestdb=# COPY t_import FROM'/ tmp/data/t_copy.txt' with DELIMITER'|'; COPY 1000testdb=# select * from t_import limit 10; id | C1-+-1 | C1-12 | C1-2 3 | C1-3 4 | C1-4 5 | C1-5 6 | C1-6 7 | C1-7 8 | C1-8 9 | C1-9 10 | C1-10 (10 rows)

WHERE conditional filtering is not supported

Testdb=# COPY t_import FROM'/ tmp/data/t_copy.txt' with DELIMITER'| 'where id < 5 * error: syntax error at or near "where" LINE 1:... t FROM' / tmp/data/t_copy.txt' with DELIMITER'| 'where id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.