The `DECIMAL` data type stores exact, fixed-point numbers. This type is used when it is important to preserve exact precision, for example, with monetary data.

## Aliases

In CockroachDB, the following are aliases for `DECIMAL`:

• `DEC`
• `NUMERIC`

## Precision and scale

To limit a decimal column, use `DECIMAL(precision, scale)`, where `precision` is the maximum count of digits both to the left and right of the decimal point and `scale` is the exact count of digits to the right of the decimal point. The `precision` must not be smaller than the `scale`. Also note that using `DECIMAL(precision)` is equivalent to `DECIMAL(precision, 0)`.

When inserting a decimal value:

• If digits to the right of the decimal point exceed the column's `scale`, CockroachDB rounds to the scale.
• If digits to the right of the decimal point are fewer than the column's `scale`, CockroachDB pads to the scale with `0`s.
• If digits to the left and right of the decimal point exceed the column's `precision`, CockroachDB gives an error.
• If the column's `precision` and `scale` are identical, the inserted value must round to less than 1.

## Syntax

A constant value of type `DECIMAL` can be entered as a numeric literal. For example: `1.414` or `-1234`.

The special IEEE754 values for positive infinity, negative infinity and NaN (Not-a-Number) cannot be entered using numeric literals directly and must be converted using an interpreted literal or an explicit conversion from a string literal instead.

The following values are recognized:

Syntax Value
`inf`, `infinity`, `+inf`, `+infinity` +∞
`-inf`, `-infinity` -∞
`nan` NaN (Not-a-Number)

For example:

• `DECIMAL '+Inf'`
• `'-Inf'::DECIMAL`
• `CAST('NaN' AS DECIMAL)`

## Size

The size of a `DECIMAL` value is variable, starting at 9 bytes. It's recommended to keep values under 64 kilobytes to ensure performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.

## Examples

copy
``````> CREATE TABLE decimals (a DECIMAL PRIMARY KEY, b DECIMAL(10,5), c NUMERIC);
``````
copy
``````> SHOW COLUMNS FROM decimals;
``````
``````+-------------+---------------+-------------+----------------+-----------------------+-------------+
| column_name |   data_type   | is_nullable | column_default | generation_expression |   indices   |
+-------------+---------------+-------------+----------------+-----------------------+-------------+
| a           | DECIMAL       |    false    | NULL           |                       | {"primary"} |
| b           | DECIMAL(10,5) |    true     | NULL           |                       | {}          |
| c           | DECIMAL       |    true     | NULL           |                       | {}          |
+-------------+---------------+-------------+----------------+-----------------------+-------------+
(3 rows)
``````
copy
``````> INSERT INTO decimals VALUES (1.01234567890123456789, 1.01234567890123456789, 1.01234567890123456789);
``````
copy
``````> SELECT * FROM decimals;
``````
``````+------------------------+---------+-----------------------+
|           a            |    b    |         c             |
+------------------------+---------+-----------------------+
| 1.01234567890123456789 | 1.01235 | 1.0123456789012346789 |
+------------------------+---------+-----------------------+
# The value in "a" matches what was inserted exactly.
# The value in "b" has been rounded to the column's scale.
# The value in "c" is handled like "a" because NUMERIC is an alias.
``````

## Supported casting and conversion

`DECIMAL` values can be cast to any of the following data types:

Type Details
`INT` Truncates decimal precision
`FLOAT` Loses precision and may round up to +/- infinity if the value is too large in magnitude, or to +/-0 if the value is too small in magnitude
`BOOL` 0 converts to `false`; all other values convert to `true`
`STRING` ––