Which Datatype should we use to store rupees and paisa?-CodePudding

I am building an E-commerce platform and, while creating an invoice and saving to data base, it is not accurate; there is difference of 3-4 rupees in the final amount.

Which Datatype should we use to store rupees and paisa to avoid any inaccuracy?

CodePudding user response：

It is always suggested to use Decimal datatype for storing monetary values. In PostgreSQL, Numeric datatype is equivalent to Decimal and hence it can be used.

Consider below given example for better understanding:-

CREATE TABLE IF NOT EXISTS products (
    id serial PRIMARY KEY,
    name VARCHAR NOT NULL,
    price NUMERIC (5, 2)     // the second parameter is the precision for decimal values
);

INSERT INTO products (name, price) VALUES
    ('Phone', 100.21), 
    ('Tablet', 300.49);

CodePudding user response：

I would suggest normalizing your amount and currency into separate columns.

You might have to write some script in order to convert alphabetically mentioned amounts into numeric.

'amount' : double precision
'currency' : varchar(128)