Back to list PostgreSQL How to Remove Trailing Zeros from a Decimal in PostgreSQL Database: PostgreSQL Operators:::, 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. 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: SQL Basics in SQL Server SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP See also: How to Convert a String to a Numeric Value in PostgreSQL How to Capitalize the First Letter of Every Word in PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.