Postgresql Return 0 If Returned Value Is Null

postgresql return 0 if returned value is null

use coalesce

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null.  
Null is returned only if all arguments are null. It is often
used to substitute a default value for null values when data is
retrieved for display.

Edit

Here's an example of COALESCE with your query:

SELECT AVG( price )
FROM(
      SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
      WHERE listing_Type = 'AARM'
        AND u_kbalikepartnumbers_id = 1000307
        AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
        AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50
                                     FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) g
                                    WHERE cume_dist < 0.50
                                  )
        AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2
                                     FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) d
                                     WHERE cume_dist < 0.50)
     )s
HAVING COUNT(*) > 5

IMHO COALESCE should not be use with AVG because it modifies the value. NULL means unknown and nothing else. It's not like using it in SUM. In this example, if we replace AVG by SUM, the result is not distorted. Adding 0 to a sum doesn't hurt anyone but calculating an average with 0 for the unknown values, you don't get the real average.

In that case, I would add price IS NOT NULL in WHERE clause to avoid these unknown values.

Postgresql turn null into zero

select coalesce(max(column), 0) from mytable; 

Returning alternative value if null?

As you defined all those columns as UNIQUE you can have multiple null values, but you can't have two rows with the same 'Coming soon' value.

But you can replace those NULL values during retrieval, with the desired replacement:

select id, 
       coalesce(website_link, 'Coming Soon') as website_link,
       coalesce(about, 'Coming soon') as about,
       coalesce(twitter, 'None') as twitter,
       coalesce(instgram, 'None') as instagram
from the_table;

If you don't want to type that every time, create a view which does that for you.

Returning Table with null value causes error

Just because you use the alias posttypeid in the query does not mean that PostgreSQL infers the data type of your PL/pgSQL variable.

Even though NULL can be any data type, PostgreSQL has to determine a data type for the result column of the query. Lacking other information, it arbitrarily chooses text.

Mapping the query result type to the function result type happens later, in PL/pgSQL. That is what causes the error you observe.

You can avoid the problem by specifying the type of NULL with an explicit type cast:

SELECT CAST (NULL AS integer)

How do I select null if no rows match in PostgreSQL?

One solution is a LEFT JOIN, but starting with the customers table. However, it is a little complicated:

SELECT o.customer_id
FROM customer c LEFT JOIN
     order o
     ON c.customer_id = o.customer_id AND
        o.time >= '2021-01-01 10:00:00' AND
        o.time < '2021-01-01 11:00:00'
LIMIT 1;

Note that this is returning customer_id from the orders table. That is how it is NULL if there is no match.

The above does assume that you have at least one customer, which seems like a reasonable assumption.

An alternative is UNION ALL:

WITH c as (
      SELECT customer_id
      FROM customer c JOIN
           order o
           USING (customer_id) 
      WHERE o.time >= '2021-01-01 10:00:00' AND
            o.time < '2021-01-01 11:00:00'
      LIMIT 1
     )
SELECT c.customer_id
FROM c
UNION ALL
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM c);

And a third alternative might be even simpler, a subquery:

select (select o.customer_id
        from order o
        where o.time >= '2021-01-01 10:00:00' AND
              o.time < '2021-01-01 11:00:00'
        limit 1
       ) as customer_id;

If the subquery returns no rows, then the result is NULL.

---

Related Topics