Numbers, rounding & casting

Arithmetic, round(), and converting types with ::.

Numbers and conversions

Beyond + - * / Postgres gives you round(n), round(n, d) (d decimals), ceil, floor, abs, mod, power.

The :: operator casts a value to another type — '42'::int, price::int, now()::date. (The standard form CAST(price AS int) does the same.) Casting numeric to int rounds.

SELECT round(avg(price), 2) AS avg_price FROM books;

floor and a cast disagree on the same value: 14.99::int is 15 because casting rounds, while floor(14.99) is 14. Reach for round when you mean "nearest" and floor when you mean "down".