如何在PostgreSQL中全面发挥作用？ [英] How exactly work round function in postgresql?

问题描述

postgresql中的round函数实际上如何工作？
下面的查询演示了奇怪的行为

How round function in postgresql actually work? Query below demonstrate odd behavior

SQL演示

SQL DEMO

select
    val
    ,Round(x.val::NUMERIC) as NUMERIC_Round
    ,Round(x.val::DOUBLE PRECISION) as DOUBLE_Round
from
    generate_series(-10.5,10.5,0.5) as x(val)

当 Val 取'10 .5'，'6.5'，'4.5 '等的回合结果不同。

When Val take '10.5', '6.5', '4.5' etc. result of round is different.

文档表示
用于双精度：

Docs say that for double precision:

 round(dp or numeric)   (same as input) round to nearest integer

对于数值：

round(v numeric, s int) numeric round to s decimal places

这不能解释为什么'10 .5'，'6.5'，'4.5'的结果不同。
我在做什么错？
可能有一些详细的解释可以帮助理解舍入函数。

This does not explain why result for '10.5', '6.5', '4.5' are different. What am i doing wrong? May be some detailed explanation can help to understand round function.

推荐答案

文档中的此页面表明了不同数据类型之间舍入的区别：

This page in the documentation indicates the differences in rounding between different data types:

十进制和数字的类型是等效的。这两种类型都是SQL标准的一部分。

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

在对值进行舍入时，数字类型会将关系从零舍入，而（在大多数计算机上）实数和双精度类型将四舍五入为最接近的偶数。例如：

When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precision types round ties to the nearest even number. For example:

SELECT x,
    round(x::numeric) AS num_round,
    round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;

  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

有一些讨论在pgsql-hackers邮件列表上有关更改的信息此处。

There was some discussion on the pgsql-hackers mailing list about the changes here.

这篇关于如何在PostgreSQL中全面发挥作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！