Update different columns on different clauses

Question

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.

Answer 1

I would probably do something like this:

UPDATE tablename
SET col1 = col1 + CASE WHEN col1 > $VAR THEN 2 ELSE 0 END,
    col2 = col2 + CASE WHEN col2 >= $VAR THEN 2 ELSE 0 END
WHERE col1 > $VAR OR col2 >= $VAR

Make sure that col1 and col2 are indexed if you are only updating a few rows and tablename has many rows.