Numeric Types

Synopsis

YSQL support integers, floating-point numbers, and fixed-point numbers of different value ranges and precisions.

Data typeDescriptionMinMax
BIGINT8 bytes-9,223,372,036,854,775,8089,223,372,036,854,775,807
DECExact 64-bit fixed point numbervariablevariable
DECIMALExact 64-bit fixed point numbervariablevariable
DOUBLE PRECISIONInexact 64-bit floating point number15-digit precision15-digit precision
FLOATInexact 64-bit floating point numbervariablevariable
INTEGER4-byte integer-2,147,483,6482,147,483,647
INT4-byte interger-2,147,483,6482,147,483,647
NUMERICExact 64-bit fixed point numbervariablevariable
REALInexact 32-bit floating point number6-digit precision6-digit precision
SMALLINT2-byte integer-32,76832,767

Integers

The following keywords are used to specify a column of type integer for different constraints, including its value ranges.

  1. type_specification ::= SMALLINT | INT | INTEGER | BIGINT
  2. integer_literal ::= [ + | - ] digit [ { digit | , } ... ]
  • Columns of type SMALLINT, INT, INTEGER, or BIGINT can be part of the PRIMARY KEY.
  • Values of different integer data types are comparable and convertible to one another.
  • Values of integer data types are convertible but not comparable to floating point number.
  • Currently, values of floating point data types are not convertible to integers. This restriction will be removed in the near future.

Floating-point numbers

The following keywords are used to specify a column of floating-point types for different constraints including its value ranges.

  1. type_specification ::= { FLOAT | DOUBLE PRECISION | REAL }
  2. floating_point_literal ::= non_integer_fixed_point_literal | "NaN" | "Infinity" | "-Infinity"
  • Columns of type REAL, DOUBLE PRECISION, and FLOAT can be part of the PRIMARY KEY.
  • Values of different floating-point and fixed-point data types are comparable and convertible to one another.
  • Conversion from floating-point types into DECIMAL will raise an error for the special values NaN, Infinity, and -Infinity.
  • The ordering for special floating-point values is defined as (in ascending order): -Infinity, all negative values in order, all positive values in order, Infinity, and NaN.
  • Values of non-integer numeric data types are neither comparable nor convertible to integer although integers are convertible to them. This restriction will be removed.

Fixed-point numbers

The following keywords are used to specify a column of floating-point types for different constraints including its value ranges.

  1. type_specification ::= { DEC | DECIMAL | NUMERIC }
  2. fixed_point_literal ::= [ + | - ] { digit [ digit ...] '.' [ digit ...] | '.' digit [ digit ...] }
  • Columns of type DEC, DECIMAL, and NUMERIC can be part of the PRIMARY KEY.
  • Values of different floating-point and fixed-point data types are comparable and convertible to one another.
  • Values of non-integer numeric data types are neither comparable nor convertible to integer although integers are convertible to them. This restriction will be removed.