Feng’ Blog

PostGreSQL批量更新、删除或插入数据
批量操作可以减少数据库与应用程序的交互次数,提高数据处理的吞吐量。本文将通过示例介绍如何批量插入、更新和删除数据。...
扫描右侧二维码阅读全文
10
2018/08

PostGreSQL批量更新、删除或插入数据

pgsql221.png

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

批量插入数据

使用 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)
使用 values(),(),...(); 的方法
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());
INSERT 0 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
使用 copy 协议。copy 协议与 insert 协议不一样,更加精简,插入效率高。
test03=# d test

            Table "public.test"
ColumnTypeModifiers
idintegernot null
infotext
crt_timetimestamp 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

批量更新数据

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;

idinfocrt_time
3hello2017-04-24 15:31:49.14291
4digoal01232017-04-24 15:42:50.912887
5hello digoal2017-04-24 15:57:29.622045
1new12017-04-24 15:58:55.610072
2new22017-04-24 15:28:20.37392
6new62017-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;

idinfocrt_time
1new12017-04-24 15:58:55.610072
2new22017-04-24 15:28:20.37392
6new62017-04-24 15:59:12.265915

如果要清除全表,建议您使用 truncate。

test03=# set lock_timeout = '1s';
SET
test03=# truncate test;
TRUNCATE TABLE
test03=# select * from test;

idinfocrt_time

(0 rows)
文章摘转自: https://helpcdn.aliyun.com/document_detail/52952.html

Last modification:January 9th, 2019 at 01:12 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment