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

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

2

Answers


  1. 0

    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:

    • (-1+abs(-1))/2 = (-1+1)/2 = 0/2 = 0
    • ( 1+abs( 1))/2 = ( 1+1)/2 = 2/2 = 1
    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 make 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 ) as subquery;

    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 ) as subquery;

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

    Login or Signup to reply.
  2. 0

    If you are planning on attaching your SQL Database to an ASP.NET app, you could create a c# script to query your database, and use the following:

    Parameters.AddWithValue(‘Data You want to change’ ‘0’);

    However, if your not using your SQL database with a ASP.NET app, this will not work.

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

Back To Top
Search