Storing prices in SQLite, what data-type to use

datatypesmoneysqlite

I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store prices numerically so they sort correctly?

Best Answer

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.

In my experience it's standard practice.

Related Solutions

PostgreSQL data type text vs varchar without length

There is no difference between the two data types. They use the exact same storage and the same operators.

MySQL Data Types – Best Type for Numbering in Harmonized System

"The harmonized system uses a numbering" - they are not numbers, they are codes. They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit. You have to go with textual types. Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text? Just think of them this way, but with the A, B and C removed. Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.

Best Answer

Related Solutions

PostgreSQL data type text vs varchar without length

MySQL Data Types – Best Type for Numbering in Harmonized System

Related Question