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)  
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
9月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL 删除数据库
PostgreSQL 删除数据库
79 0
|
10月前
|
SQL 存储 关系型数据库
RDS For SQL Server删除数据库报错
RDS For SQL Server删除数据库报错
|
11月前
|
SQL 关系型数据库 数据库
PostgreSQL 删除数据库
PostgreSQL 删除数据库
165 0
|
存储 资源调度 Kubernetes
云原生 PostgreSQL - CrunchyData PGO 教程:创建、连接、删除 Postgres 集群
云原生 PostgreSQL - CrunchyData PGO 教程:创建、连接、删除 Postgres 集群
236 0
|
关系型数据库 PostgreSQL
PostgreSQL 删除表格
PostgreSQL 删除表格
132 0
|
关系型数据库 Java 数据库连接
【RDS PostgreSQL】批量更新、删除或插入数据
批量操作可以减少数据库与应用程序的交互次数,提高数据处理的吞吐量。本文将通过示例介绍如何批量插入、更新和删除数据。
524 0
【RDS PostgreSQL】批量更新、删除或插入数据
|
关系型数据库 数据库 RDS
PolarDB-X 1.0-常见问题-分库分表问题-删除数据库时,PolarDB-X各物理分库是否会被自动删除?是否会影响RDS上的数据库?
在控制台上删除PolarDB-X的数据库时,只会删除之前由PolarDB-X所创建的数据库,不会影响原本在RDS上创建的数据库。
161 0
|
SQL Java 数据库连接
SQL 中删除超出时间限制的数据,并返回删除数据信息(Mybatis+postgresql)
前言 前一阵子,接到一个活,主要内容是这样的,数据库中存在一些过期的日志(可能是一天前的数据,或者是一个月前的数据,等等),将这些数据删除掉,并且返回这些数据的信息(就是说我得知道自己到底删了哪些数据呀?)。
1858 0
|
关系型数据库 数据库 C语言
Greenplum 6已合并到PostgreSQL 9.3版本 - 比上一代GP提升:8倍读,195倍更新、删除 - 另有大量PG新特性
标签 PostgreSQL , Greenplum , 6 , gin , 异步事务 背景 Greenplum 6已合并到PostgreSQL 9.3版本 - 相比5性能提升:读8倍,更新、删除195倍 - 另有大量PG新特性,详见PostgreSQL release notes https://www.postgresql.org/docs/11/static/release.html 例如 1、GIN、SPGIST 索引接口。
7201 0
|
关系型数据库 数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 21 章 数据库角色_21.4. 删除角色
21.4. 删除角色 由于角色可以拥有数据库对象并且能持有访问其他对象的特权,删除一个角色 常常并非一次DROP ROLE就能解决。任何被该用户所拥有 的对象必须首先被删除或者转移给其他拥有者,并且任何已被授予给该角色的 权限必须被收回。
1224 0
相关产品
云数据库 Redis 版
云数据库 MongoDB 版
云数据库 RDS
推荐文章
更多