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.
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:12where 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:58UPDATE
is applied to all rows for whichWHERE
clause evaluates toTRUE
(or there is noWHERE
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