这篇文章主要讲解了“PostgreSQL数据库中如何从update返回old”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL数据库中如何从update返回old”吧！

UPDATEtblxSETtbl_id=23,name='NewGuy'FROMtbly--usingtheFROMclauseWHEREx.tbl_id=y.tbl_id--mustbeUNIQUENOTNULLANDx.tbl_id=3RETURNINGy.tbl_idASold_id,y.nameASold_name,x.tbl_id,x.name;

old_id|old_name|tbl_id|name--------+----------+--------+---------3|OldGuy|23|NewGuy

WITHselAS(

SELECTtbl_id,nameFROMtblWHEREtbl_id=3--assuminguniquetbl_id),updAS(UPDATEtblSETname='NewGuy'WHEREtbl_id=3RETURNINGtbl_id,name)SELECTs.tbl_idASold_id,s.nameAsold_name,u.tbl_id,u.nameFROMsels,updu;


UPDATEtblxSETtbl_id=24,name='NewGal'FROM(SELECTtbl_id,nameFROMtblWHEREtbl_id=4FORUPDATE)yWHEREx.tbl_id=y.tbl_idRETURNINGy.tbl_idASold_id,y.nameASold_name,x.tbl_id,x.name;对于insert怎么办呢?

WITHselAS(SELECTid,titleFROMpostsWHEREidIN(1,2)--selectrowstocopy),insAS(INSERTINTOposts(title)SELECTtitleFROMselRETURNINGid,title)SELECTins.id,sel.idASfrom_idFROMinsJOINselUSING(title);Iftitleisnotuniqueperquery(butatleastidisuniquepertable):WITHselAS(SELECTid,title,row_number()OVER(ORDERBYid)ASrnFROMpostsWHEREidIN(1,2)--selectrowstocopyORDERBYid),insAS(INSERTINTOposts(title)SELECTtitleFROMselORDERBYid--ORDERredundanttobesureRETURNINGid)SELECTi.id,s.idASfrom_idFROM(SELECTid,row_number()OVER(ORDERBYid)ASrnFROMins)iJOINselsUSING(rn);Thissecondqueryreliesontheundocumentedimplementationdetailthatrowsareinsertedintheorderprovided.ItworksinallcurrentversionsofPostgresandisprobablynotgoingtobreak.留着做参考吧.

感谢各位的阅读，以上就是“PostgreSQL数据库中如何从update返回old”的内容了，经过本文的学习后，相信大家对PostgreSQL数据库中如何从update返回old这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！