Postgresql – Use CASE to select columns in UPDATE query

postgresqlupdate

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:

SELECT CASE WHEN val = 0 THEN column_x
            WHEN val = 1 THEN column_y
            ELSE 0
       END AS update, ...

Is something similar at all possible when performing an UPDATE query in Postgres (i.e. choose which columns should be updated)? I assume not since I couldn't find anything about this, but maybe someone has a clever alternative (besides using a procedure or updating each column using a CASE to determine if the value of the column should be assigned a new value or simply reassigned the existing value). If there is no easy alternative, I'll of course accept that as an answer as well.

Extra info: In my case I have 14 potential columns that may be updated, with only one being updated per matching row (the table to be updated is joined with another in the query). The amount of rows to update will most likely vary, could be dozens or hundreds. I believe indexes are in place for the joining conditions.

Best Answer

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

UPDATE some_table
SET    column_x = CASE WHEN should_update_x THEN new_value_for_x ELSE column_x END
     , column_y = CASE WHEN should_update_y THEN new_value_for_y ELSE column_y END
     , column_z = CASE WHEN should_update_z THEN new_value_for_z ELSE column_z END
FROM   ...

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.

Related Solutions

PostgreSQL Update – How to Update Different Columns on Different Clauses

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.

PostgreSQL – Slow Updates When Other Columns Are Indexed

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten

That's not the case.

In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential log for crash recovery) then to the table.

PostgreSQL's autovacuum then comes along later and marks the old row versions as free space that can be re-used.

See the user manual for more information on concurrency control and MVCC.

the data is not changing size (e.g., rewriting a boolean value)

Irrelevant, because the row must be rewritten anyway.

only one column is being updated, and it is non-indexed
there are indexes on other columns

This only matters insofar as it affects HOT updates, where PostgreSQL can potentially avoid writing new index entries for a row update if no indexed columns are modified and there's enough free space on the same disk page (8k block) to store a new copy of the row.

Why do indexes on columns irrelevant to this query balloon the update time?

In most cases PostgreSQL must add new index entries even if you didn't modify indexed columns, because it has to write a new version of the row to a separate database page.

Setting a FILLFACTOR of 50 or less can help with this, as it encourages PostgreSQl to keep more free space for updates, at the cost of scans having to read and process more data.

Best Answer

Related Solutions

PostgreSQL Update – How to Update Different Columns on Different Clauses

PostgreSQL – Slow Updates When Other Columns Are Indexed

Related Question