How to Insert Double and Float Values in SQLite?

SQLite, a lightweight and server-less relational database management system, provides powerful features for handling various data types, including double and float values.

In this article, we will explore various methods along with examples for inserting double and float values into SQLite tables, ensuring data integrity and precision.

How to Insert Double and Float Values?

When working with SQLite, it's important to understand how to insert double and float values correctly. Unlike other RDBMS, SQLite does not have any different data types for double and float. Below are the methods through which we can effectively insert double and float values in tables of SQLite.

1. Using REAL Storage class
   
2. Using the TEXT Storage Class

1. Using REAL Storage Class

We can store those values with the REAL storage class. REAL storage class is used to store floating point numbers. SQLite uses 8 bytes (64 bits) to store floating-point numbers. Therefore, we can store both types i.e. single precision and double precision value.

In this, we will see how we can store double and float values in our table in SQLite. We will first create a table namely 'geeksforgeeks' with (id, name, contest_score, and total_score ) as columns.

Query:

--table creation
CREATE TABLE geeksforgeeks (
    id INTEGER,
    name TEXT,
    contest_score REAL,
    total_score REAL
);

Explanation: We have created a table 'geeksforgeeks' and set contest_score and total_score as REAL. In those columns, we are going to store float and double values. After executing the above command, we can clearly see a table has been created in our database.

As we have done with creating a table, now lets insert some values and display it.

Query:

--Inserting values in our table 
INSERT INTO geeksforgeeks(id, name, contest_score ,total_score)
VALUES(01,'Vishu', 22.5 , 505.623523); 
INSERT INTO geeksforgeeks(id, name, contest_score ,total_score)
VALUES(02,'Neeraj', 20.5 , 501.2237562); 
INSERT INTO geeksforgeeks(id, name, contest_score ,total_score)
VALUES(03,'Aayush', 22.3 , 495.3534532); 
INSERT INTO geeksforgeeks(id, name, contest_score ,total_score)
VALUES(04,'Vivek', 18.2 , 491.10545610123); 

--displaying values of our table
SELECT * FROM geeksforgeeks;

Output:

Explanation: In the above image, we can clearly observe that float and double values are inserted in contest_score and toal_score columns. We can clearly see that both the columns are created with REAL storage class. Therefore, it can accommodate both values without losing any precision.

2. Using the TEXT Storage Class

We can also use the TEXT storage class to store double and float values. But it cannot be considered as an ideal case. Using the TEXT storage class for floating-point numbers can lead to a loss of precision. It is not recommended due to potential loss of precision. However, if we must store these values as text, ensure proper conversion to avoid data loss.

Query:

CREATE TABLE example_table ( id INTEGER PRIMARY KEY, value TEXT ); 

INSERT INTO example_table (id, value) VALUES (1, '123.456');

Output:

Explanation: In this example, we create a table with an id column as INTEGER PRIMARY KEY and a value column as TEXT. We then insert a double value (123.456) as a string into the table.

Conclusion

Overall, inserting a double or a float value in a table is basically done with the help of REAL storage class. Unlike other RDBMS, SQLite do not have any separate data types of float and double types of values. We have to use REAL storage class so that we will not loose precision.

Using other class like INTEGER or TEXT will result in the loss of precision of values. The most optimal way to keep our precision is to choose REAL storage class. Now you have a good understanding of inserting float and double values in a table in SQLite. Now you can write queries related to it with ease.