4.3 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 SQL supports for table columns.

There are several categories of SQL data types:

• Character
• Exact numeric
• Approximate numeric
• Date-time
• Bit String

All 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

4.3.1 Character Data Types

See "Character String Literals" on page 4-36 for details on specifying values to be stored in character columns.

Syntax

char_data_type ::
    { CHARACTER | CHAR } [(length)] [ CHARACTER SET charset-name ]
|   { CHARACTER VARYING | CHAR VARYING | VARCHAR } [(length)]
       [ CHARACTER SET charset-name ]
|   LVARCHAR | LONG VARCHAR
|   { NATIONAL CHARACTER | NATIONAL CHAR | NCHAR } [(length)]
|   { NATIONAL CHARACTER VARYING | NATIONAL VARCHAR } [(length)]

Arguments

{ CHARACTER | CHAR } [(length)] [ CHARACTER SET charset-name ]

Type CHARACTER (abbreviated as CHAR) corresponds to a null terminated character string with the maximum length specified. The default length is 1. The maximum length is 2000.

The optional CHARACTER SET clause specifies an alternative character set supported by the underlying storage system. "Specifying the Character Set for Character Data Types" on page 4-6 describes general considerations for using this clause. See the documentation for the underlying storage system for details on valid values for charset-name, if any.

{ NATIONAL CHARACTER | NATIONAL CHAR | NCHAR } [(length)]

Type NATIONAL CHARACTER is equivalent to type CHARACTER with a CHARACTER SET clause specifying the character set designated as NATIONAL CHARACTER by the underlying storage system. See "Specifying the Character Set for Character Data Types" on page 4-6.

{ CHARACTER VARYING | CHAR VARYING | VARCHAR } [(length)] [ CHARACTER SET charset-name ]

Type CHARACTER VARYING corresponds to a variable-length character string with the maximum length specified.

The optional CHARACTER SET clause specifies an alternative character set supported by the underlying storage system. "Specifying the Character Set for Character Data Types" on page 4-6 describes general considerations for using this clause. See the documentation for the underlying storage system for details on valid values for charset-name, if any.

The default length for columns defined as CHARACTER VARYING is 1. The maximum length depends on whether the data type specification includes the CHARACTER SET clause:

• If it does not specify CHARACTER SET, the maximum length is 2000.
• If it does specify CHARACTER SET, the maximum length is 32752.

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

Type NATIONAL CHARACTER VARYING is equivalent to type CHARACTER VARYING with a CHARACTER SET clause specifying the character set designated as NATIONAL CHARACTER by the underlying storage system. See "Specifying the Character Set for Character Data Types" on page 4-6.

LVARCHAR | LONG VARCHAR

Type LONG VARCHAR corresponds to an arbitrarily-long character string with a maximum length limited by the specific storage system.

The arbitrary size and unstructured nature of LONG data types restrict where they can be used.

• LONG columns are allowed in select lists of query expressions and in INSERT statements.
• INSERT statements can store data from columns of any type into a LONG VARCHAR column, but LONG VARCHAR data cannot be stored in any other type.
• CONTAINS predicates are the only predicates that allow LONG columns (and then only if the underlying storage system explicitly supports CONTAINS predicates).
• Conditional expressions, arithmetic expressions, and functions cannot specify LONG columns.
• UPDATE statements cannot specify LONG columns.

Specifying the Character Set for Character Data Types

SQL allows column definitions of type CHARACTER and CHARACTER VARYING to specify an alternate character set. If you omit the CHARACTER SET clause in a column definition, the default character set is the standard 7-bit ASCII character set, shown in Table 4–2.

The character set associated with a table column defines which set of characters can be stored in that column, how those characters are represented in the underlying storage system, and how character strings using the character set compare with each other:

• The set of characters allowed in a character set is called the repertoire of the character set. The default ASCII character set has a repertoire of 128 characters, shown in Table 4–2. Other character sets, such as Unicode, specify much larger repertoires and include characters for many languages other than English.
• The storage representation for a character set is called the form of use of the character set. The form of use for the default ASCII character set is a single byte (or octet) containing a number designating a particular ASCII character, also shown in Table 4–2. Other character sets, such as Unicode, use two or more bytes (or a varying number of bytes, depending on the character) for each character.
• The rules used to control how character strings compare with each other is called the collation of a character set. Each character set specifies a collating sequence that defines relative values of each character for comparing, merging and sorting character strings. Character sets may also define additional collations that override the default for a character set. SQL statements specify such collations with the COLLATE clause in character column definitions, basic predicates, the GROUP BY clause of query expressions, and the ORDER BY clause of SELECT statements.

Table 4–2 shows the characters in the default ASCII character set and the decimal values that designate each character. (This is the default representation on UNIX; other operating systems may have slight differences in their definitions of the default ASCII character set.) The values also define the collating sequence for the character set. For instance, this collating sequence specifies that a lowercase letter is always a larger value than an uppercase letter.

Dharma/SQL supports the ASCII_SET character-set name and a collation sequence named CASE_INSENSITIVE. The ASCII_SET character set is the same as the default and is provided to test and illustrate the CHARACTER SET syntax. The CASE_INSENSITIVE collation sequence overrides the default ASCII collation and specifies the same comparison values for a lowercase letter as its uppercase counterpart.

The following example uses the ISQL TABLE command to show two tables. bigwigs is defined with the CASE_INSENSITIVE collating sequence for its column, and bigwigs2 is not. Both tables contain the same data. SELECT statements show the difference in collation:

ISQL> TABLE bigwigs
COLNAME        NULL ?    TYPE      LENGTH    CHARSET NAME   COLLATION
-------        ------    ----      ------    ------------   ---------
name                     CHAR      10
CASE_INSENSITIVE
ISQL> TABLE bigwigs2
COLNAME        NULL ?    TYPE      LENGTH    CHARSET NAME   COLLATION
-------        ------    ----      ------    ------------   ---------
name                     CHAR      10

ISQL> select * from bigwigs order by name;
NAME
----
bill
LARRY
mARk
scott
4 records selected
ISQL> select * from bigwigs2 order by name;
NAME
----
LARRY
bill
mARk
scott

Support for character sets other than ASCII_SET and collations other than CASE_INSENSITIVE depends on the underlying storage system. When statements refer to a character set or collation name that is not supported by the underlying storage system, SQL generates an error:

ISQL> create table badset (c1 char(10) character set bad_set);
create table badset (c1 char(10) character set bad_set);
                                               *
error(-20239): Invalid character set name specified
ISQL> create table badseq (c1 char(10) collate bad_seq);
create table badseq (c1 char(10) collate bad_seq);
                                         *
error(-20240): Invalid collation name specified

The NATIONAL CHARACTER reserved words in SQL are shorthand notation for specifying a particular character set supported by the underlying storage system. If the underlying storage system designates a supported character set as the national character set, column definitions can use the NATIONAL CHARACTER (or NATIONAL CHARACTER VARYING) data type instead of explicitly specifying the character set name in the CHARACTER SET clause of the CHARACTER (or CHARACTER VARYING ) data type. If the underlying storage system does not associate another character set with the NATIONAL CHARACTER clause, the default national character set is the ASCII_SET character set.

4.3.2 Exact Numeric Data Types

See "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)]

Arguments

TINYINT

Type TINYINT corresponds to an integer value stored in one byte. The range of TINYINT is -128 to 127.

SMALLINT

Type SMALLINT corresponds to an integer value of length 2 bytes.

The range of SMALLINT is -32768 to +32767.

INTEGER

Type INTEGER corresponds to an integer of length 4 bytes.

The range of values for INTEGER columns is -2 ** 31 to 2 ** 31 -1.

BIGINT

Type BIGINT corresponds to an integer of length 8 bytes. The range of values for BIGINT columns is -2 ** 63 to 2 ** 63 -1.

NUMERIC | NUMBER [ ( precision [ , scale ] ) ]

Type NUMERIC corresponds to a number with the given precision (maximum 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.

The range of values for a NUMERIC type column is -n to +n where n is the largest number that can be represented with the specified precision and scale. If a value exceeds the precision of a NUMERIC column, SQL generates an overflow error. If a value exceeds the scale of a NUMERIC column, SQL rounds the value.

NUMERIC type columns cannot specify a negative scale or specify a scale larger than the precision.

The following example shows what values will fit in a column created with a precision of 3 and scale of 2:

insert into t4 values(33.33);
error(-20052): Overflow error
insert into t4 values(33.9);
error(-20052): Overflow error
insert into t4 values(3.3);
1 record inserted.
insert into t4 values(33);
error(-20052): Overflow error
insert into t4 values(3.33);
1 record inserted.
insert into t4 values(3.33333);
1 record inserted.
insert into t4 values(3.3555);
1 record inserted.
select * from t4;
    C1
    --
  3.30
  3.33
  3.33
  3.36
4 records selected

DECIMAL [(precision, scale)]

Type DECIMAL is equivalent to type NUMERIC.

MONEY [(precision)]

Type MONEY is equivalent to type NUMERIC with a fixed scale of 2.

4.3.3 Approximate Numeric Data Types

See "Numeric Literals" for details on specifying values to be stored in numeric columns.

Syntax

approx_numeric_data_type ::
    REAL
|   DOUBLE PRECISION
|   FLOAT [ (precision) ]

Arguments

REAL

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

DOUBLE PRECISION

Type DOUBLE PRECISION corresponds to a double precision floating point number equivalent to the C language double type.

FLOAT [ (precision) ]

Type FLOAT corresponds to a double precision floating point number of the given precision. By default, FLOAT columns have a precision of 8.

4.3.4 Date-Time Data Types

See "Date-Time Literals" for details on specifying values to be stored in date-time columns. See "Date-Time Format Strings" for details on using format strings to specify the output format of date-time columns.

Syntax

date_time_data_type ::
    DATE
|   TIME
|   TIMESTAMP

Arguments

DATE

Type DATE stores a date value as three parts: year, month, and day. The range for the parts is:

• Year: 1 to 9999
• Month: 1 to 12
• Day: Lower limit is 1; the upper limit depends on the month and the year

TIME

Type TIME stores a time value as four parts: hours, minutes, seconds, and milliseconds. The range for the parts is:

• Hours: 0 to 23
• Minutes: 0 to 59
• Seconds: 0 to 59
• Milliseconds: 0 to 999

TIMESTAMP

Type TIMESTAMP combines the parts of DATE and TIME.

4.3.5 Bit String Data Types

Syntax

bit_string_data_type ::
    BIT
|   BINARY [(length)]
|   VARBINARY [(length)]
|   LVARBINARY | LONG VARBINARY

Arguments

BIT

Type BIT corresponds to a single bit value of 0 or 1.

SQL statements can assign and compare values in BIT columns to and from columns of types CHAR, VARCHAR, BINARY, VARBINARY, TINYINT, SMALLINT, and INTEGER. However, in assignments from BINARY, VARBINARY, and LONG VARBINARY, the value of the first four bits must be 0001 or 0000.

No arithmetic operations are allowed on BIT columns.

BINARY [(length)]

Type BINARY corresponds to a bit field of the specified length of bytes. The default length is 1 byte. The maximum length is 2000 bytes.

In interactive SQL, INSERT statements must use a special format to store values in BINARY columns. They can specify the binary values as a bit string, hexadecimal string, or character string. INSERT statements must enclose binary values in single-quote marks, preceded by b for a bit string and x for a hexadecimal string:

SQL interprets a character string as the character representation of a hexadecimal string.

If the data inserted into a BINARY column is less than the length specified, SQL pads it with zeroes.

BINARY data can be assigned and compared to and from columns of type BIT, CHAR, and VARBINARY types. No arithmetic operations are allowed.

VARBINARY [(length)]

Type VARBINARY corresponds to a variable-length bit field with the maximum length specified. The default length is 1 and the maximum length is 32752. Otherwise, VARBINARY columns have the same characteristics as BINARY.

LVARBINARY | LONG VARBINARY

Type LONG VARBINARY corresponds to an arbitrarily-long bit field with the maximum length defined by the underlying storage system.

The arbitrary size and unstructured nature of LONG data types restrict where they can be used.

• LONG columns are allowed in select lists of query expressions and in INSERT statements.
• INSERT statements can store data from columns of any type into a LONG VARCHAR column, but LONG VARCHAR data cannot be stored in any other type.
• CONTAINS predicates are the only predicates that allow LONG columns (and then only if the underlying storage system explicitly supports CONTAINS predicates).
• Conditional expressions, arithmetic expressions, and functions cannot specify LONG columns.
• UPDATE statements cannot specify LONG columns.