How to round an average to 2 decimal places in PostgreSQL?

sql

prompt-engineering

best-practices

dataframe

byAnton Shumikhin·Oct 6, 2024

To obtain a 2-decimal precision average in PostgreSQL, apply the ROUND function directly to AVG, as shown below:

SELECT ROUND(AVG(column_name)::numeric, 2) FROM table_name;

This statement calculates the average of column_name and asserts the result to exactly 2 decimal places by casting to numeric data type.

Data types and rounding

Rounding in PostgreSQL can surprisingly be tricky due to the different behaviors of numeric types. PostgreSQL's round function can accurately handle the numeric data type. Because numeric type is an arbitrary precision number format, it maintains decimal precision to a specified scale.

However, applying round to types such as double precision might yield unexpected rounding errors due to the binary nature of these types.

Comment: Just when you thought you had it all figured out, along come numeric types to spice things up.

Doing the cast: essential to rounding

Casting allows you to convert a value from one data type to another. In the context of rounding, casting to numeric is key to achieving accurate results:

SELECT ROUND(AVG(column_name)::numeric, 2) FROM table_name;

The ::numeric in the above statement is a shorthand casting notation. This casts the average to numeric before rounding, ensuring predictable rounding behavior.

Display formatting: a subtle art

For display purposes, you might want to convert the numbers to a formatted string using the to_char function:

SELECT to_char(AVG(column_name), 'FM999999990.00') FROM table_name;

The to_char function combines rounding and formatting into one step, while the FM prefix eliminates leading spaces in the formatted numbers.

Know your numbers: numeric type pitfalls

Awareness of the different numeric types in PostgreSQL is crucial. Floats, for example, can, due to their binary nature, prove to be a real headache when rounding and formatting.

Advanced PostgreSQL: creating custom functions

Your rounding needs might not always align with the standard PostgreSQL round function. If that's your case, contemplate creating a custom function. User-defined functions can encapsulate specific rounding logic and carry the IMMUTABLE clause option for PostgreSQL to cache the function's result when the inputs are identical.

Comment: This is like those custom shortcuts on your phone that find the nearest pizza place. Only in PostgreSQL.

`TRUNC()` vs `ROUND()`: a worthwhile distinction

Knowing the difference between the TRUNC() and ROUND() functions can prove immensely beneficial. TRUNC() just trims off decimals while ROUND() looks at the next digit and decides to round it up or not.

Comment: Precision is everything, it turns out.

Old but gold: the classic cast syntax

The old cast syntax can slighty improve performance in certain versions of PostgreSQL. When execution time is of the essence, this relic can be your secret weapon:

SELECT ROUND(CAST(AVG(column_name) AS numeric), 2) FROM table_name;

Caution with floats

It's important to remember that if you are dealing with float values, you might encounter unpredictable behavior due to their binary representation. Floats converted to their decimal counterparts don't always convert neatly, which can throw off your expectations when rounding.

Consistent formatted results: The key

When dealing with rounded averages, to_char can be a game-changer, especially when multiple clients need to see the same formatted result:

SELECT to_char(AVG(column_name)::numeric, 'FM999999990.00') FROM table_name;

A reusable piece of magic: user-defined functions

If your application often rounds off values across various queries, it might be beneficial to have a reusable function:

CREATE OR REPLACE FUNCTION custom_round(value double precision) RETURNS numeric AS $$
BEGIN
  RETURN ROUND(value::numeric, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;