如何将REAL类型四舍五入为NUMERIC? [英] How to round REAL type to NUMERIC?

问题描述

我的表具有real列类型，并带有示例值:

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;

此处输出:

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

具有列NUMERIC(19,2)的数据库表:

km        casting   rounding
0.12      0.12      0.12

123456.00 123456.00 123456.00

为什么cast和round函数对值123456.12不起作用?

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

推荐答案

real 是有损，不精确的浮点类型.它仅使用4个字节进行存储，不能完全以开头存储所提供的数字文字.此外，实施细节取决于您的平台.考虑手册中的浮点类型"一章.

real is a lossy, inexact floating-point type. It only uses 4 bytes for storage and cannot 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.

round()或cast()都没有问题.为了获得准确的结果，您必须先使用numeric.

There is nothing wrong with either round() or cast(). For exact results, you'd have to use numeric to begin with.

CREATE OR REPLACE FUNCTION test3()
  RETURNS void 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$ LANGUAGE plpgsql;

• 请勿引用语言名称plpgsql.这是一个标识符.

• 在将强制转换为numeric(16,2)后将其舍入到小数位数没有任何意义，已经被强制舍入. -或..

  • 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

  最后，您需要升级到当前版本. Postgres 8.3已达到EOL且不受支持.

  Finally, you need to upgrade to a current version. Postgres 8.3 has reached EOL and is unsupported.

  这篇关于如何将REAL类型四舍五入为NUMERIC?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！