sqlpostgresql

Replace calculated negative values with 0 in PostgreSQL

I have a table my_table:

case_id    first_created  last_paid       submitted_time
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00
   1245    2021-09-13     None            2022-10-31 02:03:59.620348+00:00
   9073    None           None            2021-09-12 10:25:30.845687+00:00
   6891    2021-08-03     2021-09-17      None

I created 2 new variables:

select *,
       first_created-coalesce(submitted_time::date) as create_duration,
       last_paid-coalesce(submitted_time::date) as paid_duration 
from my_table;

The output:

  case_id    first_created  last_paid       submitted_time               create_duration paid_duration
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00     1                3
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00     -450            -405
   1245    2021-09-13     null            2022-10-31 02:03:59.620348+00:00     -412            null
   9073    None           None            2021-09-12 10:25:30.845687+00:00     null           null
   6891    2021-08-03     2021-09-17      null                                 null            null

My question is how can I replace new variables' value with 0, if it is smaller than 0?

The ideal output should look like:

  case_id    first_created  last_paid       submitted_time               create_duration paid_duration
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00     1                3
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00     0                0
   1245    2021-09-13     null            2022-10-31 02:03:59.620348+00:00     0               null
   9073    None           None            2021-09-12 10:25:30.845687+00:00     null             null
   6891    2021-08-03     2021-09-17      null                                 null             null

My code:

select  *,
        first_created-coalesce(submitted_time::date) as create_duration,
        last_paid-coalesce(submitted_time::date) as paid_duration, 
        case
            when create_duration < 0 THEN 0
            else create_duration
        end as QuantityText
from my_table
Solution

  • greatest(yourvalue,0)

    Given yourvalue lower than 0, 0 will be returned as the greater value:

    select *
  ,greatest(0,first_created-coalesce(submitted_time::date)) as create_duration
  ,greatest(0,last_paid    -coalesce(submitted_time::date)) as paid_duration
from my_table;

    This will also change null values to 0.

    case statement

    If you wish to keep the null results, you can resort to a regular case statement. In order to alias your calculation you'll have to put it in a subquery or a cte:

    select *
  ,case when create_duration<0 then 0 else create_duration end as create_duration_0
  ,case when paid_duration  <0 then 0 else paid_duration   end as paid_duration_0
from (
    select *
      ,first_created-coalesce(submitted_time::date) as create_duration
      ,last_paid    -coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

    (n+abs(n))/2

    If you sum a number with its absolute value, then divide by two (average them out), you'll get that same number if it was positive, or you'll get zero if it was negative because a negative number will always balance itself out with its absolute value:

    select *
  ,(create_duration + abs(create_duration)) / 2 as create_duration_0
  ,(paid_duration   + abs(paid_duration)  ) / 2 as paid_duration_0
from (
    select *
      ,first_created-coalesce(submitted_time::date) as create_duration,
      ,last_paid    -coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

    Which according to this demo, is slightly faster than case and about as fast as greatest(), without affecting null values.

    Note that select * pulls everything from below, so you'll end up seeing create_duration as well as create_duration_0 - you can get rid of it by listing your desired output columns explicitly in the outer query. You can also rewrite it without subquery/cte, repeating the calculation, which will look ugly but in most cases planner will notice the repetition and evaluate it only once

    select *
  ,case when first_created-coalesce(submitted_time::date) < 0
        then 0
        else first_created-coalesce(submitted_time::date)
   end as create_duration
  ,(abs(last_paid-coalesce(submitted_time::date))
    +   last_paid-coalesce(submitted_time::date) )/2 
       as paid_duration
from my_table;

    or using a scalar subquery

    select *
  ,(select case when a<0 then 0 else a end 
    from (select first_created-coalesce(submitted_time::date)) as alias(a) ) 
    as create_duration
  ,(select case when a<0 then 0 else a end 
    from (select last_paid-coalesce(submitted_time::date)) as alias(a) ) 
    as paid_duration
from my_table;

    Neither of which help with anything in this case but are good to know.