Menu
what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes.
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 decimal and numeric datatype. But, still I didn't understand what is the exact use of these data type and where it is used instead of other datatypes.
Answer with example is much appreciated...
305
There is a small difference between NUMERIC(p,s) and DECIMAL(p,s) SQL numeric data type. NUMERIC determines the exact precision and scale. DECIMAL specifies only the exact scale; the precision is equal or greater than what is specified by the coder.
In PostgreSQL, the Numeric data type is used to store the numbers with various significant numbers of digits. In other words, we can say that the PostgreSQL Numeric data type is used to specify the numeric data into the table, which needs quantities or monetary amounts where the precision is required.
The decimal data type is an exact numeric data type defined by its precision (total number of digits) and scale (number of digits to the right of the decimal point).
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly. Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type range can be represented exactly with precision and scale. You can use decimal for money saving.
Right from the manual:
The types
decimalandnumericare equivalent. Both types are part of the SQL standard.
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
(Emphasis mine).
If you need numbers with decimals, use decimal (or numeric) if you need numbers without decimals, use integer or bigint. A typical use of decimal as 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 many products were ordered (assuming you can't order "half" a product).
double and real are also types that can store decimal values, but they are approximate types. This means you don't necessarily retrieve the value you stored. For details please see: http://floating-point-gui.de/
178
Quoted straight from 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
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
