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

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.

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`:

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

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.