作者
digoal
日期
2017-04-24
标签
PostgreSQL , 批量 , batch , insert , update , delete , copy
背景
如何一次插入多条记录？
如何一次更新多条记录？
如何一次批量删除多条记录？
批量操作可以减少数据库与应用程序的交互次数，提高数据处理的吞吐量。
批量插入
批量插入1
使用insert into ... select的方法
```
postgres=# insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();
INSERT 0 10000
postgres=# select count(*) from tbl1;
count
10001
(1 row)
```
批量插入2
使用values(),(),...();的方法
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());
INSERT 0 3
批量插入3
BEGIN; ...多条insert...; END;
严格来说，这应该不属于批量，但是可以减少事务提交时的同步等待。同样有性能提升的效果。
postgres=# begin;
BEGIN
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());
INSERT 0 1
postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());
INSERT 0 1
postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());
INSERT 0 1
postgres=# end;
COMMIT
批量插入4
copy
copy协议与insert协议不一样，更加精简，插入效率高。
```
test03=# \d test
Table "public.test"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | integer | not null
info | text |
crt_time | timestamp without time zone |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
test03=# copy test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
8 'test' '2017-01-01'
9 'test9' '2017-02-02'
.
COPY 2
```
不同的语言驱动，对应的COPY接口不一样。
参考
https://jdbc.postgresql.org/documentation/publicapi/index.html
https://www.postgresql.org/docs/9.6/static/libpq-copy.html
批量更新
批量更新
test03=# update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;
UPDATE 3
test03=# select * from test;
id | info | crt_time
----+--------------+----------------------------
3 | hello | 2017-04-24 15:31:49.14291
4 | digoal0123 | 2017-04-24 15:42:50.912887
5 | hello digoal | 2017-04-24 15:57:29.622045
1 | new1 | 2017-04-24 15:58:55.610072
2 | new2 | 2017-04-24 15:28:20.37392
6 | new6 | 2017-04-24 15:59:12.265915
(6 rows)
from后面用其他表名代替可以实现多表JOIN批量更新。
批量删除
批量删除
test03=# delete from test using (values (3),(4),(5)) as tmp(id) where test.id=tmp.id;
DELETE 3
test03=# select * from test;
id | info | crt_time
----+---------+----------------------------
1 | new1 | 2017-04-24 15:58:55.610072
2 | new2 | 2017-04-24 15:28:20.37392
6 | new6 | 2017-04-24 15:59:12.265915
using后面用其他表名代替可以实现多表JOIN批量删除。
如果要清除全表，建议使用truncate
test03=# set lock_timeout = '1s';
SET
test03=# truncate test;
TRUNCATE TABLE
test03=# select * from test;
id | info | crt_time
----+------+----------
(0 rows)
批量更新、删除注意-当JOIN出现笛卡尔或一对多或多对多时, 情况可能和想象的不一样, 因为数据库并不知道你需要将VALUE更新到哪一行匹配的目标行的VALUE
```
postgres=# create table t1 (id int primary key, info text); CREATE TABLE postgres=# create table t2(id int, info text); CREATE TABLE postgres=# insert into t1 select generate_series(1,10), 't1'; INSERT 0 10 postgres=# insert into t2 values (1,'t2'); INSERT 32796 1 postgres=# insert into t2 values (1,'t2'); INSERT 32797 1 postgres=# insert into t2 values (1,'t3'); INSERT 32798 1 postgres=# insert into t2 values (1,'t4'); INSERT 32799 1 postgres=# update t1 set info=t2.info from t2 where t1.id=t2.id; UPDATE 1 postgres=# select * from t1 where id=1; id | info ----+------ 1 | t2 (1 row)
postgres=# delete from t1 using t2 where t1.id=t2.id; DELETE 1
postgres=# drop table t1; DROP TABLE postgres=# drop table t2; DROP TABLE postgres=# create table t1 (id int, info text); CREATE TABLE postgres=# create table t2 (id int, info text); CREATE TABLE postgres=# insert into t1 values (1,'t1'); INSERT 32814 1 postgres=# insert into t1 values (1,'t1'); INSERT 32815 1 postgres=# insert into t1 values (1,'t1'); INSERT 32816 1 postgres=# insert into t2 values (1,'t2'); INSERT 32817 1 postgres=# insert into t2 values (1,'t3'); INSERT 32818 1 postgres=# insert into t2 values (1,'t4'); INSERT 32819 1 postgres=# insert into t1 values (2,'t1'); INSERT 32820 1 postgres=# select * from t1; id | info ----+------ 1 | t1 1 | t1 1 | t1 2 | t1 (4 rows)
postgres=# select * from t2; id | info ----+------ 1 | t2 1 | t3 1 | t4 (3 rows)
postgres=# update t1 set info=t2.info from t2 where t1.id=t2.id; UPDATE 3 postgres=# select ctid,* from t1; ctid | id | info -------+----+------ (0,4) | 2 | t1 (0,5) | 1 | t2 (0,6) | 1 | t2 (0,7) | 1 | t2 (4 rows)
postgres=# delete from t1 using t2 where t1.id=t2.id; DELETE 3 postgres=# select ctid,* from t1; ctid | id | info -------+----+------ (0,4) | 2 | t1 (1 row) ```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
