Postgres SQL: INSERT from DELETE

Question

I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like:

INSERT INTO table (user_id,timestamp)
VALUES (
 (DELETE FROM table WHERE id=some_number returning user_id),
 current_timestamp+'1 day'
)
RETURNING *;

I am getting a syntax error doing this... Can someone help me here plz...?

Answer 1

You can use a data modifying common table expression:

with deleted as (
 DELETE FROM some_table
 WHERE id = 42
 returning user_id
)
INSERT INTO other_table(user_id, "timestamp")
select user_id, timestamp '2020-01-04 21:32:34'
from deleted;

• Ghanashyam Sateesh: So, I'm trying to extract only user_id from the DELETE and a separate some_timestamp value. (Edited the question just now.)
• user1822: @GhanashyamS: see my edit
• Ghanashyam Sateesh: Any way I can achieve this...? timestamp (current_timestamp+'1 day')
• user1822: current_timestamp + interval '1 day' will work
• Ghanashyam Sateesh: Perfect! Again! Thank you!!