839
PostgreSQL 如何實現批量更新、刪除、插入
標簽
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)
批量刪除
批量刪除
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
如果要清除全表，建議使用truncate
test03=# set lock_timeout = '1s';
SET
test03=# truncate test;
TRUNCATE TABLE
test03=# select * from test;
id | info | crt_time
----+------+----------
(0 rows) 最後更新：2017-04-24 21:32:59