CODEWITHSUNDEEP
sqlite
daneg
daneg

Reputation: 29

SQLite how to store decimal 0.00 and 1.10 instead of 0 and 1.1?

This is my SQLite query:

CREATE TABLE IF NOT EXISTS `accounts` (id INTEGER NOT NULL, price DECIMAL(10,2) default 0.00);", conn);

I want to avoid saving 0 instead of 0.00 or 1.1 instead of 1.10. Is it possible in SQLite?

Upvotes: 0

Views: 3417

Answers (2)

forpas
forpas

Reputation: 164099

There is no DECIMAL data type in SQLite as you can find here: Datatypes In SQLite Version 3.
By defining the column price as DECIMAL(10,2) you just define its affinity as NUMERIC, but this does not mean that the numeric values that you will store will have precision of 2 decimal places.
You can store anything in that column, even strings.

What you can do is format the numbers when you query the table with the function printf():

SELECT id, printf("%.2f", price) AS price FROM accounts

Or redefine the column as TEXT:

CREATE TABLE IF NOT EXISTS `accounts` (id INTEGER NOT NULL, price TEXT DEFAULT '0.00');

and store the values like:

INSERT INTO accounts(id, price) VALUES 
  (1, printf("%.2f", 1.1)),
  (2, printf("%.2f", 1000));

But when you want to aggregate or do any kind of numeric calculations like:

SELECT SUM(price) FROM accounts

or

SELECT 1.1 * price FROM accounts

you will still need to use printf() to format the results:

SELECT printf("%.2f", SUM(price)) FROM accounts

or

SELECT printf("%.2f", 1.1 * price) FROM accounts

Upvotes: 1

GMB
GMB

Reputation: 222492

You have a decimal datatype that can store up to two decimal digits.

When you insert data, you can ignore non-significant digits. The following commands are equivalent:

insert into accounts (id, price) values (1, 1.1);
insert into accounts (id, price) values (1, 1.10);

SQLite stores it in an internal format that you don’t need to worry about. The value can be formatted as needed when it is read in a select query.

Upvotes: 1

Related Questions