Skip site navigation (1) Skip section navigation (2)

Function trunc() behaves in unexpected manner with different data types

From: "Nathan M(dot) Davalos" <n(dot)davalos(at)sharedmarketing(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Function trunc() behaves in unexpected manner with different data types
Date: 2011-02-24 19:01:21
Message-ID: 2701CF596B80DC44815FDBFFF5881A1E0104BB63@exchange01.sharedmarketing.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I ran into something interesting with using trunc() and different data
types:

The following is a simplified from the statement we're using and
produces the same results:

 

select trunc( ((cast(2183.68 as numeric) - cast(1 as numeric)) )*100)
/100 

  yields 2184.68

 

select trunc(((cast(2183.68 as numeric) - cast(1 as double precision)
))*100) /100

  yields 2184.67

 

select trunc(cast(2184.68 as double precision) *100) 

  yields 218467 instead of 218468

 

This only happens on certain ranges of numbers. Doing the same thing
with the number 3183.68 yields the same result in both cases. It only
appears to happen when a number is declared as a double and there is no
number past the last significant digit or the number past the last
significant digit is a zero AND falls within a certain range of numbers.
For instance select trunc(cast(2184.681 as double precision) *100)
yields 218468, but select trunc(cast(2184.680 as double precision) *100)
yields 218467.

 

I already made sure everything we're using is just defined as numeric to
avoid the issue.

Responses

pgsql-bugs by date

Next:From: Merlin MoncureDate: 2011-02-24 19:31:22
Subject: Re: Function trunc() behaves in unexpected manner with different data types
Previous:From: Ross BarrettDate: 2011-02-24 16:36:34
Subject: BUG #5899: Memory corruption when running psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group