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

背景

如何一次插入多条记录？

如何一次更新多条记录？

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

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

批量插入

批量插入1

使用insert into ... select的方法

1postgres=# insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();    
2INSERT 0 10000    
3postgres=# select count(*) from tbl1;    
4 count     
5-------    
6 10001    
7(1 row)

批量插入2

使用values(),(),...();的方法

1postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());    
2INSERT 0 3

批量插入3

BEGIN; ...多条insert...; END;

严格来说，这应该不属于批量，但是可以减少事务提交时的同步等待。同样有性能提升的效果。

1postgres=# begin;    
2BEGIN    
3postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());    
4INSERT 0 1    
5postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());    
6INSERT 0 1    
7postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());    
8INSERT 0 1    
9postgres=# end;    
10COMMIT

批量插入4

copy

copy协议与insert协议不一样，更加精简，插入效率高。

1test03=# \d test  
2                Table "public.test"  
3  Column  |            Type             | Modifiers   
4----------+-----------------------------+-----------  
5 id       | integer                     | not null  
6 info     | text                        |   
7 crt_time | timestamp without time zone |   
8Indexes:  
9    "test_pkey" PRIMARY KEY, btree (id)  
10  
11test03=# copy test from stdin;  
12Enter data to be copied followed by a newline.  
13End with a backslash and a period on a line by itself.  
14>> 8    'test'  '2017-01-01'  
15>> 9    'test9' '2017-02-02'  
16>> \.  
17COPY 2

不同的语言驱动，对应的COPY接口不一样。

参考

https://jdbc.postgresql.org/documentation/publicapi/index.html

https://www.postgresql.org/docs/9.6/static/libpq-copy.html

批量更新

1test03=# update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;  
2UPDATE 3  
3test03=# select * from test;  
4 id |     info     |          crt_time            
5----+--------------+----------------------------  
6  3 | hello        | 2017-04-24 15:31:49.14291  
7  4 | digoal0123   | 2017-04-24 15:42:50.912887  
8  5 | hello digoal | 2017-04-24 15:57:29.622045  
9  1 | new1         | 2017-04-24 15:58:55.610072  
10  2 | new2         | 2017-04-24 15:28:20.37392  
11  6 | new6         | 2017-04-24 15:59:12.265915  
12(6 rows)

批量删除

1test03=# delete from test using (values (3),(4),(5)) as tmp(id) where test.id=tmp.id;  
2DELETE 3  
3test03=# select * from test;  
4 id |  info   |          crt_time            
5----+---------+----------------------------  
6  1 | new1    | 2017-04-24 15:58:55.610072  
7  2 | new2    | 2017-04-24 15:28:20.37392  
8  6 | new6    | 2017-04-24 15:59:12.265915

如果要清除全表，建议使用truncate

1test03=# set lock_timeout = '1s';
2SET
3test03=# truncate test;  
4TRUNCATE TABLE  
5test03=# select * from test;  
6 id | info | crt_time   
7----+------+----------  
8(0 rows)