PostgreSQL SELECT, UPDATE and INSERT together

Question

I'm currently trying to SELECT columns from a table, do some arithmetic with the selected values and then INSERT the updated values back into the same table. So far, I'm thinking of SELECT all columns, copy to another table, UPDATE that table and copy it back but this seems kinda redundant.

INSERT INTO tableB (x, y) SELECT x, y FROM tableA;

UPDATE tableB SET y = y + 1;

INSERT INTO tableA (x, y) SELECT x, y FROM tableB;

where x and y are declared UNIQUE(x, y)

EDIT: Is there a way to avoid creating another table for this transaction?

If you want to insert into two different tables then you need two statements AFAIK. And if the two inserts are logically dependent, then you might want to use a transaction. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 9, 2017 at 11:09
I think my question might be slightly ambiguous but I'm trying to copy and update the values in one table only, in this case tableA. I created tableB to hold the temporary values. — Larrrrrrrrrry
– Larrrrrrrrrry, Commented Dec 9, 2017 at 11:17

score 3 · Accepted Answer · 2017-12-09 11:28:16Z

3

If you want to copy the changed data, you can do that with a single insert:

insert into tablea (x,y)
select x, y + 1
from tablea;

edited Dec 9, 2017 at 11:28

answered Dec 9, 2017 at 11:25

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Larrrrrrrrrry Over a year ago

I'm trying to copy the values, not directly update it. The old values should remain, only that the new values to be inserted depends on the old ones. Sorry if its slightly confusing.

user330315 Over a year ago

You can do that with a single statement

Tim Biegeleisen · Accepted Answer · 2017-12-09 11:06:20Z

1

If I read your logic correctly, then you only need two inserts here, possibly inside a transaction:

BEGIN;
    INSERT INTO tableB (x, y) SELECT x, y+1 FROM tableA;
    INSERT INTO tableA (x, y) SELECT x, y FROM tableB
COMMIT;

The update after the insert is unnecessary as you can simply insert the incremented values of y.

edited Dec 9, 2017 at 11:06

answered Dec 9, 2017 at 11:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Larrrrrrrrrry Over a year ago

Would something like timestamp addition with interval work in the first statement?

Tim Biegeleisen Over a year ago

@Larrrrrrrrrry If it works in any select, it should work here. I think you need two inserts, but the update looks unnecessary to me.

Collectives™ on Stack Overflow

PostgreSQL SELECT, UPDATE and INSERT together

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related