Изменить данные в строке, скопировать ее и вставить с новыми данными

Question

У меня есть таблица с сообщениями:

messages {
    id bigint auto primary
    user_id bigint
    content text
    replaced_by bigint
    created_at int
}

Мне нужно в одном запросе скопировать row в новую стоку с новым id и в ней же изменить replaced_by на id этой новой строки.

Тоесть по логике мне нужно реализовать изменение сообщения без удаления старого, и когда оригинальное сообщение изменяется, то в нем изменяется только replaced_by и создается новое сообщение со всем содержимым старого но с новым content

Сейчас это сделано в двух запросах, но это не совсем то что я хочу.

Answer 1

create table messages (
    id serial primary key,
    user_id bigint,
    content text,
    replaced_by bigint,
    created_at int
);

insert into messages (user_id, content, created_at)
  values (22, 'aaa', extract(epoch from now()));

select * from messages ;
 id | user_id | content | replaced_by | created_at 
----+---------+---------+-------------+------------
  1 |      22 | aaa     |             | 1601909639
(1 row)

with nv as (
  insert into messages (user_id, content, created_at)
    select user_id, 'NEW_CONTENT', extract(epoch from now())
      from messages where id = 1 returning *
)
update messages m
  set replaced_by = nv.id
  from nv
  where m.id = 1
  RETURNING nv.*;

 id | user_id |   content   | replaced_by | created_at 
----+---------+-------------+-------------+------------
  2 |      22 | NEW_CONTENT |             | 1601909647
(1 row)

select * from messages ;
 id | user_id |   content   | replaced_by | created_at 
----+---------+-------------+-------------+------------
  2 |      22 | NEW_CONTENT |             | 1601909647
  1 |      22 | aaa         |           2 | 1601909639
(2 rows)

Обратите внимание, что в запросе, обновляющем таблицу, сейчас в двух местах стоит id = 1 - это из тестового прогона, могу убрать из ответа.

И имейте ввиду, что этот подход будет верным только если в таблице существует запись с id = 1.

Answer 2

@nörbörnën дал верный для меня ответ. Но я хочу добавить, что я использовал intellij idea и она не подсвечивает синтаксис со звездочкой *. Очень жаль, это сбивает с толку.

Это красиво только если писать так:

БЛОГ НА HUSL

Изменить данные в строке, скопировать ее и вставить с новыми данными

Ответы (2 шт):