How to update data with case conditional in postgresql?

dwlpra 0
Viewed 23

I want to update data using query but when column is not null, it will not update and still use old data. I tried with this query but failed.

UPDATE tableA SET 
column2 = case column2 WHEN NULL THEN to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss') end,
column3 = CASE column3 WHEN NULL THEN to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss')  end,
colum3 = '1'
WHERE column1='abc';
sql postgresql
2 Answers

to check for NULL you need IS NULL

UPDATE tableA SET 
column2 = case  WHEN column2 IS  NULL THEN to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss') end,
column3 = CASE  WHEN column3 IS NULL THEN to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss')  end,
colum3 = '1'
WHERE column1='abc';
nbk0

Alternatively, you can use coalesce:

UPDATE tableA
SET 
  column2 = coalesce (column2, to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss')),
  column3 = coalesce (column3, to_timestamp('2022-08-09 13:57:40', 'YYYY-MM-DD hh24:mi:ss')),
  colum3 = '1'
WHERE
  column1='abc' and
 (column2 is null or column3 is null or colum3 is distinct from '1')

And a word of advice (reflected in the suggestion above)... evaluating an expression is "cheaper" than doing actual DML, so I'd encourage you to consider, where possible, adding a where clause to determine if it's even worth updating or not.

Postgres uses MVCC, which will essentially kill the original row and create a new row with the modified data. If you're doing this on tens of thousands of rows, it can make a lot of sense to limit those transactions.

In part this comes down to knowing your data, but I think it's generally worthwhile limit updates to those that need to happen.

Hambone0
Related