Partial update based on condition in postgres

Question

I would like to make an update to a table, and the columns updated is dependent on a condition. I already know how to make a conditional WHERE clause like so:

UPDATE table SET colA='apple', colB='banana' WHERE colC='fruits

However I would like to base the update on a condition, such that if the conditional is false, only colA will update. Something to the extent of the following:

UPDATE table SET colA='apple', (if $1=true colB='banana') WHERE colC='fruits

The only resources I'm finding are pertaining to the first example. Is this possible in Postgres? I would like to avoid making separate queries for this type of condition.

Answer 1

You could use CASE expressions to control the logic of updating the B column:

UPDATE table
SET
    colA = 'apple',
    colB = CASE WHEN $1 = true THEN 'banana' ELSE colB END
WHERE
    colC = 'fruits';

When the $1 flag be set to true, the B column would be updated to banana, otherwise the update would just set to the current value, which is effectively a no-op.

Answer 2

A CASE statement might help you here:

UPDATE table
SET colA = 'apple',
    colB = CASE WHEN $1 = true THEN 'banana' ELSE colB END
WHERE colC = 'fruits';

However, there may be risk of SQL injection, in which case you may be better off having two separate queries, selected by your application logic.