SQL Reference - NUMERIC

Example

CREATE TABLE Prices (
    ID INT PRIMARY KEY,
    Price NUMERIC(5, 2)
);

INSERT INTO Prices (ID, Price)
VALUES (1, 123.45), (2, 67.89);

SELECT * FROM Prices;

Output

+----+-------+
| ID | Price |
+----+-------+
| 1  | 123.45|
| 2  | 67.89 |
+----+-------+

Explanation

The NUMERIC type is used to store numbers with a large number of digits. Its precision is perfect, and is often used for financial or scientific calculations. In this case, a NUMERIC(5, 2) number could range, for example, from -999.99 to 999.99. The first parameter (total digits) is 5 and the second (digits after the decimal point) is 2.

Example

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    salary NUMERIC(10,2)
);

INSERT INTO employees (salary)
VALUES (3542.45), (7001.22), (15000.99);

SELECT * FROM employees;

Output

 id |  salary
----|-----------
  1 |  3542.45
  2 |  7001.22
  3 | 15000.99
(3 rows)

Explanation

In the example above, the “employees” table was created with a NUMERIC field “salary”. This NUMERIC field is defined with a precision of 10 and a scale of 2, meaning it can store a number of up to 10 digits in total, with 2 numbers reserved for values after the decimal point. Three rows of data were then inserted into the table. The SELECT statement retrieved all the records from the “employees” table, displaying each employee’s salary.

Example

CREATE TABLE Test (
    ID INT IDENTITY,
    Data NUMERIC(5,2)
);

INSERT INTO Test (Data)
VALUES (123.45);

SELECT * FROM Test;

Output

ID     Data
1    123.45

Explanation

In this example, a NUMERIC column Data is defined in the Test table with precision 5 and scale 2. One row is inserted with the value of 123.45 for Data. The SELECT * FROM Test; query is used to return all rows from the Test table, which displays the inserted record with Data value 123.45.

Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Salary NUMERIC(10,2)
);

INSERT INTO Employees (ID, Salary)
VALUES (1, 1200.45);

SELECT * FROM Employees;

Output

ID	Salary
1	1200.45

Explanation

In the example, a table named Employees has been created, where the Salary field is defined as NUMERIC(10,2). This indicates that the Salary column can store numbers with up to 10 digits, 2 of which can be after the decimal point. An employee record has been inserted with a salary of 1200.45. This salary is within the defined limits of the numeric field specification.

Example

CREATE TABLE Employees (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Salary NUMERIC(10, 2)
);

INSERT INTO Employees (ID, Name, Salary)
VALUES (1, 'John Doe', 45000.25);

Output

SELECT * FROM Employees;

ID	Name	Salary
1	John Doe	45000.25

Explanation

In this example, a table named Employees is created with the ID, Name, and Salary columns. The Salary column is declared as NUMERIC(10, 2), implying a maximum of 10 digits in total, with 2 digits allowed after the decimal point. An entry is inserted into the Employees table, specifically, an employee named ‘John Doe’ with a salary of 45000.25.