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

by

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill ‘Cat Recall’ explains in the comments, the version of round that takes a precision is only available for numeric.

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist

regress=> \df *round*
                           List of functions
   Schema   |  Name  | Result data type | Argument data types |  Type  
------------+--------+------------------+---------------------+--------
 pg_catalog | dround | double precision | double precision    | normal
 pg_catalog | round  | double precision | double precision    | normal
 pg_catalog | round  | numeric          | numeric             | normal
 pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)

regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
 round 
-------
  3.14
(1 row)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

If you’re formatting for display to the user, don’t use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn’t affected by whatever weirdness your client language might do with numeric values. For example:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
    to_char    
---------------
 3.14
(1 row)

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don’t want any padding with leading spaces.

More Related Contents:

  1. Rails 4 LIKE query – ActiveRecord adds quotes
  2. Select rows which are not present in other table
  3. Optimize GROUP BY query to retrieve latest row per user
  4. How to implement a many-to-many relationship in PostgreSQL?
  5. Check if value exists in Postgres array
  6. How to use ANY instead of IN in a WHERE clause?
  7. Is there a shortcut for SELECT * FROM?
  8. sql statement error: “column .. does not exist”
  9. What is the difference between single quotes and double quotes in PostgreSQL?
  10. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  11. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  12. Best way to count rows by arbitrary time intervals
  13. PostgreSQL sequence based on another column
  14. Sorting null values after all others, except special
  15. Reference alias in WHERE clause
  16. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  17. Slow simple update query on PostgreSQL database with 3 million rows
  18. Discard millisecond part from timestamp
  19. Return setof record (virtual table) from function
  20. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  21. SQL column reference “id” is ambiguous
  22. IN Clause with NULL or IS NULL
  23. How to convert PostgreSQL 9.4’s jsonb type to float
  24. postgres – where in (list) – column does not exist
  25. incomplete information from query on pg_views [duplicate]
  26. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  27. Referencing current row in FILTER clause of window function
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment