开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 在对账|购票|防纂改|原子操作中的语法妙用

简介:
+关注继续查看

标签

PostgreSQL , update , returning , NEW , OLD


背景

在数据库中更新记录时,有时为了对账,或者防纂改的目的,需要在更新后立即返回更新前和更新后的值。

例如以set bit为例,假设使用BIT串作为火车的每个位置,每个BIT代表一张票,0表示未售卖,1表示已售卖。

购票时,使用set bit=1的操作,为了确保不出现重复售票的问题,必须确保被set的value以前的值为0,SET后的值为1。

这个动作其实也可以在function中来保证,不过你要扩展FUNCTION的功能,在function中确保set bit前的值为0,set bit后的值为1。

本文要讲的是通过update returning语法来实现类似的对照功能。

其实insert returning也有类似的用法,例如插入时并不知道数据库生成了什么UUID,这个UUID可能是流水号,将来程序要用来做二次确认的搜索。(如运营商的二次确认,或者短信密码,也需要用来作为标识)

update returning语法

目前PostgreSQL支持insert,delete,update的returning。

insert returning 返回的是新插入的值。

delete returning 返回的是被删除的值。

update returning 返回的是更新后的值,不能返回更新前的值,但是有方法可以得到。

或者等阿里云RDS PostgreSQL退出update returning old.column的功能吧。

例子

PostgreSQL 支持delete, update返回删除前的值以及更新后的值.

postgres=# create table test (old text, new text, mod_time timestamp);
CREATE TABLE
postgres=# insert into test values ('old', 'new', now());
INSERT 0 1
postgres=# select * from test ;
 old | new |          mod_time          
-----+-----+----------------------------
 old | new | 2013-01-22 15:36:02.543393
(1 row)

postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning *;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)
UPDATE 1

update returning 返回的是更新后的值.

postgres=# select * from test ;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)

postgres=# delete from test returning *;
 old |  new   |          mod_time          
-----+--------+----------------------------
 new | DIGOAL | 2013-01-22 15:36:40.062419
(1 row)
DELETE 1

delete returning 返回的是删除前的值.

returning 后的子句类似select ... from 中的子句, 所以也支持表达式 :

postgres=# insert into test values ('old', 'new', now());
INSERT 0 1
postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning 1,2,3,old,new,mod_time,old||new;
 ?column? | ?column? | ?column? | old |  new   |          mod_time          | ?column?  
----------+----------+----------+-----+--------+----------------------------+-----------
        1 |        2 |        3 | new | DIGOAL | 2013-01-22 15:39:13.238924 | newDIGOAL
(1 row)
UPDATE 1

update returning 如何返回old.column

方法1, update 中, 如果将一个字段的值赋予给另一个字段, 那会将更新前的值赋予给它, 而不是更新后的值.

postgres=# update test set new='DIGOAL', old=new, mod_time=clock_timestamp() returning 1,2,3,old,new,mod_time,old||new;
 ?column? | ?column? | ?column? | old |  new   |          mod_time          | ?column?  
----------+----------+----------+-----+--------+----------------------------+-----------
        1 |        2 |        3 | new | DIGOAL | 2013-01-22 15:39:13.238924 | newDIGOAL
(1 row)
UPDATE 1

new='DIGOAL', old=new

更新后 :   

old = 'new' (new字段更新前的值)
new = 'DIGOAL'

使用这种方法可以返回set_bit前的bit string以及set_bit后的bit string。

方法2, 如果被更新的表有PK,可以使用update from子句, 返回旧值

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be unique
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;
Returns:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

使用这种方法也可以返回set_bit前的bit string以及set_bit后的bit string。

参考

1. http://www.postgresql.org/docs/9.2/static/sql-delete.html

2. http://www.postgresql.org/docs/9.2/static/sql-update.html

3. http://stackoverflow.com/questions/7923237/return-pre-update-column-values-using-sql-only-postgresql-version

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 18 章 服务器设置和操作_18.4. 管理内核资源
18.4. 管理内核资源 18.4.1. 共享内存和信号量 18.4.2. systemd RemoveIPC 18.4.3. 资源限制 18.4.4. Linux 内存过量使用 18.4.5. Linux 大页面 PostgreSQL某些时候会耗尽操作系统的各种资源限制,当同一个系统上运行着多个拷贝的服务器或在一个非常大的安装中时尤其如此。
1389 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 18 章 服务器设置和操作_18.2. 创建一个数据库集簇
18.2. 创建一个数据库集簇 18.2.1. 二级文件系统的使用 18.2.2. 网络文件系统的使用 在你能做任何事情之前,你必须在磁盘上初始化一个数据库存储区域。我们称之为一个数据库集簇(SQL标准使用的术语是目录集簇)。
1197 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 18 章 服务器设置和操作
第 18 章 服务器设置和操作 目录 18.1. PostgreSQL用户账户 18.2. 创建一个数据库集簇 18.2.1. 二级文件系统的使用 18.2.2. 网络文件系统的使用 18.3.
1128 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 18 章 服务器设置和操作_18.7. 阻止服务器欺骗
18.7. 阻止服务器欺骗 服务器在运行时,它不可能让恶意用户取代正常的数据库服务器。然而,当服务器关闭时, 一个本地用户可以通过启动它们自己的服务器来欺骗正常的服务器。行骗的服务器可以读取客户端发送的密码和查询语句, 但是不会返回任何数据,因为PGDATA这个目录是安全的(它有目录权限)。
1012 0
厉害了!阿里安全图灵实验室在ICDAR2017 MLT竞赛刷新世界最好成绩
近日,阿里安全图灵实验室(Alibaba Turing Lab)的ATL Cangjie OCR算法在ICDAR2017的MLT(Competition on Multi-lingual scene text detection)自然场景多语言文本检测竞赛中刷新了世界最好成绩,以73.52%的Hmean排名第一。
5803 0
去除Visual Paradigm输出图片中的浮水印
BoUML都收费了,argoUML半年没更新了,而startUML六年多没更新了,免费的UML工具里就数VP的社区版还不错了。唯一的缺憾就是输出图片有浮水印,虽说咱们也四处宣传VP,但输出的图片实在不方便。
2831 0
温故而知新:查看端口占用情况以及DOS中的管道操作/重定向操作
自从进入windows时代以后,估计这玩意儿很多人都快忘光了吧,今天小小复习一下   先来看DOS命令中的重定向/管道操作符号   ">" :将命令输出结果写入到文件或设备(比如打印机),而不是直接输出在屏幕上 示例: c:\> dir /w > dir_tree.
732 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载