Search code examples
sqlpostgresqlcastingroundingaverage

Is this the correct way to cast, round, and avg an object in SQL?

QUESTION

How do I use avg and round to provide a decimal point in PostgreSQL?

What function do I need to convert a string to a decimal number?

FUNCTIONS USED

1st function

AVG(ratings.dating) AS scent_avg

1st error

Error: expected { scents_id: 1, scent_avg: 4.5 } response body, got { scents_id: 1, scent_avg: '4.5000000000000000' }

2nd function

ROUND(AVG(ratings.rating), 1) AS scent_avg

2nd error

Error: expected { scents_id: 1, scent_avg: 4.5 } response body, got { scents_id: 1, scent_avg: '4.5' }

3rd function

What function is used to convert a string to a number?

EDIT

Looks like I had a mental lapse. sticky bit clarified that int has no decimals! I removed the int bit from the original post.

Solution

  • Formatted string

    Use to_char() to get a formatted string without insignificant zeroes (or padding blanks) - "rounded to the tenth decimal place" as you commented:

    SELECT to_char(round(avg(ratings.rating), 10), 'FM999999999990.9999999999');

    Note the one 0. Typically you want that position in any case. Like for 0.3. Add as many 9 as you want to allow digits. The manual:

    0 specifies a digit position that will always be printed, even if it contains a leading/trailing zero. 9 also specifies a digit position, but if it is a leading zero then it will be replaced by a space, while if it is a trailing zero and fill mode is specified then it will be deleted.

    And about the FM prefix:

    fill mode (suppress leading zeroes and padding blanks)

    Numeric value without insignificant trailing zeroes

    Cast to double precision (float8) to get a numeric value without insignificant trailing zeroes. The cast trims insignificant zeroes. Generally, casting to a floating point number can introduce corner case rounding errors.

    I had suggested more sophisticated solutions first, but since you are only interested in precision up to the tenth decimal place and float8 is precise up to 15 fractional digits, the problem does not apply. The manual:

    On all currently supported platforms, the real type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

    So just:

    SELECT round(avg(ratings.rating), 10)::float8;

    Note that we cast after rounding, as the variant of round() accepting a number of decimal places only works for numeric (due to the inexact nature of internal storage of floating point numbers).

    Inside Postgres

    ... you wouldn't worry much about trailing zeroes. The manual:

    Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

    See:

    Inside Postgres, equality is established correctly:

    SELECT numeric '4.50000000' = numeric '4.5';  -- true
SELECT jsonb '{"scents_id": 4.5}' = jsonb '{"scents_id": 4.5000}';  -- true

    Your client throwing the error seems to compare text representations, which is subtly incorrect. So you may have to format like your client expects ...