Back to cookbooks list Articles Cookbook

How to Remove Trailing Zeros from a Decimal in PostgreSQL

  • ::
  • TRUNC()

Problem:

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

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).

idnamewidth
1Large Satin N241.2000
2Creation Yc20.5500
3Three Color 4F13.2050
4Blue Ribbon LB10.1265

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:

namenew_width
Large Satin N241.2
Creation Yc20.55
Three Color 4F13.205
Blue Ribbon LB10.1265

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.

Discover the best interactive PostgreSQL courses

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.

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:

idnamewidth
1Large Satin N241.200
2Creation Yc20.500
3Three Color 4F13.200
4Blue Ribbon LB10.100

Solution 2:

Here’s the query you’d write:

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

Here’s the result:

namenew_width
Large Satin N241.0
Creation Yc20.5
Three Color 4F13.2
Blue Ribbon LB10.1

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

Recommended courses:

Recommended articles:

See also: