PostgreSQL TRUNC

This post is really about the importance of data types and the effect it can have on the accuracy of calculations. A small error can have a huge impact!

During some testing, I found a strange thing happen when using the TRUNC() command. This code was converted from by someone from an Oracle procedure.

The initial statement was:

select TRUNC(7006493001860012394 / 36);

which gave this result:

1.9462480560722256e+17

The problem with this result was that the last digit was returned as a zero instead of 6. 

I then ran the following statement to cast the return value to BIGINT to make it display in a more readable format and also because that was the data type the value would be stored in in the code.

select TRUNC(7006493001860012394 / 36)::BIGINT;
194624805607222560

As you can see, the last digit is zero. I decided to check exactly how the TRUNC() function should work. TRUNC() accepts two parameters:

TRUNC(number [, precision])

The precision argument is optional. It defaults to zero if it is not specified. 

If the precision is a positive integer the number of decimal places after the decimal point is truncated to that number.

If the precision is a negative integer the digits to the left of the decimal point are zeroed for the specified number. e.g. Negative 3 (-3) would zero the three digits to the left of the decimal like this: Trunc(123456,-3) = 123000.

select TRUNC(7006493001860012394 / 36,0)::BIGINT;
194624805607222566

select TRUNC(7006493001860012394 / 36,0);
194624805607222566

select (7006493001860012394 / 36);
194624805607222566