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)
    (DELETE FROM table WHERE id=some_number returning user_id),
    current_timestamp+'1 day'

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


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;
  • So, I'm trying to extract only user_id from the DELETE and a separate some_timestamp value. (Edited the question just now.) Jan 4 '21 at 20:28
  • @GhanashyamS: see my edit Jan 4 '21 at 20:32
  • Any way I can achieve this...? timestamp (current_timestamp+'1 day') Jan 4 '21 at 20:45
  • current_timestamp + interval '1 day' will work Jan 4 '21 at 21:12
  • Perfect! Again! Thank you!! Jan 4 '21 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.