Update different columns on different clauses 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 &gt; $VAR


and

UPDATE tablename
SET col2 = col2 + 2
WHERE col2 &gt;= $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.
I would probably do something like this:

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


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