作者
digoal
日期
2019-03-31
标签
PostgreSQL , copy , from , filter , where
背景
PostgreSQL 12 copy from 数据COPY语法增强，支持在COPY过程中过滤记录。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=31f3817402da736b29014ace470cf70aeb126ac5
```
Allow COPY FROM to filter data using WHERE conditions
Extends the COPY FROM command with a WHERE condition, which allows doing
various types of filtering while importing the data (random sampling,
condition on a data column, etc.). Until now such filtering required
either preprocessing of the input data, or importing all data and then
filtering in the database. COPY FROM ... WHERE is an easy-to-use and
low-overhead alternative for most simple cases.
Author: Surafel Temesgen
Reviewed-by: Tomas Vondra, Masahiko Sawada, Lim Myungkyu
Discussion: https://www.postgresql.org/message-id/flat/CALAY4q_DdpWDuB5-Zyi-oTtO2uSk8pmy+dupiRe3AvAc++1imA@mail.gmail.com
```
语法
```
postgres=# \h copy
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
where option can be one of:
FORMAT format_name
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'
URL: https://www.postgresql.org/docs/devel/sql-copy.html
```
例子
postgres=# create table t_to (id int , info text, crt_Time timestamp);
CREATE TABLE
postgres=# insert into t_to select generate_series(1,100000), md5(random()::Text), clock_timestamp();
INSERT 0 100000
postgres=# copy t_to to '/tmp/t_to';
COPY 100000
postgres=# create table t_from (like t_to);
CREATE TABLE
postgres=# copy t_from from '/tmp/t_to' where id<100;
COPY 99
参考
https://www.postgresql.org/docs/devel/sql-copy.html
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=31f3817402da736b29014ace470cf70aeb126ac5
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
