Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to return old from update in PostgreSQL database". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to return old from update in PostgreSQL database".

UPDATE tbl xSET tbl_id = 23, name = 'New Guy'FROM tbl y-- using the FROM clauseWHERE x.tbl_id = y.tbl_id-- must be UNIQUE NOT NULLAND x.tbl_id = 3RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name Old_id | old_name | tbl_id | name-+- 3 | Old Guy | 23 | New Guy WITH sel AS (SELECT tbl_id, name FROM tbl WHERE tbl_id = 3-- assuming unique tbl_id), upd AS (UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3 RETURNING tbl_id Name) SELECT s.tbl_id AS old_id, s.name As old_name, u.tbl_id, u.nameFROM sel s, upd u

UPDATE tbl xSET 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_idRETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name What about insert? WITH sel AS (SELECT id, title FROM posts WHERE id IN (1J2)-select rows to copy), ins AS (INSERT INTO posts (title) SELECT title FROM sel RETURNING id, title) SELECT ins.id, sel.id AS from_idFROM insJOIN sel USING (title) If title is not unique per query (but at least id is unique per table): WITH sel AS (SELECT id, title, row_number () OVER (ORDER BY id) AS rn FROM posts WHERE id IN (1Magne2)-- select rows to copy ORDER BY id), ins AS (INSERT INTO posts (title) SELECT title FROM sel ORDER BY id-- ORDER redundant to be sure RETURNING id) SELECT i.id, s.id AS from_idFROM (SELECT id) Row_number () OVER (ORDER BY id) AS rn FROM ins) iJOIN sel s USING (rn) This second query relies on the undocumented implementation detail that rows are inserted in the order provided. It works in all current versions of Postgres and is probably not going to break. Save it for reference. Thank you for your reading, the above is the content of "how to return old from update in PostgreSQL database". After the study of this article, I believe you have a deeper understanding of how to return old from update in PostgreSQL database. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.