Casting to Float 2 dp in Postgres

Question

I'm trying to cast average_salary and total_salary as float 2 decimal places but I keep getting an error. Am I doing this correctly?

Error: Test Failedexpected 2 to be a kind of Float

SELECT 
  j.job_title,
  SUM(j.salary)/COUNT(p.id)::float, 2 AS average_salary,
  COUNT(p.id) AS total_people,
  SUM(j.salary):: float, 2 AS total_salary
  FROM job j
    JOIN people p
    ON p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary DESC
  LIMIT 100

Answer 1

You can use a floating point, but you can't set it to a certain number of decimal places. The floating point types available in PostgreSQL are real or double precision.

You can either do this:

expression::real

or, to get 2 decimal places, use numeric(10,2), where 10 is the precision (the total number of digits) and 2 is the scale (the number of decimal places):

postgres=# SELECT (15::decimal/7)::decimal(10,2);
 numeric 
---------
    2.14
(1 row)

Note that you will have to cast one of the values in the expression as decimal, otherwise it will treat both as int, and the division will result in an integer, so casting to a decimal after that won't be accurate.

For example:

postgres=# SELECT (15/7)::decimal(10,2);
 numeric 
---------
    2.00
(1 row)

This also applies to the real type, where you would have to cast at least one of the values in your expression as real to get an accurate result.

See the documentation for more information: https://www.postgresql.org/docs/12/datatype-numeric.html