Numeric Data Types

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Data Types and Expressions > Data Type >

Numeric Data Types

Numeric data types allow the database server to store numbers such as integers and real numbers in a column.

Numeric Data Type:
 
                                 (1)
|--+-| Exact Numeric Data Type |------------+-------------------|
   |                                   (2)  |
   '-| Approximate Numeric Data Type |------'

Notes:

See Exact Numeric Data Types
See Approximate Numeric Data Types

The values of numbers are stored either as exact numeric data types or as approximate numeric data types.

Exact Numeric Data Types

An exact numeric data type stores numbers of a specified precision and scale.

Exact Numeric Data Type:
 
|--+-+-DECIMAL-+--+-------------------------------+--+----------|
   | +-DEC-----+  |               .-, 0------.    |  |
   | '-NUMERIC-'  '-(--precision--+----------+--)-'  |
   |                              '-,--scale-'       |
   |  (1)          .-(16, 2)-----------------------. |
   +--------MONEY--+-------------------------------+-+
   |               |               .-, 2------.    | |
   |               '-(--precision--+----------+--)-' |
   |                               '-,--scale-'      |
   +-+-INT-----+-------------------------------------+
   | '-INTEGER-'                                     |
   +-SMALLINT----------------------------------------+
   |  (1)                                            |
   '------+-INT8-------------------------+-----------'
          |              .-(1)---------. |
          '-+-SERIAL--+--+-------------+-'
            '-SERIAL8-'  '-(--start--)-'

Notes:

Informix extension

Element	Description	Restrictions	Syntax
precision	Significant digits	Must be an integer; 1 ≤ precision ≤ 32	Literal Number
scale	Digits in fractional part	Must be an integer; 1 ≤ scale ≤ precision	Literal Number
start	Integer starting value	For SERIAL: 1 ≤ start ≤ 2,147,483,64; For SERIAL8: 1 ≤ start ≤ 9,223,372,036,854,775,807	Literal Number

The precision of a data type is the number of digits that the data type stores. The scale is the number of digits to the right of the decimal separator.

The following table summarizes the exact numeric data types available.

Data Type	Description
DEC(p,s)	Synonym for DECIMAL(p,s)
DECIMAL(p,s)	Stores fixed-point decimal values of real numbers, with up to 30 significant digits in the fractional part, or up to 32 significant digits to the left of the decimal point.
INT	Synonym for INTEGER
INTEGER	Stores a 4-byte integer value. These values can be in the range from -((231)-1) to (231)-1 (the range -2,147,483,647 to 2,147,483,647).
INT8	Stores an 8-byte integer value. These values can be in the range from -((263)-1) to (263)-1 (the range -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807).
MONEY(p,s)	Stores fixed-point currency values. These values have same internal data format as a fixed-point DECIMAL(p,s) value.
NUMERIC(p,s)	ANSI-compliant synonym for DECIMAL(p,s)
SERIAL	Stores a 4-byte positive integer that the database server generates. Values can range from 1 to (2**31)-1 (that is, from 1 to 2,147,483,647).
SERIAL8	Stores an 8-byte positive integer value that the database server generates. Values can range from 1 to (2**63)-1 (that is, from 1 to 9,223,372,036,854,775,807).
SMALLINT	Stores a 2-byte integer value. These values can be in the range from -((215)-1) to (215)-1 (that is, from -32,767 to 32,767).

DECIMAL(p,s) Data Types

The first DECIMAL(p, s) parameter (p) specifies the precision (the total number of digits) and the second (s) parameter specifies the scale (the number of digits in the fractional part). If you provide only one parameter, an ANSI-compliant database interprets it as the precision of a fixed-point number and the default scale is 0. If you specify no parameters, and the database is ANSI-compliant, then by default the precision is 16 and the scale is 0.

If the database is not ANSI-compliant, and you specify fewer than 2 parameters, you declare a floating-point DECIMAL, which is not an exact number data type. (See instead the section Approximate Numeric Data Types.)

DECIMAL(p, s) values are stored internally with the first byte representing a sign bit and a 7-bit exponent in excess-65 format. The other bytes express the mantissa as base-100 digits. This implies that DECIMAL(32,s) data types store only s-1 decimal digits to the right of the decimal point, if s is an odd number.

SERIAL and SERIAL8 Data Types

If you want to insert an explicit value into a SERIAL or SERIAL8 column, you can use any nonzero number. You cannot, however, start or reset the value of a SERIAL or SERIAL8 column with a negative number. (For details of an alternative feature of Dynamic Server for generating integer values, see CREATE SEQUENCE.)

A SERIAL or SERIAL8 column is not unique unless you set a unique index on the column. (The index can also be in the form of a primary key or unique constraint.) With such an index, values in SERIAL or SERIAL8 columns are guaranteed to be unique, but successive values are not necessarily contiguous.

Approximate Numeric Data Types

An approximate numeric data type represents numeric values approximately.

Approximate Numeric Data Type:
 
                     (1)
                  .--------(16)-----.
|--+-+-DECIMAL-+--+-(--precision--)-+------------+--------------|
   | +-DEC-----+                                 |
   | '-NUMERIC-'                                 |
   +-+-FLOAT------------+--+-------------------+-+
   | '-DOUBLE PRECISION-'  '-(float_precision)-' |
   |    (1)                                      |
   '-+--------SMALLFLOAT-+-----------------------'
     '-REAL--------------'

Notes:

Informix extension

Element	Description	Restrictions	Syntax
float_precision	The float_precision is ignored, but is ANSI/ISO compliant.	Must be a positive integer. Specified value has no effect.	Literal Number
precision	Significant digits. Default is 16.	An integer; 1 ≤ precision ≤ 32	Literal Number

Use approximate numeric data types for very large and very small numbers that can tolerate some degree of rounding during arithmetic operations.

The following table summarizes the built-in approximate numeric data types.

Data Type	Description
DEC(p)	Synonym for DECIMAL(p)
DECIMAL(p)	Stores floating-point decimal values in the approximate range from 1.0E-130 to 9.99E+126 The p parameter specifies the precision. If no precision is specified, the default is 16. This floating-point data type is available as an approximate numeric type only in a database that is not ANSI-compliant. In an ANSI-compliant database, DECIMAL(p) is implemented as a fixed-point DECIMAL; see Exact Numeric Data Types.
DOUBLE PRECISION	ANSI-compliant synonym for FLOAT. The float_precision term is not valid when you use this synonym in data type declarations.
FLOAT	Stores double-precision floating-point numbers with up to 16 significant digits. The float-precision parameter is accepted in data-type declarations for compliance with the ANSI/ISO standard for SQL, but this parameter has no effect on the actual precision of values that the database server stores.
NUMERIC(p)	ANSI-compliant synonym for DECIMAL(p) In an ANSI-compliant database, this is implemented as an exact numeric type, with the specified precision and a scale of zero, rather than an approximate numeric (floating-point) data type.
REAL	ANSI-compliant synonym for SMALLFLOAT
SMALLFLOAT	Stores single-precision floating-point numbers with approximately 8 significant digits

The built-in number data types of Informix database servers support real numbers. They cannot directly store imaginary or complex numbers.

In Dynamic Server, you must create a user-defined data type for applications that support values that can have an imaginary part.

No more than nine arguments to an external UDR can be DECIMAL data types of SQL that the UDR declares as BigDecimal data types of the Java language.