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

xiaoxiao2025-11-17 3

背景

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

例如以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 返回的是更新后的值.

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

相关资源：python入门教程(PDF版)

最新回复(0)

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

标签

背景

update returning语法

update returning 如何返回old.column

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

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

参考