Difference between DECIMAL and NUMERIC datatype in PSQL

postgresql rdbms sqldatatypes
mrg picture mrg · Nov 16, 2015 · Viewed 59.4k times · Source

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

Answer

a_horse_with_no_name picture a_horse_with_no_name · Nov 16, 2015

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/

Related questions

Differences between ROWTYPE, TYPE, and RECORD in postgresql?

What is the use of the following declarations, and where will we use these types of declarations? myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;

Read More
IN vs ANY operator in PostgreSQL

What is the difference between IN and ANY operator in PostgreSQL? The working mechanism of both seems to be the same. Can anyone explain this with an example?

Read More
SQL query for point-in-polygon using PostgreSQL

I have the following simple table: CREATE TABLE tbl_test ( id serial NOT NULL, poly polygon NOT NULL ) WITH (OIDS=FALSE); I then try to insert a row with a polygon: insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))')) And …

Read More