Remove trailing zeroes from number returned by trunc() function

Question

I am trying to truncate numbers in PostgreSQL with:

SELECT trunc(31.71429,15); 

I have this output:

31.714290000000000

But in Oracle I have this:

31.71429

How can have the same output as in Oracle?

Answer 1

This is behave of Numeric data type. The most easy solution - with possible lost some small precision is cast to double precision:

postgres=# SELECT trunc(31.71429,15);
┌────────────────────┐
│       trunc        │
╞════════════════════╡
│ 31.714290000000000 │
└────────────────────┘
(1 row)

postgres=# SELECT trunc(31.71429,15)::double precision;
┌──────────┐
│  trunc   │
╞══════════╡
│ 31.71429 │
└──────────┘
(1 row)

Answer 2

The value is correct, your "problem" is with the display - the text representation. So use to_char() to format numbers to your liking. You can use the display modifier FM to suppress insignificant '0':

SELECT to_char(trunc(31.71429,15), 'FM9999999999.999999999999999') 

Produces the desired result - as text for display (up to 10 digits before and 15 after the comma - add more if there can be more):

'31.71429'

BTW, the manual is a bit misleading about that. It says:

prefix fill mode (suppress leading zeroes and padding blanks)

But 'FM' also suppresses insignificant trailing zeroes. I filed a documentation bug report.

Answer 3

As stated in this other thread, you can now use the new trim_scale(numeric) function starting with PostgreSQL 13.

e.g. trim_scale(8.4100) → 8.41

Answer 4

As per w3resource-http://www.w3resource.com/PostgreSQL/trunc-function.php

The PostgreSQL trunc() function is used to truncate a number to a particular decimal places. If no decimal places are provided it truncate toward zero(0).

Example 1: PostgreSQL TRUNC() function

SELECT TRUNC(123.4562) AS "Truncate";
Output : Truncate
'---------
123
(1 row)

Example 2: PostgreSQL TRUNC() function

SELECT TRUNC(67.4562,1) AS "Truncate upto 1 decimal";
Output : Truncate
'---------
123.4
(1 row)

Example 3: PostgreSQL TRUNC() function

SELECT TRUNC(67.4562,2) AS "Truncate upto 2 decimal";
Output : Truncate
'---------
123.45
(1 row)

So, second value after comma is values to show after decimal.