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

Stella981
• 阅读 1076

标签

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)
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
待兔 待兔
3年前
PostgreSQL语法、连接
1.语法1.插入数据(INSERT语句)在PostgreSQL中,INSERT查询用于在表中插入新行。可以一次插入单行或多行到表中。语法:INSERTINTOTABLE_NAME(column1,column2,column3,...columnN)VALUES(value1,value2,v
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Mybatisd对MySQL批量插入、批量更新及批量删除语句
1、批量插入<insertid"insertBatch"parameterType"java.util.List"insertintot_student(name,age,class)values<forea
Stella981 Stella981
3年前
PHP如何避免高并发下insert into 重复入库
场景:用户签到/分享功能,每天只能签到一次或分享一次数据库:id  user\_id  add\_time  逻辑分析:用户每天进行分享或签到,得到积分,数据库通过以上字段进行记录,同一时间不可插入多条,一天只能有一条记录,插入前判断是否当天已插入过问题点:用户连点、并发请求等会导致同时插入多条记录,导致积分异常解决方案:使用文件锁,经过
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这