[SOLVED] Postgres SQL : How to replace value in column without adding new column?

Postgres SQL : How to replace value in column without adding new column?

I want to replace '' or '0.00' values from specific column with '' only, without adding new column. I was trying to do as below but its not working.

DB columns:

  id    rt1     rt2
   x    0.345   
   y    0.00    0.345

Expected result:

 id rt1     rt2   new column
  x 0.345            0.345
  y 0.00    0.345    0.345

Based on conversation ,below is my expected result.

 id      rt1    rt2      new column
 'a'    0.345,  null     0.345
 'b'    0.00,  0.345     0.345
  'c'   0.345, 0.445     more 0.00 values cant be in both columns
  'd'   0.445, 0.345     more 0.00 values cant be in both columns
 'e'    0.00,  0.00      null
 'f'    0.00,  null      null
 'g'    null,  0.00      null

My condition: If rt1 is 0.00 or '', then '' else rt1. Same for r2 and then create new column.

SELECT id,
   CASE WHEN rt1 = '0.00' THEN '' ELSE rt1 END AS rt1,
   CASE WHEN rt2 = '0.00' THEN '' ELSE rt2 END AS rt2,
   Case when rt1 != ''  then rt1 else rt2 end as new_rt_column
   
FROM tablename;

Solution

You can use the function GREATEST() to get the greatest of the 2 values and NULLIF() to return null if the greatest value is 0.00:

SELECT id, 
       rt1, 
       rt2,
       NULLIF(GREATEST(NULLIF(rt1, '')::float, NULLIF(rt2, '')::float), 0) AS new_rt_column
FROM tablename;

See the demo.