Introduction to SQLite NUMERIC Data Type

SQLite is a lightweight relational database management system widely used in mobile devices and embedded systems. The NUMERIC data type is supported in SQLite, which is used to store numbers with variable precision.

Syntax

In SQLite, the NUMERIC data type is used to store numbers with variable precision, and its syntax is as follows:

NUMERIC[(M[, D])]

Where M represents the maximum precision, and D represents the number of digits after the decimal point. If D is omitted, it means the number can have any number of decimal places. If both M and D are omitted, it means the number can have any precision and any number of decimal places.

Use Cases

The NUMERIC data type is commonly used to store numbers that require high precision calculations, such as currency amounts, measurements, scientific data, etc. Compared to other numeric types, the NUMERIC data type provides higher precision and a larger range, making it very useful in scenarios that require accurate calculations.

Examples

Here are two examples of using the NUMERIC data type:

Example 1

Suppose there is a table for storing product prices, which includes the following fields:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

In this table, the price field is used to store the price of products with a precision of 10 digits and 2 decimal places. Here are some example data:

INSERT INTO products (name, price) VALUES
    ('Apple', 1.99),
    ('Banana', 0.99),
    ('Orange', 2.49);

You can use the following query to get the product prices:

SELECT name, price FROM products;

The query result would be:

Example 2

Suppose there is a table for storing scientific data, which includes the following fields:

CREATE TABLE science_data (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    value NUMERIC
);

In this table, the value field is used to store scientific data with arbitrary precision and decimal places. Here are some example data:

INSERT INTO science_data (name, value) VALUES
    ('Speed of light', 299792458),
    ('Gravitational constant', 6.67430e-11),
    ('Avogadro constant', 6.02214076e23);

You can use the following query to get the scientific data:

SELECT name, value FROM science_data;

The query result would be:

Conclusion

The NUMERIC data type is a very useful data type in SQLite, providing the ability to store numbers with high precision and variable precision, suitable for scenarios that require accurate calculations. Using the NUMERIC data type ensures the accuracy and reliability of data, especially for storing currency amounts, measurements, scientific data, etc. In practical development, developers can choose the appropriate data type to store data according to their requirements to ensure the correctness and reliability of their applications.