How can I insert the return of DELETE into INSERT in postgresql?

Question

I am trying to delete a row from one table and insert it with some additional data into another. I know this can be done in two separate commands, one to delete and another to insert into the new table. However I am trying to combine them and it is not working, this is my query so far:

insert into b (one,two,num) values delete from a where id = 1 returning one, two, 5;

When running that I get the following error:

ERROR: syntax error at or near "delete"

Can anyone point out how to accomplish this, or is there a better way? or is it not possible?

score 70 · Answer 1 · answered Jul 05 '11 at 06:31

70

You cannot do this before PostgreSQL 9.1, which is not yet released. And then the syntax would be

WITH foo AS (DELETE FROM a WHERE id = 1 RETURNING one, two, 5)
    INSERT INTO b (one, two, num) SELECT * FROM foo;

answered Jul 05 '11 at 06:31

Peter Eisentraut

35,221
12
85
90

1

Thank you very much for this answer, when I move to 9.1 I will use this, for now Tometzky's method will have to do. – lanrat Jul 06 '11 at 04:47
1

Nice answer now that most people are using versions much greater than 9.1. No need for triggers. – sheepdog Dec 17 '18 at 07:42
1

The docs also have a similar example of this and go into some [more detail](https://www.postgresql.org/docs/12/queries-with.html#QUERIES-WITH-MODIFYING). – Greg Feb 15 '20 at 00:53

score 8 · Accepted Answer · answered Jul 05 '11 at 16:01

Before PostgreSQL 9.1 you can create a volatile function like this (untested):

create function move_from_a_to_b(_id integer, _num integer)
returns void language plpgsql volatile as
$$
  declare
    _one integer;
    _two integer;
  begin
    delete from a where id = _id returning one, two into strict _one, _two;
    insert into b (one,two,num) values (_one, _two, _num);
  end;
$$

And then just use select move_from_a_to_b(1, 5). A function has the advantage over two statements that it will always run in single transaction — there's no need to explicitly start and commit transaction in client code.

score 1 · Answer 3 · answered Jul 23 '13 at 09:33

For all version of PostgreSQL, you can create a trigger function for deleting rows from a table and inserting them to another table. But it seems slower than bulk insert that is released in PostgreSQL 9.1. You just need to move the old data into the another table before it gets deleted. This is done with the OLD data type:

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
        INSERT INTO another_table VALUES(OLD.column1, OLD.column2,...);
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER moveDeleted
BEFORE DELETE ON table 
    FOR EACH ROW
        EXECUTE PROCEDURE moveDeleted();

As above answer, after PostgreSQL 9.1 you can do this:

WITH tmp AS (DELETE FROM table RETURNING column1, column2, ...)
    INSERT INTO another_table (column1, column2, ...) SELECT * FROM tmp;

Explain where do you add the `WHERE` clause if you want to move some rows. `DELETE FROM table WHERE RETURNING`... — Horaciux, Jun 09 '21 at 21:07

score -1 · Answer 4 · answered Jul 05 '11 at 03:57

-1

That syntax you have there isn't valid. 2 statements is the best way to do this. The most intuitive way to do it would be to do the insert first and the delete second.

answered Jul 05 '11 at 03:57

Al W

7,539
1
19
40

score -1 · Answer 5 · answered Jul 05 '11 at 06:03

-1

As "AI W", two statements are certainly the best option for you, but you could also consider writing a trigger for that. Each time something is deleted in your first table, another is filled.

answered Jul 05 '11 at 06:03

plang

5,446
3
25
36

How can I insert the return of DELETE into INSERT in postgresql?

5 Answers5

Linked