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

``````> CREATE TABLE decimals (a DECIMAL PRIMARY KEY, b DECIMAL(10,5), c NUMERIC);

> SHOW COLUMNS FROM decimals;
``````
``````+-------+---------------+-------+---------+
| Field |     Type      | Null  | Default |
+-------+---------------+-------+---------+
| a     | DECIMAL       | false | NULL    |
| b     | DECIMAL(10,5) | true  | NULL    |
| c     | DECIMAL       | true  | NULL    |
+-------+---------------+-------+---------+
``````
``````> INSERT INTO decimals VALUES (1.01234567890123456789, 1.01234567890123456789, 1.01234567890123456789);

> 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 & 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` ––