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.

• Mihai: Not sure if CASE accepts a comparison UPDATE t SET col1= CASE WHEN col1 > $VAR THEN col1+2 ELSE col1 END ,col2 = CASE WHEN col1 <= $VAR THEN col2+2 ELSE col2 END
• user11944: Would using CASE have a better performance than two UPDATES ? (as in the worst case i would set most of the rows col1 and col2 to the same value as before)
• Mihai: Hitting the table once is better than 2 queries.The optimizer is smart enough to not do the update if it`s the same value as the existing one.
• user1822: Mihai's suggestion is good, except that you should add a where WHERE col1 > $VAR or col2 >= $VAR to the update to make sure that you only touch rows that you are interested in. Otherwise that single update would be much costlier than two updates if only parts of all rows are updated.
• Erwin Brandstetter: @Mihai: This is incorrect. In Postgres, an UPDATE is applied to all rows for which WHERE clause evaluates to TRUE (or there is no WHERE clause). Postgres does not and must not decide to do nothing when it is told to do something, even if the whole row remains unchanged (some system columns do not). Triggers and other things may depend on that.

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.