REAL vs NUMERIC in SQLite

When working with SQLite, understanding the differences between data types is important for efficient database design and query performance. Two common data types often confused are REAL and NUMERIC. While both can store numeric values, they have distinct characteristics that impact how data is stored and processed. In this article, we will learn about what is REAL and NUMERIC along with the examples and also see the differences between them.

What is REAL Data Type?

The REAL storage class is used for numeric data with a decimal component. REAL values are stored as 8-byte floating-point numbers according to the IEEE 754 standard which means that they can represent a wide range of values including both very small and very large numbers.

When using the REAL data type it is important to be aware of precision issues. Due to the nature of floating-point representation, some values may not be stored exactly as entered. This can lead to small rounding errors, especially when performing calculations involving REAL values.

What is Numeric Data Type?

The numeric data type is a more general–purpose data type that can be used to store numeric values with varying precision. Unlike REAL, which stores floating–point numbers, numeric can store both integers and decimal numbers.

The numeric data type is specified using the syntax NUMERIC(precision, scale). The precision specifies the total number of digits that can be stored, while the scale specifies the number of digits to the right of the decimal point.

Example of REAL Datatype

Example 1: Storing a simple floating-point number in a SQLite database using the REAL data type.

CREATE TABLE example_real (
    id INTEGER PRIMARY KEY,
    value REAL
);

INSERT INTO example_real (value) VALUES (123.456);

SELECT * FROM example_real;

Output:

Explanation: In the above query, we creates a table example_real with columns id (as the primary key) and value (of type REAL), inserts a row with the value 123.456 into the table and finally retrieves all rows from the table example_real.

Example 2: Storing a very large floating-point number in a SQLite database using the REAL data type.

CREATE TABLE example_large_real (
    id INTEGER PRIMARY KEY,
    value REAL
);

INSERT INTO example_large_real (value) VALUES (12345678901234567890.123456789);

SELECT * FROM example_large_real;

Output:

Explanation: In the above query, we creates a table example_large_real with columns id (as the primary key) and value (of type REAL), inserts a row with the value 12345678901234567890.123456789 into the table and finally retrieves all rows from the table example_large_real.

Examples of NUMERIC Data Type

Example 1: Storing an integer value with specific precision and scale using the NUMERIC data type in SQLite.

CREATE TABLE example_numeric (
    id INTEGER PRIMARY KEY,
    value NUMERIC(5,2)
);

INSERT INTO example_numeric (value) VALUES (123.45);

SELECT * FROM example_numeric;

Output:

Explanation: In the above query, we creates a table example_numeric with columns id (as the primary key) and value (of type NUMERIC with a precision of 5 digits and 2 decimal places) inserts a row with the value 123.45 into the table and finally retrieves all rows from the table example_numeric.

Example 2: Storing a decimal value with specific precision and scale using the NUMERIC data type in SQLite.

CREATE TABLE example_decimal (
    id INTEGER PRIMARY KEY,
    value NUMERIC(8,4)
);

INSERT INTO example_decimal (value) VALUES (12345.6789);

SELECT * FROM example_decimal;

Output:

Explanation: In the above query, we creates a table example_decimal with columns id (as the primary key) and value (of type NUMERIC with a precision of 8 digits and 4 decimal places), inserts a row with the value 12345.6789 into the table, and finally retrieves all rows from the table example_decimal.

Difference Between the REAL and NUMERIC in SQLite

Feature	REAL	NUMERIC
Storage Size	It shows the 8 bytes	It show the in form of Variable
Precision	Up to 15 decimal places	Up to 18 decimal places
Storage Strategy	Floating-point	Exact numeric
Use Cases	Scientific or floating-point calculations	Financial or precise calculations

CONCLUSION

Overall, the choice between REAL and numeric in SQLite depends on the specific requirements of your application. If you need to store floating-point numbers with a wide range of values and precision is not important then REAL may be sufficient. However, if you require exact precision or need to store decimal numbers with specific precision and scale, the numeric data type offers more control and flexibility.