PostgreSQL 如何实现批量更新、删除、插入

背景

如何一次插入多条记录？

如何一次更新多条记录？

如何一次批量删除多条记录？

批量操作可以减少数据库与应用程序的交互次数，提高数据处理的吞吐量。

批量插入

批量插入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)

digoal’s 大量PostgreSQL文章入口