1

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.

  • 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 – Mihai Aug 16 '14 at 7:12
  • 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) – user11944 Aug 16 '14 at 8:20
  • 2
    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. – Mihai Aug 16 '14 at 8:46
  • 1
    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. – a_horse_with_no_name Aug 16 '14 at 8:58
  • 1
    @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. – Erwin Brandstetter Aug 17 '14 at 23:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.