PostgreSQL update returning NEW|OLD在对账|购票|防纂改|原子操作中的妙用

作者: digoal

日期: 2016-11-25

标签: 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。

参考

• http://www.postgresql.org/docs/9.2/static/sql-delete.html
• http://www.postgresql.org/docs/9.2/static/sql-update.html
• http://stackoverflow.com/questions/7923237/return-pre-update-column-values-using-sql-only-postgresql-version