Difference between DECIMAL and NUMERIC datatypes in PostgreSQL

Right from the manual:

The types decimal and numeric are 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/

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

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

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>.

There are two points of view - are these data types the same in the ANSI SQL standard, and are these data types the same in PostgreSQL.

For PostgreSQL the answer is yes, they are exactly the same (https://www.postgresql.org/docs/current/datatype-numeric.html):

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

They are not exactly equivalent in the ANSI SQL standard. NUMERIC requires the database to store the floating point number exactly as specified, with this exact precision and scale. DECIMAL is required to store the floating point with precision at least as requested, but with scale equal to one than requested.

In most modern RDBMs, DECIMAL and NUMERIC are equivalent.