DECFLOAT type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Represents base-10 floating-point numbers with 16 or 34 significant digits and no fixed scale.

Important

DECFLOAT is in Beta. A workspace admin must enable DECFLOAT data type in Previews.

Syntax

DECFLOAT [ ( p ) ]
  • p: The maximum number of significant decimal digits. Supported values are 16 and 34. The default is 34.

DECFLOAT does not accept a scale argument. Each value has its own exponent.

Limits

Property DECFLOAT(16) DECFLOAT(34)
Significant digits Up to 16 Up to 34
Smallest positive nonzero value 1E-398 1E-6176
Largest positive finite value (10^16 - 1) × 10^369 (10^34 - 1) × 10^6111

The same limits apply to negative numbers. Both types also support infinity and NaN.

Literals

Use CAST with a string or supported numeric value. Strings can also contain NaN, Inf, or Infinity.

Notes

DECFLOAT represents decimal values such as 0.1 exactly. Values that exceed the precision can round without an error, including with TRY_CAST. Cast decimal text directly. Converting through FLOAT or DOUBLE can lose precision.

String output preserves trailing zeros, so equal values such as 1.5 and 1.50 can produce different strings (+15E-1 and +150E-2). Casts from DECFLOAT to DECIMAL round half up, independently of DECFLOAT_ROUNDING_MODE.

  • Arithmetic preserves precision when both operands have the same DECFLOAT type. Mixed precisions or operations with other supported numeric types use DECFLOAT(34).
  • SUM and AVG return DECFLOAT(34). MIN and MAX preserve the input precision.
  • SQRT, ABS, and ROUND accept DECFLOAT directly.

DECFLOAT_ROUNDING_MODE controls rounding for casts to DECFLOAT, +, -, *, /, SQRT, SUM, and AVG. The default is ROUND_HALF_EVEN. Other supported modes are ROUND_HALF_UP, ROUND_CEILING, ROUND_FLOOR, and ROUND_DOWN. DIV removes the fractional part of the result, regardless of DECFLOAT_ROUNDING_MODE. ROUND, BROUND, and TRUNCATE use their own rounding rules.

> SET DECFLOAT_ROUNDING_MODE = ROUND_FLOOR;
> SELECT CURRENT_DECFLOAT_ROUNDING_MODE();
  ROUND_FLOOR

With the default rounding mode, overflow produces infinity and division by zero produces infinity or NaN. ANSI mode does not change these results. Supported TRY_* arithmetic functions preserve infinity and NaN rather than returning NULL. NaNs compare equal and sort after positive infinity. Positive and negative zero compare equal. Use ISNAN to detect NaN.

Examples

> SELECT CAST('1.5' AS DECFLOAT(34)) + CAST('2.5' AS DECFLOAT(34)) AS total;
  +40E-1

> SELECT CAST(CAST('1.23E+100' AS DECFLOAT(34)) AS STRING);
  +123E+98

> SELECT typeof(CAST('1' AS DECFLOAT(16)) + 1);
  decfloat(34)

Limitations

  • Remainder and bitwise operations are unsupported.
  • Functions that require DOUBLE inputs do not accept DECFLOAT directly. Convert the value to DOUBLE first (this can result in precision loss).
  • DECFLOAT is not compatible with deletion vectors. When creating a Delta table with DECFLOAT columns, set TBLPROPERTIES ('delta.enableDeletionVectors' = 'false'). For existing tables, drop the deletion-vector table feature before adding DECFLOAT columns.
  • Delta tables containing DECFLOAT cannot have Iceberg reads enabled.
  • Writing DECFLOAT values directly to Parquet files is unsupported.
  • DECFLOAT columns have no min/max statistics for data skipping. They cannot be partition columns, clustering keys, Z-order columns, or Bloom filter index columns.
  • Changing a column between DECFLOAT(16) and DECFLOAT(34) with ALTER COLUMN is unsupported.
  • Viimeksi päivitetty