postgresql PSQL 中 DECIMAL 和 NUMERIC 数据类型的区别

Difference between DECIMAL and NUMERIC datatype in PSQL

提问by mrg

what is the use of decimaland numericdatatype in postgreSQL. As per the reference the following is the explanation given to these datatypes.

postgreSQL中decimal和numeric数据类型的用途是什么。根据参考资料，以下是对这些数据类型的解释。

Decimal,numeric --> It is a user specified precision, exact and range up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

The above statement shows the description of decimaland numericdatatype. But, still I didn't understand what is the exact use of these data type and where it is used instead of other datatypes.

上面的语句显示了decimal和numeric数据类型的描述。但是，我仍然不明白这些数据类型的确切用途是什么以及在哪里使用它而不是其他数据类型。

Answer with example is much appreciated...

非常感谢示例的回答......

回答by a_horse_with_no_name

Right from the manual:

直接从手册：

The types decimaland numericare equivalent. Both types are part of the SQL standard.

类型decimal和numeric是等价的。这两种类型都是 SQL 标准的一部分。

As for the "why do I need to use it", this is also explained in the manual:

至于“为什么我需要使用它”，这在手册中也有说明：

The type numeric can store numbers with a very large number of digits and perform calculations exactly

类型 numeric 可以存储非常多位数的数字并精确执行计算

(Emphasis mine).

（强调我的）。

If you need numbers with decimals, use decimal(or numeric) if you need numbers without decimals, use integeror bigint. A typical use of decimalas a column type would be a "product price" column or an "interest rate". A typical use of an integer type would be e.g. a column that stores how manyproducts were ordered (assuming you can't order "half" a product).

如果您需要带小数的数字，请使用decimal(或numeric) 如果您需要没有小数的数字，请使用integer或bigint。decimal列类型的典型用途是“产品价格”列或“利率”。整数类型的典型用途是例如存储订购了多少产品的列（假设您不能订购“一半”产品）。

doubleand realare also types that can store decimal values, but they are approximatetypes. This means you don't necessarily retrieve the value you stored. For details please see: http://floating-point-gui.de/

double和real也是可以存储十进制值的类型，但它们是近似类型。这意味着您不一定要检索您存储的值。详情请见：http: //floating-point-gui.de/

回答by geoyws

Quoted straight from https://www.postgresql.org/message-id/[email protected]

直接引用自https://www.postgresql.org/message-id/[email protected]

There isn't any difference, in Postgres. There are two type names because the SQL standard requires us to accept both names. In a quick look in the standard it appears that the only difference is this:

     17)NUMERIC specifies the data type exact numeric, with the decimal
        precision and scale specified by the <precision> and <scale>.

     18)DECIMAL specifies the data type exact numeric, with the decimal
        scale specified by the <scale> and the implementation-defined
        decimal precision equal to or greater than the value of the
        specified <precision>.

ie, for DECIMAL the implementation is allowed to allow more digits than requested to the left of the decimal point. Postgres doesn't exercise that freedom so there's no difference between these types for us.

      regards, tom lane

在 Postgres 中没有任何区别。有两种类型名称，因为 SQL 标准要求我们接受这两种名称。快速浏览一下标准，似乎唯一的区别是：

     17)NUMERIC specifies the data type exact numeric, with the decimal
        precision and scale specified by the <precision> and <scale>.

     18)DECIMAL specifies the data type exact numeric, with the decimal
        scale specified by the <scale> and the implementation-defined
        decimal precision equal to or greater than the value of the
        specified <precision>.

即，对于 DECIMAL，允许实现允许比要求的小数点左侧更多的数字。Postgres 没有行使这种自由，所以这些类型对我们来说没有区别。

      regards, tom lane

回答by Rahul Tripathi

They are the synonym of each other and functionally same. The SQL:2003standard says:

它们是彼此的同义词，功能相同。在SQL：2003标准说：

21) NUMERIC specifies the data type
    exact numeric, with the decimal
    precision and scale specified by the
    <precision> and <scale>.

22) DECIMAL specifies the data type
    exact numeric, with the decimal scale
    specified by the <scale> and the
    implementation-defined decimal
    precision equal to or greater than the
    value of the specified <precision>.