Understanding how SQL stores numbers with exactness is crucial when you want to avoid rounding errors or represent monetary values accurately. Two important concepts here are precision and scale, which are used with the DECIMAL and NUMERIC data types. Imagine a digital scoreboard: precision is the total number of digits it can show, while scale is how many of those digits appear after the decimal point. For example, if you have a scoreboard with eight digits, and three are after the decimal, you could display numbers like 12345.678 or 12.345.

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(8,3)
);

The choice of precision and scale directly impacts both how much storage is used and how accurate your data remains. In the previous code sample, if you try to insert a number with more than five digits before the decimal or more than three digits after, SQL will either round the value or reject it, depending on the database system. This ensures that values like 123456.789 (which has six digits before the decimal) are not stored, preventing accidental data errors. At the same time, using a smaller scale for monetary values (like two decimal places for dollars and cents) helps you avoid issues like rounding errors that can occur with floating point types.

-- This value fits: 12345.678 (5 digits before, 3 after)
INSERT INTO transactions (amount) VALUES (12345.678);

-- This value fits: 1.234 (1 digit before, 3 after)
INSERT INTO transactions (amount) VALUES (1.234);

-- This value exceeds precision: 1234567.890 (7 digits before, 3 after)
-- This will fail because total digits (10) exceed precision (8)
INSERT INTO transactions (amount) VALUES (1234567.890);

-- This value exceeds scale: 12.3456 (4 digits after decimal)
-- Most databases will round to 12.346 or throw an error
INSERT INTO transactions (amount) VALUES (12.3456);