In a column declared DECIMAL(8,2), what do the 8 and the 2 mean, and what happens to 1234.5678?

middleshould knowfreq 52%

answer

Two numbers, two different jobs
One counts everything, the other only one side
Digits before the point are what is left over
One kind of overflow is silent, the other is not
Too many integer digits cannot be shortened

basics

~20 s

Precision 8 is the total count of significant decimal digits; scale 2 is how many sit right of the point, leaving six integer digits. 1234.5678 is rounded or truncated to two decimals and stored; only too many integer digits raise an error.

solid answer

~40 s

In DECIMAL(p, s), p is the precision — the total number of significant decimal digits the column can hold — and s is the scale, how many of those digits fall to the right of the decimal point. DECIMAL(8,2) therefore allows six digits before the point and two after, a range of -999999.99 to 999999.99. The two limits behave very differently on assignment. Excess scale is absorbed: 1234.5678 is reduced to two decimals and stored as 1234.57, with rounding versus truncation left implementation-defined by the standard (PostgreSQL rounds). Excess integer digits cannot be absorbed, so 12345678.00 raises a numeric-value-out-of-range error rather than being clipped. That asymmetry is the point of the question: scale loss is silent, magnitude overflow is loud. Omitting the scale, as in DECIMAL(8), means scale zero — an eight-digit integer.

In `DECIMAL(p, s)`, `p` is the **precision** — the total number of significant decimal digits the column can hold — and `s` is the **scale**, how many of those digits fall to the right of the decimal point. `DECIMAL(8,2)` therefore allows six digits before the point and two after, a range of -999999.99 to 999999.99. The two limits behave very differently on assignment. Excess *scale* is absorbed: 1234.5678 is reduced to two decimals and stored as 1234.57, with rounding versus truncation left implementation-defined by the standard (PostgreSQL rounds). Excess *integer* digits cannot be absorbed, so 12345678.00 raises a numeric-value-out-of-range error rather than being clipped. That asymmetry is the point of the question: scale loss is silent, magnitude overflow is loud. Omitting the scale, as in `DECIMAL(8)`, means scale zero — an eight-digit integer.

code

sql · 9 lines

sql

CREATE TABLE invoice_line (
  qty        DECIMAL(9,3)  NOT NULL,  -- 6 integer digits, 3 decimals
  unit_price DECIMAL(12,4) NOT NULL,  -- wider scale: prices divide later
  line_total DECIMAL(12,2) NOT NULL   -- rounded once, at 2 decimals
);

-- Round at a defined step rather than accepting the engine's expression scale
INSERT INTO invoice_line (qty, unit_price, line_total)
VALUES (3.500, 19.9900, CAST(3.500 * 19.9900 AS DECIMAL(12,2)));  -- 69.97

go deeper

for a junior

Recall the two arguments correctly: precision is the total digit count, scale is the digits after the point, and the integer part gets whatever is left. DECIMAL(8,2) means six digits before the point, two after.

for a middle

Explain the asymmetry — extra decimal places are absorbed by rounding or truncation with no error, extra integer digits raise an out-of-range error — and note that which of rounding or truncation applies is implementation-defined.

for a senior

Show how you size these columns for a real domain, including a wider scale for unit prices and rates than for stored totals, and where you place an explicit CAST so rounding happens at a defined step rather than wherever the engine chose.

for a principal

Own the rule for how numeric shapes are chosen and reviewed across a system, so that the same quantity has the same declared scale everywhere and rounding points are agreed rather than emerging from each engine's expression-typing rules.

Reading the declaration

DECIMAL(p, s) declares an exact decimal number with:

precision p — the total number of significant decimal digits, counting both sides of the decimal point;
scale s — how many of those digits are to the right of the point, with 0 <= s <= p.

The integer part therefore gets p - s digits, and the representable range is from -(10^(p-s) - 10^-s) to +(10^(p-s) - 10^-s). For DECIMAL(8,2): six integer digits, two fractional, so -999999.99 through 999999.99, in steps of 0.01.

Two shorthand forms exist. DECIMAL(8) means DECIMAL(8,0) — scale zero, an eight-digit integer. A bare DECIMAL takes implementation-defined defaults, and in some engines it is unconstrained rather than defaulted; never rely on it in a schema you care about.

The asymmetry on assignment

This is what interviewers are testing. The two limits fail in opposite ways.

Excess scale is absorbed. Assigning 1234.5678 to DECIMAL(8,2) fits in magnitude — four integer digits, well inside six — so the engine reduces the fractional part to two digits and stores 1234.57 (or 1234.56 if it truncates). The SQL standard leaves the choice between rounding and truncation implementation-defined; PostgreSQL rounds, and you should check rather than assume for any other engine. Either way, no error is raised, and the discarded digits are gone.

Excess magnitude is rejected. Assigning 12345678.00 to the same column needs eight integer digits where only six exist. There is no defensible way to shorten a number's integer part, so the engine raises an error — numeric value out of range, or the engine's spelling of it. Statements fail rather than storing a wrong figure.

The practical lesson: silent precision loss comes from scale, hard failures come from precision. When you size a monetary or quantity column, choose the scale for the smallest unit you must represent exactly, and the precision for the largest magnitude you must ever hold, plus headroom.

NUMERIC versus DECIMAL

The standard defines both, and they differ in one nuance: NUMERIC(p, s) must provide exactly the requested precision, while DECIMAL(p, s) must provide at least it, letting an implementation give more. In practice the mainstream engines treat the two spellings as synonyms, so pick one for the whole schema and stay consistent. Nothing else about the semantics differs.

Arithmetic on decimals

Exact decimal arithmetic gives exact results — with a caveat about the type of the result. Adding two DECIMAL(8,2) values needs one more integer digit for a possible carry; multiplying them needs a scale of four to be exact. How the engine derives the precision and scale of an expression's result is implementation-defined, and division is where it bites: dividing two decimals generally cannot be exact, so the engine picks a result scale, and if that scale is small you lose digits you expected to keep. When a computation chain matters — a unit price times quantity, then a tax rate, then a discount — apply an explicit CAST to the precision and scale you want at the point where rounding is supposed to happen, rather than discovering the engine's default at the end.

Sizing in practice

Money in ordinary currencies: scale 2 covers cents; precision from the largest realistic amount, e.g. DECIMAL(12,2).
Unit prices and rates: often need scale 4 or more, because a per-unit figure is divided later and rounding at two decimals too early distorts the total.
Physical quantities counted in fractions: choose the scale from the smallest unit the business recognises, not from what looks tidy.
Percentages and rates: decide whether you store 0.075 or 7.5, and encode that in both the scale and the column name.

Checking what you actually stored

After a load, verifying is easy: compare the loaded column with the raw source, or count rows where the value differs from itself re-rounded. Once the digits are gone they cannot be recovered from the column, so catch scale loss during a migration, not months later when a total fails to reconcile.

## Reading the declaration `DECIMAL(p, s)` declares an exact decimal number with: - **precision `p`** — the total number of significant decimal digits, counting both sides of the decimal point; - **scale `s`** — how many of those digits are to the right of the point, with `0 <= s <= p`. The integer part therefore gets `p - s` digits, and the representable range is from `-(10^(p-s) - 10^-s)` to `+(10^(p-s) - 10^-s)`. For `DECIMAL(8,2)`: six integer digits, two fractional, so -999999.99 through 999999.99, in steps of 0.01. Two shorthand forms exist. `DECIMAL(8)` means `DECIMAL(8,0)` — scale zero, an eight-digit integer. A bare `DECIMAL` takes implementation-defined defaults, and in some engines it is unconstrained rather than defaulted; never rely on it in a schema you care about. ## The asymmetry on assignment This is what interviewers are testing. The two limits fail in opposite ways. **Excess scale is absorbed.** Assigning 1234.5678 to `DECIMAL(8,2)` fits in magnitude — four integer digits, well inside six — so the engine reduces the fractional part to two digits and stores 1234.57 (or 1234.56 if it truncates). The SQL standard leaves the choice between rounding and truncation implementation-defined; PostgreSQL rounds, and you should check rather than assume for any other engine. Either way, **no error is raised**, and the discarded digits are gone. **Excess magnitude is rejected.** Assigning 12345678.00 to the same column needs eight integer digits where only six exist. There is no defensible way to shorten a number's integer part, so the engine raises an error — `numeric value out of range`, or the engine's spelling of it. Statements fail rather than storing a wrong figure. The practical lesson: silent precision loss comes from scale, hard failures come from precision. When you size a monetary or quantity column, choose the scale for the smallest unit you must represent exactly, and the precision for the largest magnitude you must ever hold, plus headroom. ## NUMERIC versus DECIMAL The standard defines both, and they differ in one nuance: `NUMERIC(p, s)` must provide *exactly* the requested precision, while `DECIMAL(p, s)` must provide *at least* it, letting an implementation give more. In practice the mainstream engines treat the two spellings as synonyms, so pick one for the whole schema and stay consistent. Nothing else about the semantics differs. ## Arithmetic on decimals Exact decimal arithmetic gives exact results — with a caveat about the *type* of the result. Adding two `DECIMAL(8,2)` values needs one more integer digit for a possible carry; multiplying them needs a scale of four to be exact. How the engine derives the precision and scale of an expression's result is implementation-defined, and division is where it bites: dividing two decimals generally cannot be exact, so the engine picks a result scale, and if that scale is small you lose digits you expected to keep. When a computation chain matters — a unit price times quantity, then a tax rate, then a discount — apply an explicit `CAST` to the precision and scale you want at the point where rounding is supposed to happen, rather than discovering the engine's default at the end. ## Sizing in practice - **Money in ordinary currencies:** scale 2 covers cents; precision from the largest realistic amount, e.g. `DECIMAL(12,2)`. - **Unit prices and rates:** often need scale 4 or more, because a per-unit figure is divided later and rounding at two decimals too early distorts the total. - **Physical quantities counted in fractions:** choose the scale from the smallest unit the business recognises, not from what looks tidy. - **Percentages and rates:** decide whether you store 0.075 or 7.5, and encode that in both the scale and the column name. ## Checking what you actually stored After a load, verifying is easy: compare the loaded column with the raw source, or count rows where the value differs from itself re-rounded. Once the digits are gone they cannot be recovered from the column, so catch scale loss during a migration, not months later when a total fails to reconcile.

What does DECIMAL(8) alone mean, and what does a bare DECIMAL with no arguments mean?
`DECIMAL(8)` means scale zero — an eight-digit integer, no fractional part. A bare `DECIMAL` falls back to implementation-defined defaults, and some engines treat it as effectively unconstrained rather than applying a default precision. Since the whole value of the type is that the schema declares the exact shape of the number, always write both arguments explicitly.
Two DECIMAL(8,2) values are multiplied. What precision and scale does the result have?
Exactness requires a scale of four and up to twelve integer digits, but the precision and scale an engine assigns to an expression result are implementation-defined, and division is worse because the exact result may not terminate. When rounding matters, CAST the expression to the precision and scale you intend at the point in the chain where rounding is meant to happen.

saying these in an interview costs you the question

Thinks precision counts only the digits before the decimal point
Expects an error when a value has too many decimal places
Assumes every engine rounds rather than truncates excess scale
Believes DECIMAL(8) means eight decimal places
Assumes arithmetic on decimals keeps the operands' scale

In a column declared DECIMAL(8,2), what do the 8 and the 2 mean, and what happens to 1234.5678?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Reading the declaration

The asymmetry on assignment

NUMERIC versus DECIMAL

Arithmetic on decimals

Sizing in practice

Checking what you actually stored

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics4 topics

Reading the declaration

The asymmetry on assignment

NUMERIC versus DECIMAL

Arithmetic on decimals

Sizing in practice

Checking what you actually stored

answer

go deeper

context