How to round REAL type to NUMERIC?

Question

I have table with real column type with example values:

123456,12
0,12345678

And code in stored procedure:

CREATE OR REPLACE FUNCTION test3()
  RETURNS integer AS
$BODY$
   DECLARE
      rec    RECORD;
   BEGIN

      FOR rec IN 

         SELECT
         gme.abs_km as km,
         CAST(gme.abs_km as numeric) as cast,         
         round(gme.abs_km:: numeric(16,2), 2) as round
         FROM gps_entry gme
      LOOP

         RAISE NOTICE 'Km: % , cast: % , round: %', rec.km, rec.cast, rec.round;
         INSERT INTO test (km, casting, rounding) VALUES (rec.km, rec.cast, rec.round);

      END LOOP;
      RETURN 1;      
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Here is output:

2014-02-05 12:49:53 CET NOTICE:  Km: 0.12345678 , cast: 0.123457 , round: 0.12
2014-02-05 12:49:53 CET NOTICE:  Km: 123456.12 , cast: 123456 , round: 123456.00

DB table with columns NUMERIC(19,2):

km        casting   rounding
0.12      0.12      0.12

123456.00 123456.00 123456.00

Why do cast and round functions not work for the value 123456.12?

actually I would like to insert these values to db table with columns numeric(19,2), I have updated code in question — Maciej, Feb 05 '14 at 12:40
Issue seems to be with numbers that have more than 6 digits, for these numbers casting and rounding works: 1.23 1234.12, 123.456 — Maciej, Feb 05 '14 at 12:54

Erwin Brandstetter · Answer 1 · 2022-11-14T12:40:57.463

real is a lossy, inexact floating-point type. It only uses 4 bytes for storage and may not store the presented numeric literals precisely to begin with. In addition, implementation details depend on your platform. Consider the chapter "Floating-Point Types" in the manual.

There is nothing wrong with either round() or cast(). For (more) exact results, use numeric.

Function audit

CREATE OR REPLACE FUNCTION test3()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   r record;
BEGIN
   FOR r IN 
      SELECT abs_km AS km
           , cast(abs_km AS numeric) AS km_cast
           , round(abs_km::numeric, 2) AS km_round
      FROM   gps_entry
   LOOP
      RAISE NOTICE 'km: % , km_cast: % , km_round: %'
                  , r.km, r.km_cast, r.km_round;
      INSERT INTO test (km, casting, rounding)
      VALUES (r.km, r.km_cast, r.km_round);
   END LOOP;    
END
$func$;

Of course, it would be more efficient to replace the loop with a single multi-row INSERT statement.

Do not quote the language name plpgsql. It's an identifier.

Makes no sense to round to 2 fractional digits after casting to numeric(16,2), which forcibly rounds already. Either / or ..

round(abs_km:: numeric(16,2), 2) as round
round(abs_km::numeric, 2) as round
abs_km::numeric(16,2) as round

How to round REAL type to NUMERIC?

1 Answers1

Function audit

Linked