# How to Remove Trailing Zeros from a Decimal in PostgreSQL

Database:

Operators:

Table of Contents

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

with data in the columns **ribbon**`id`

, `name`

, and `width`

(in meters, denoted by a decimal number with a 4-digit fractional part).

id | name | width |
---|---|---|

1 | Large Satin N24 | 1.2000 |

2 | Creation Yc2 | 0.5500 |

3 | Three Color 4F1 | 3.2050 |

4 | Blue Ribbon LB1 | 0.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:

name | new_width |
---|---|

Large Satin N24 | 1.2 |

Creation Yc2 | 0.55 |

Three Color 4F1 | 3.205 |

Blue Ribbon LB1 | 0.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`

:

id | name | width |
---|---|---|

1 | Large Satin N24 | 1.200 |

2 | Creation Yc2 | 0.500 |

3 | Three Color 4F1 | 3.200 |

4 | Blue Ribbon LB1 | 0.100 |

Here’s the query you’d write:

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

Here’s the result:

name | new_width |
---|---|

Large Satin N24 | 1.0 |

Creation Yc2 | 0.5 |

Three Color 4F1 | 3.2 |

Blue Ribbon LB1 | 0.1 |

Only one digit is displayed in the decimal part. Note that `TRUNC()`

cuts **all digits**, not just zeros.