How to Remove Trailing Zeros from a Decimal in PostgreSQL

Problem:

You’d like to remove trailing zeros from the fractional part of a decimal number in PostgreSQL database.

Example:

Our database has a table named ribbon with data in the columns id, name, and width (in meters, denoted by a decimal number with a 4-digit fractional part).

Let’s get the names of ribbons along with their width in meters, but without trailing zeros.

Solution:

We’ll use the operator :: to convert the decimal number to a REAL data type. Here’s the query you’d write:

SELECT name, 
  width::REAL 
FROM ribbon;

Here’s the result:

Discussion:

Use the :: operator to convert a decimal number containing trailing zeros to a number without additional zeros. This operator converts the value on the left to the data type placed on the right.

In our example, the decimal value in width was converted to the REAL data type, which doesn’t display trailing zeros. The ‘Large Satin N24’ ribbon has a width value of 1.2000 – but we don’t need zeros in the fractional part of this number. Therefore, we converted to the REAL data type to cut them (in our example, three zeros were removed). It is a convenient way to display the number because it removes only the trailing zeros; for example, ‘Blue Ribbon LB1’ hasn’t any trailing zeros after the decimal point, so its value remains the same after conversion.

Solution 2:

If you have the same number of zeros in the fractional part of all your values, use the TRUNC() function. It takes two arguments: the value/column name containing the number and an integer indicating the desired number of fractional digits; the rest is cut. Look at the same example with different values in the column width:

Here’s the query you’d write:

SELECT name, 
  TRUNC(width, 1) 
FROM ribbon;

Here’s the result:

Only one digit is displayed in the decimal part. Note that TRUNC() cuts all digits, not just zeros.