Question 114488 in PostgreSQL Question posted in
The official documentation can be found here.

How to update data with case conditional in postgresql?

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';
Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search