|
|
|
Refer to Numeric Literals for details on specifying values to be stored in numeric columns.
Syntax
exact_numeric_data_type ::
TINYINT
| SMALLINT
| INTEGER
| BIGINT
| NUMERIC | NUMBER [ ( precision [ , scale ] ) ]
| DECIMAL [(precision, scale)]
| MONEY [(precision[,scale])]
[ AUTO_INCREMENT [ ( ± seed , ± increment ) ] ]
[ IDENTITY [ ( ± seed , ± increment ) ] ]
Arguments
Type TINYINT corresponds to an integer value stored in one byte. The range of TINYINT is -128 to 127.
Type SMALLINT corresponds to an integer value of length two bytes. The range of SMALLINT is -32768 to +32767.
Type INTEGER corresponds to an integer of length four bytes. The range of values for INTEGER columns is -2 ** 31 (-2,147,483,648) to (2 ** 31) -1 (2,147,483,647).
Type BIGINT corresponds to an integer of length eight bytes. The range of values for BIGINT columns is -2**63 (-9,223,372,036,854,775,808) to (2**63) -1 (9,223,372,036,854,775,807).
Type NUMERIC corresponds to a number with the given precision (maximum total number of digits) and scale (the number of digits to the right of the decimal point). By default, NUMERIC columns have a precision of 32 and scale of 0. If NUMERIC columns omit the scale, the default scale is 0. Precision must be an integer number >=scale and <= 32. Scale must be an integer number >=0 and <= Precision. NUMERIC type columns cannot specify a negative scale or specify a scale larger than the precision.
Internally, the FairCom Database Engine always uses 32 digits of precision. Thus, the only reason to use a precision less than 32 is to limit the range of a number.
The range of values for a NUMERIC type column is limited by its precision and scale.
For example, a precision of 3 and scale of 2 allows for 2 digits to the right of the decimal point.
For example, a precision of 32 and scale of 0 allows for 0 digits to the right of the decimal point.
A NUMERIC value is rounded when it has more digits than allowed by the scale.
For example, a precision of 3 and scale of 2 allows for 1 digit to the left of the decimal point.
For example, a precision of 32 and scale of 0 allows for 32 digits to the left of the decimal point.
The overflow error (-20052) is thrown when a NUMERIC value has more digits than allowed by the precision.
The following example shows what values will fit in a column created with a precision of 3 and scale of 2. In SQL, the column is defined as NUMBER(3,2).
CREATE TABLE num_test (num NUMERIC(3,2) );
Statement correctly executed
insert into num_test values(3.3);
1 record(s) inserted
insert into num_test values(3.33);
1 record(s) inserted
insert into num_test values(3.33333);
1 record(s) inserted
insert into num_test values(3.3555);
1 record(s) inserted
insert into num_test values(33.33);
Error : -20052 Error Description : Overflow/Underflow error
insert into num_test values(33.9);
Error : -20052 Error Description : Overflow/Underflow error
insert into num_test values(33);
Error : -20052 Error Description : Overflow/Underflow error
select * from num_test;
num
---
3.30
3.33
3.33
3.36
4 records selected
Type DECIMAL is comparable to type NUMERIC.
Type MONEY is equivalent to type NUMERIC with a scale of 2 or 4 (default 2). Precision must be an integer number >= scale and <= 32.
Example:
CREATE TABLE money_test (m MONEY(32,4) );
INSERT INTO money_test VALUES(0);
INSERT INTO money_test VALUES(0.1);
INSERT INTO money_test VALUES(0.1234);
INSERT INTO money_test VALUES(0.123456789);
INSERT INTO money_test VALUES(1234567890123456789012345678.1234);
SELECT * FROM money_test;
m
-------------------------------
0
0.10
0.1234
0.1235
1.2345678901234567890123456E+27
IDENTITY and AUTO_INCREMENT are not field types; they are additional attributes for the field types to which they refer.
For TINYINT, SMALLINT, INTEGER and BIGINT column types, an optional auto-incrementing attribute can be defined with the IDENTITY or the AUTO_INCREMENT option. This adds the column of the defined type to the table and automatically updates the value on each row insert.
IDENTITY / AUTO_INCREMENT do not guarantee uniqueness of assigned values.
IDENTITY / AUTO_INCREMENT can optionally specify seed and increment values. seed is the starting assignment value and is incremented by increment for each update.
CREATE TABLE t1 (name CHAR(10), id_num INTEGER IDENTITY (0, 1));
Only one IDENTITY / AUTO_INCREMENT column can be defined per table. IDENTITY / AUTO_INCREMENT columns cannot be specified on tables with only one column.
IDENTITY / AUTO_INCREMENT values assigned to aborted rows in a table are lost. Note that this can result in gaps in the numerical sequence order.
IDENTITY / AUTO_INCREMENT are not supported for NUMERIC, NUMBER, DECIMAL, or MONEY column types.
IDENTITY / AUTO_INCREMENT cannot be added to an existing field via ALTER TABLE.
AUTO_INCREMENT is similar to IDENTITY except that the INSERT statement allows you to assign a value to AUTO_INCREMENT column. When a value for the AUTO_INCREMENT column is not specified, it works like an IDENTITY column. When a value is specified, it inserts the specified value and the next inserted record will be incremented starting from that value.
In the example below, a table is created with an AUTO_INCREMENT column named id. It starts with a seed of 100 and an increment of 100. Four records are inserted. The value of the id in the first record is 100, which matches the seed value of 100. The value of the id in the second record is 200, which matches the increment value of 100 being added to the id of the previously inserted record. The third INSERT statement assigns the value 5000 to the id column, which is something an IDENTITY column cannot do. The fourth INSERT statement does not assign a value to id, and its value is automatically calculated as 5100, which is the value of the id in the third record plus the increment value of 100.
CREATE TABLE auto_inc (id INTEGER AUTO_INCREMENT(100,100), value CHAR(10) );
Statement correctly executed
INSERT INTO auto_inc VALUES('1');
1 record(s) inserted
INSERT INTO auto_inc VALUES('2');=
1 record(s) inserted
INSERT INTO auto_inc VALUES(5000, '3');
1 record(s) inserted
INSERT INTO auto_inc VALUES('4');
1 record(s) inserted
SELECT * FROM auto_inc;
id | value
------------
100 | 1
200 | 2
5000 | 3
5100 | 4
The following example uses an IDENTITY column instead of AUTO_INCREMENT. It works identically except that it throws an error when it tries to assign a value to the id column.
CREATE TABLE identity_test (id INTEGER IDENTITY(100,100), value CHAR(10) );
Statement correctly executed
INSERT INTO identity_test VALUES('1');
1 record(s) inserted
INSERT INTO identity_test VALUES('2');
1 record(s) inserted
INSERT INTO identity_test VALUES(5000, '3');
Error : -20017 Error Description : Too many values specified
INSERT INTO identity_test VALUES('4');
1 record(s) inserted
SELECT * FROM identity_test;
id | value
-----------
100 | 1
200 | 2
300 | 4
