Aller au contenu principal
Dernière mise à jour le

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.

Beta

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

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.

SQL
> 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​

SQL
> 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.
Sur cette page