Re: Inconsistent results postgresql

Chris Curvey <chris(at)chriscurvey(dot)com> wrote:
> Emir Ibrahimbegovic <emir(dot)ibrahimbegovic(at)gmail(dot)com> wrote:

>> So looking for same date using different date range I get
>> different results, how is this even possible? Can I look at
>> something else? I'm really stuck here

> Remove the sum (just select "payments.amount") and the GROUP BY
> and run your queries.  You'll see that you're getting different
> rows included than you think you are.

Well, either that or he's getting them in a different order and the
data type being summed is a floating point approximate type.  With
the approximate types the order that addition happens in can affect
the sum.  For the most accurate result you want to add from the
numbers closest to zero to the ones farthest away from zero.

If exact results are desired, the best solution is to not use an
approximate data type.  Try converting the column to numeric, which
is not as vulnerable to rounding errors.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company