Numeric data types

Synopsis

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

Data type Description Min Max
BIGINT 8 bytes -9,223,372,036,854,775,808 9,223,372,036,854,775,807
DEC user-specified precision, exact <131072 digits.16383 digits> <131072 digits.16383 digits>
DECIMAL user-specified precision, exact <131072 digits.16383 digits> <131072 digits.16383 digits>
DOUBLE PRECISION Inexact 64-bit floating point number 15-digit precision 15-digit precision
FLOAT Inexact 64-bit floating point number variable variable
INTEGER 4-byte integer -2,147,483,648 2,147,483,647
INT 4-byte integer -2,147,483,648 2,147,483,647
NUMERIC user-specified precision, exact <131072 digits.16383 digits> <131072 digits.16383 digits>
REAL Inexact 32-bit floating point number 6-digit precision 6-digit precision
SMALLINT 2-byte integer -32,768 32,767

Integers

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

type_specification ::= SMALLINT | INT | INTEGER | BIGINT
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.

type_specification ::= { FLOAT | DOUBLE PRECISION | REAL }
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 exact user-specified precision types for different constraints including its value ranges.

type_specification ::= { DEC | DECIMAL | NUMERIC }
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.