In PostgreSQL, we can use the trim_scale() function to remove any trailing zeros from the end of the fractional part.

This doesn’t change the value, it merely reduces the number characters whenever there are unnecessary zeros on the end.

Example

Here’s an example to demonstrate:

SELECT trim_scale(3.9300);

Result:

3.93

In this case the two zeros were removed from the right side of the number.

If there’s a non-zero digit on the end, then nothing will be trimmed:

SELECT trim_scale(3.93001);

Result:

3.93001

In this example the original value is returned unchanged.

Here are some more examples:

SELECT 
    trim_scale(8) AS "8",
    trim_scale(8.00) AS "8.00",
    trim_scale(8.10) AS "8.10",
    trim_scale(8.11) AS "8.11",
    trim_scale(8.0000000000000000) AS "8.0000000000000000";

Result:

 8 | 8.00 | 8.10 | 8.11 | 8.0000000000000000 
---+------+------+------+--------------------
 8 |    8 |  8.1 | 8.11 |                  8

We can see that, whenever we passed a value that only had zeros for its fractional part, that value was returned without a fractional part.

Expressions

We can include expressions, such as this.

SELECT 
    15.123 * 1.75000 AS "Raw",
    trim_scale(15.123 * 1.75000);

Result:

     Raw     | trim_scale 
-------------+------------
 26.46525000 |   26.46525

Passing a Negative Value

We can pass a negative value to the function:

SELECT trim_scale(-2.3400);

Result:

-2.34

Passing Zero

We can pass zero:

SELECT trim_scale(0);

Result:

0

Passing Null

Passing null results in null:

SELECT trim_scale(null);

Result:

null

Passing the Wrong Argument Type

Passing the wrong argument type results in an error:

SELECT trim_scale('cat');

Result:

ERROR:  invalid input syntax for type numeric: "cat"
LINE 1: SELECT trim_scale('cat');
                          ^

Similar Functions

There’s also a scale() function, which returns the number of fractional digits, and a min_scale() function, which returns the number of fractional digits with any trailing zeros removed from the fractional part.

Here’s a comparison of each function:

SELECT 
    trim_scale(8.00) AS "trim_scale",
    scale(8.10) AS "scale",
    min_scale(8.00) AS "min_scale";

Result:

 trim_scale | scale | min_scale 
------------+-------+-----------
          8 |     2 |         0