DB SQL data types

The SQL statements CREATE TABLE and ALTER TABLE specify data types for each column in the tables they define. This section describes the data types FairCom DB SQL supports for table columns.

There are several categories of FairCom DB SQL data types:

• Character
• Exact numeric
• Approximate numeric
• Date-time
• Binary

All of the data types can store null values. A null value indicates that the value is not known and is distinct from all non-null values.

Syntax

data_type ::

char_data_type

| exact_numeric_data_type

| approx_numeric_data_type

| date_time_data_type

| bit_string_data_type

Character Data Types

Syntax

char_data_type ::

{ CHARACTER | CHAR } [(length)]

| { CHARACTER VARYING | CHAR VARYING | VARCHAR } [(length)]

| LVARCHAR

Arguments

Character string literals

Literals are a type of expression that specify a constant value (they are also called constants). You can specify literals wherever FairCom DB SQL syntax allows expressions.

A character string literal is a string of characters enclosed in single quotation marks ( ' ).

To include a single quotation mark in a character-string literal, precede it with an additional single quotation mark. The following FairCom DB SQL examples demonstrate embedding quotation marks in character-string literals:

insert into quote values('unquoted literal');

insert into quote values('''single-quoted literal''');

insert into quote values('"double-quoted literal"');

insert into quote values('O''Hare');

select * from quote;

c1

unquoted literal

'single-quoted literal'

"double-quoted literal"

O'Hare

To insert a character-string literal that spans multiple lines, enclose each line in single quotation marks. The following FairCom DB SQL examples shows this syntax, as well as embedding quotation marks in one of the lines:

insert into quote2 values ('Here''s a very long character string '

'literal that will not fit on a single line.');

1 record inserted.

select * from quote2;

C1

--

Here's a very long character string literal that will not fit on a single line.

Exact Numeric Data Types

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

Numeric Literals

Literals are a type of expression that specify a constant value (they are also called constants). You can specify literals wherever FairCom DB SQL syntax allows expressions.

A numeric literal is a string of digits that FairCom DB SQL interprets as a decimal number. FairCom DB SQL allows the string to be in a variety of formats, including scientific notation.

Literals Syntax

[+|-]{[0-9][0-9]...}[.[0-9][0-9]...][[E|e][+|-][0-9]{[0-9]}]

Examples

The following are all valid numeric strings:

123

123.456

-123.456

12.34E-04

Exact Numeric Data Types Examples

NUMERIC/DECIMAL [ ( precision [ , scale ] ) ]

Type NUMERIC (and its equivalent types NUMBER and DECIMAL) correspond 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.

• The number of digits to the right of the decimal point is specified by 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.

• The number of digits to the left of the decimal point is specified by the precision minus 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

MONEY [ ( precision [ , scale ] ) ]

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 [ ( ± seed , ± increment ) ] ] and [ AUTO_INCREMENT [ ( ± seed , ± increment ) ] ]

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 does not guarantee the 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

Approximate Numeric Data Types

Syntax

approx_numeric_data_type ::

REAL

| FLOAT [ (precision) ]

| DOUBLE [ precision ]

Arguments

• REAL

Type REAL corresponds to a single precision floating point number equivalent to the C language float type.

It is a 4-byte, IEEE 754 binary floating point number.

Its safe integer range is from −9,999,999 to 9,999,999. Its largest number is ±3.4028234664e+38. Its smallest fraction is ±1.1754943508e-38.

• FLOAT | DOUBLE PRECISION

Type FLOAT corresponds to a double precision floating point number equivalent to the C language double type. By default, FLOAT columns have a precision of 8.

It is an 8-byte, IEEE 754 binary floating point number.

Its safe integer range is from −9,007,199,254,740,991 to 9,007,199,254,740,991. Its largest number is ±1.7976931348623158e+308. Its smallest fraction is ±2.2250738585072014e-308.

In V11 and later, DOUBLE can be used as an alias for DOUBLE PRECISION.

Numeric Literals

Literals are a type of expression that specify a constant value (they are also called constants). You can specify literals wherever FairCom DB SQL syntax allows expressions.

A numeric literal is a string of digits that FairCom DB SQL interprets as a decimal number. FairCom DB SQL allows the string to be in a variety of formats, including scientific notation.

Syntax

[+|-]{[0-9][0-9]...}[.[0-9][0-9]...][[E|e][+|-][0-9]{[0-9]}]

Examples

The following are all valid numeric strings:

123

123.456

-123.456

12.34E-04

Date-Time Data Types

Syntax

date_time_data_type ::

DATE

| TIME

| TIMESTAMP

Arguments

Date Literals

Date literals specify a day, month, and year. By default, FairCom DB SQL supports any of the following formats, enclosed in single quotation marks ( ' ).

Syntax

date-literal ::

{d 'yyyy-mm-dd'}

| mm-dd-yyyy

| mm/dd/yyyy

| yyyy-mm-dd

| yyyy/mm/dd

| dd-mon-yyyy

| dd/mon/yyyy

Arguments

{d 'yyyy-mm-dd'}

A date literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace. For example:

INSERT INTO DTEST VALUES ({d '1994-05-07'})

If you use the ODBC escape clause, you must specify the date using the format yyyy-mm-dd.

dd

The day of month as a 1- or 2-digit number (in the range 01-31).

mm

The month value as a 1- or 2-digit number (in the range 01-12).

mon

The first three characters of the name of the month (in the range ‘JAN’ to ‘DEC’).

yyyy

The year as four-digit number. By default, FairCom DB SQL generates an Invalid date string error if the year is specified as anything other than digits.

Examples

The following FairCom DB SQL examples demonstrate some supported formats for date literals:

CREATE TABLE T2 (C1 DATE, C2 TIME);

INSERT INTO T2 (C1) VALUES('5/7/56');

INSERT INTO T2 (C1) VALUES('7/MAY/1956');

INSERT INTO T2 (C1) VALUES('1956/05/07');

INSERT INTO T2 (C1) VALUES({d '1956-05-07'});

INSERT INTO T2 (C1) VALUES('29-sEP-1952');

SELECT C1 FROM T2;

c1

1956-05-07

1956-05-07

1956-05-07

1956-05-07

1952-09-29

Time Literals

Time literals specify an hour, minute, second, and millisecond, using the following format, enclosed in single quotation marks ( ' ):

Syntax

time-literal ::

{t 'hh:mi:ss'}

| hh:mi:ss

Arguments

{t 'hh:mi:ss'}

A time literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase t. End the literal with a close brace. For example:

INSERT INTO TTEST VALUES ({t '23:22:12'})

If you use the ODBC escape clause, you must specify the time using the format hh:mi:ss.

hh

The hour value as a 1- or 2-digit number (in the range 00 to 23).

mi

The minute value as a 1- or 2-digit number (in the range 00 to 59).

ss

The seconds value as a 1- or 2-digit number (in the range 00 to 59).

Examples

The following FairCom DB SQL examples show some of the formats FairCom DB SQL will and will not accept for time literals:

INSERT INTO T2 (C2) VALUES('3');

error(-20234): Invalid time string

INSERT INTO T2 (C2) VALUES('8:30');

error(-20234): Invalid time string

INSERT INTO T2 (C2) VALUES('8:30:1');

INSERT INTO T2 (C2) VALUES('8:30:');

error(-20234): Invalid time string

INSERT INTO T2 (C2) VALUES('8:30:00');

INSERT INTO T2 (C2) VALUES('8:30:01');

INSERT INTO T2 (C2) VALUES({t'8:30:01'});

SELECT C2 FROM T2;

c2

08:30:01

08:30:00

08:30:01

08:30:01

Timestamp Literals

Timestamp literals specify a date and a time separated by a space, enclosed in single quotation marks ( ' ):

Syntax

{ts 'yyyy-mm-dd hh:mi:ss'}

| date-literal time-literal '

Arguments

{ts 'yyyy-mm-dd hh:mi:ss'}

A timestamp literal enclosed in an escape clause compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase ts. End the literal with a close brace. For example:

INSERT INTO DTEST

VALUES ({ts '1956-05-07 10:41:37'})

If you use the ODBC escape clause, you must specify the timestamp using the format yyyy-mm-dd hh:mi:ss.

date-literal

A date literal.

time-literal

A time literal.

Example

SELECT * FROM DTEST WHERE C1 = {ts '1956-05-07 10:41:37'}

Bit String Data Types

Syntax

bit_string_data_type ::

BIT

| BINARY [(length)]

| VARBINARY [(length)]

| LVARBINARY

Arguments