I have a database table in Postgres 9.3 with the following layout:
id SERIAL,
col1 INT,
col2 INT
Whenever a new row is inserted, I will have to update ALL rows col1
and/or col2
in different cases but want to do it in only one (faster) query to avoid performance problems (especially because I'll have to do a lock in the table to avoid corruption).
Right now it is done with two queries:
UPDATE tablename
SET col1 = col1 + 2
WHERE col1 > $VAR
and
UPDATE tablename
SET col2 = col2 + 2
WHERE col2 >= $VAR
note, $VAR
is the same in both queries. It is the col1
value of the new row.
I can't find anything related to this question in the PostgreSQL manual.
EXAMPLE DB
ID | col1 | col2
1 | 1 | 12
2 | 2 | 5
3 | 3 | 4
4 | 6 | 11
5 | 7 | 8
6 | 9 | 10
If I added a new row with id
7 and col1
5 and col2
6, the table would become
ID | col1 | col2
1 | 1 | 14
2 | 2 | 7
3 | 3 | 4
4 | 8 | 13
5 | 9 | 11
6 | 11 | 12
7 | 5 | 6
In this case, all rows changed at least one column. But in a table with a thousand of rows, most would not even change, so I believe using CASE
would not be good with performance.
Best Answer
I would probably do something like this:
Make sure that
col1
andcol2
are indexed if you are only updating a few rows andtablename
has many rows.