PostgreSQL – How to Round Numbers

postgresqlpostgresql-9.4

when we use SUM on a data table gives a 1 or 2 cents of difference due to rounding in Postgres.

We are using Postgres 9.4, the field data types is numeric with 19,3
and the result have to be with two decimals.

for example:

 15.309
 10.111
  5.005

and the result must be: 30.43

the query is:

SELECT SUM(fcv_total_comprobante)::numeric(19,2) 
FROM factura_venta 
WHERE DATE(fcv_fecha_comprobante) BETWEEN '2015-09-01' AND '2015-09-30'

the result given is 30.42 (because is 30.425) and must by 30.43

which would be the best way to round a number to 2 decimal places?

Best Answer

Define the attribute/column being summed as type money or as type numeric. If defining numeric you'll need to specify the percision.

http://www.postgresql.org/docs/9.4/static/datatype-money.html#DATATYPE-MONEY-TABLE

Related Solutions

PostgreSQL – Changing Float to Numeric Casts from Assignment to Implicit

Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.

Overriding with your own function is the correct way to go.

Postgresql – Determine percentage from count() without cast issues

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.
Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).
It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).
NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:

SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;

Best Answer

Related Solutions

PostgreSQL – Changing Float to Numeric Casts from Assignment to Implicit

Postgresql – Determine percentage from count() without cast issues

Related Question