最佳实践-PostgreSQL-批量更新、删除或插入数据

批量操作可以减少数据库与应用程序的交互次数，提高数据处理的吞吐量。本文将通过示例介绍如何批量插入、更新和删除数据。

批量插入数据


您可以通过如下四种方法进行批量插入数据。

• 
  使用 insert into ... select 的方法。
  1.   postgres =# insert into tbl1 (id , info ,crt_time ) select generate_series ( 1 , 10000 ), 'test' ,now ();    
  2.   INSERT 0 10000    
  3.   postgres=# select count(*) from tbl1;    
  4.    count    
  5.   -------    
  6.    10001    
  7.   (1 row)
• 
  使用 values(),(),...(); 的方法。
  1. postgres =# insert into tbl1 (id ,info ,crt_time ) values ( 1 , 'test' ,now ()), ( 2 , 'test2' ,now ()), ( 3 , 'test3' ,now ());    
  2. INSERT 0 3
• 
  使用 BEGIN; ...多条insert...; END; 的方法。严格来说，这不属于批量，但可以减少事务提交时的同步等待，同样可以提升性能。
  1. postgres =# begin ;     
  2. BEGIN    
  3. postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());    
  4. INSERT 0 1    
  5. postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());    
  6. INSERT 0 1    
  7. postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());    
  8. INSERT 0 1    
  9. postgres=# end;    
  10. COMMIT
• 
  使用 copy 协议。copy 协议与 insert 协议不一样，更加精简，插入效率高。
  1.   test03 =# \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 |  
  8.   Indexes:  
  9.       "test_pkey" PRIMARY KEY, btree (id)  
  11.   test03=# copy test from stdin;  
  12.   Enter data to be copied followed by a newline.  
  13.   End with a backslash and a period on a line by itself.  
  14.   >> 8    'test'  '2017-01-01'  
  15.   >> 9    'test9' '2017-02-02'  
  16.   >> \.  
  17.   COPY 2

  
  说明：不同的语言驱动，对应的 COPY 接口不同，请参见如下文档。

• 
  PostgreSQL JDBC Driver - JDBC 4.2 9.4.1209 API
• 
  PostgreSQL 9.6.2 Documentation — Functions Associated with the COPY Command

1. test03=# update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;  
2. UPDATE 3  
3. test03=# 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)

1. test03=# delete from test using (values (3),(4),(5)) as tmp(id) where test.id=tmp.id;  
2. DELETE 3  
3. test03=# 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

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