Q:

Postgresql weird result on having clause

SELECT
T . ID,
T . NAME,
T .feed_id,
T .status,
T .budget,
(
    SELECT
        COUNT (*)
    FROM
        segment
    WHERE
        segment.campaign_id = T . ID
) AS total_segments,
(
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
) AS projected_revenue
  FROM
campaign T
 WHERE
(T .deleted = 0)
AND (site_id = 3)
GROUP BY
T ."id"
  HAVING
  (
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
    ) > 242.45
      ORDER BY
  T . ID DESC

The query works fine when having subquery is greater than (>) or less than (<) but when equals it does not show any result. But it works with:
HAVING (
  SELECT SUM (revenue)
  FROM TRANSACTION 
  WHERE TRANSACTION.campaign_id = T.ID 
    AND TRANSACTION.status = 3
    AND deleted = 0
)::NUMERIC = 242.45

A:

At a guess, revenue is float4 or float8, i.e. a float or double precision value.
As @GáborBakos accurately noted, some values (including 242.45) do not have an exact binary floating point representation. Observe:
regress=> SET extra_float_digits = 3;
SET
regress=> SELECT FLOAT4 '242.45';
   float4   
------------
 242.449997
(1 row)

In general, you should not compare floating point values for equality. Use a small range instead.
See:

http://floating-point-gui.de/errors/comparison/
How should I do floating point comparison?
Floating point equality and tolerances
http://randomascii.wordpress.com/category/floating-point/

Your query works with the cast to NUMERIC because, unlike float4 or float8, NUMERIC is a binary-coded decimal type with arbitrary precision and scale. It can precisely represent 242.45 and it doesn't suffer from the rounding artifacts and other oddities of floating point maths. However, that cast isn't always going to be enough - other values may still not work how you expect. Consider:
regress=> select (float4 '0.3' * 100000000) :: NUMERIC;
     numeric      
------------------
 30000001.1920929
(1 row)

As you can see those rounding errors can accumulate.
Rather than rely on comparing with numeric, you should really use a small range comparision instead of comparing for equality. Or, best of all, use numeric consistently to represent financial and exact quantities, never floating point.

