Qualifier Field Valid Entry

YEAR-MONTH
INTERVAL

YEAR

A number of years

MONTH

A number of months

DAY-TIME INTERVAL

DAY

A number of days

HOUR

A number of hours

MINUTE

A number of minutes

SECOND

A number of seconds

FRACTION

A decimal fraction of a second, with up to 5 digits of precision. The default precision is 3 digits (thousandth of a second). Other precisions are indicated explicitly by writing FRACTION(n), where n is the desired number of digits from 1 to 5.
INTERVAL Classes As with a DATETIME column, you can define an INTERVAL column to include a subset of the fields you need; however, because the INTERVAL data type represents a span of time that is independent of an actual date, you cannot combine the two INTERVAL classes. For example, because the number of days in a month depends on which month it is, a single INTERVAL data value cannot combine months and days.

A value entered into an INTERVAL column need not include all fields contained in the column. For example, you can enter a value of HOUR TO SECOND into a column defined as DAY TO SECOND. However, a value must always consist of a contiguous sequence of fields. In the previous example, you cannot enter just HOUR and SECOND values; you must also include MINUTE values.

A valid INTERVAL literal contains the INTERVAL keyword, the values to be entered, and the field qualifiers. (See the discussion of the Literal Interval segment in Chapter 1 of the Informix Guide to SQL: Syntax.) When a value contains only one field, the largest and smallest fields are the same.

When you enter a value in an INTERVAL column, you must specify the largest and smallest fields in the value, just as you do for DATETIME values. In addition, you can use n optionally to specify the precision of the first field (and the last field if it is a FRACTION). If the largest and smallest field qualifiers are both FRACTIONS, you can specify only the precision in the last field. Acceptable qualifiers for the largest and smallest fields are identical to the list of INTERVAL fields displayed in Figure 2-12.

If you are using the DB-Access TABLE menu and you do not specify the INTERVAL field qualifiers, the default INTERVAL qualifier, YEAR TO YEAR, is assigned.

The largest_qualifier in an INTERVAL value can be up to nine digits (except for FRACTION, which cannot be more than five digits), but if the value you want to enter is greater than the default number of digits allowed for that field, you must explicitly identify the number of significant digits in the value you are entering. For example, to define an INTERVAL of DAY TO HOUR that can store up to 999 days, you could specify it as shown in the following example:

INTERVAL values use the same delimiters as DATETIME values. The delimiters are shown in Figure 2-13.

Figure 2-13

Delimiter Placement in DATETIME Expression

hyphen

Between the YEAR and MONTH portions of the value

space

Between the DAY and HOUR portions of the value

colon

Between the HOUR and MINUTE and the MINUTE and SECOND portions of the value

decimal point

Between the SECOND and FRACTION portions of the value

INTERVAL Delimiters You also can enter INTERVAL values as character strings. However, the character string must include information for the identical sequence of fields defined for that column. The INSERT statement in the following example shows an INTERVAL value entered as a character string:

Because the lead_time column is defined as INTERVAL DAY(3) TO DAY, this INTERVAL value requires only one field, the span of days required for lead time. If the character string does not contain information for all fields (or adds additional fields), the database server returns an error. For more information on entering INTERVAL values as character strings, see Chapter 1 of the Informix Guide to SQL: Syntax.

By default, all fields of an INTERVAL column are two-digit numbers except for the year and fraction fields. The year field is stored as four digits. The fraction field requires n digits where 1 £ n £ 5, rounded up to an even number. You can use the following formula (rounded up to a whole number of bytes) to calculate the number of bytes required for an INTERVAL value:

For example, a YEAR TO MONTH qualifier requires a total of six digits (four for year and two for month). This data value requires 4, or (6/2) + 1, bytes of storage.

For information on how to use INTERVAL data in arithmetic and relational operations, see "Using Implicit Casts". For information on how to use INTERVAL as a constant expression, see the description of the INTERVAL Field Qualifier segment in Chapter 1 of the Informix Guide to SQL: Syntax.

LIST(e)

The LIST data type is a collection type that stores ordered, nonunique elements; that is it allows duplicate element values. The elements of a LIST have ordinal positions; with a first, second, and third element in a LIST. (For a collection type with no ordinal positions, see the MULTISET data type on page 2-54 and the SET data type on page 2-62.)

By default, the database server inserts LIST elements at the end of the list. To support the ordinal position of a LIST, the INSERT statement provides the AT clause. This clause allows you to specify the position at which you wish to insert a list-element value. For more information, see the INSERT statement in of the Informix Guide to SQL: Syntax.

All elements in a LIST have the same element type. To specify the element type, use the following syntax:

The element_type of a collection can be any of the following:

You must specify the NOT NULL constraint for LIST elements. No other constraints are valid for LIST columns. For more information on the syntax of the LIST collection type, see the Data Type segment in the Informix Guide to SQL: Syntax.

You can use LIST anywhere that you would use any other data type, for example:

You cannot use LIST with an aggregate function such as AVG, MAX, MIN, or SUM.

Two lists are equal if they have the same elements in the same order. The following examples are lists but are not equal:

The above statements are not equal because the values are not in the same order. To be equal, the second statement would have to be:

LVARCHAR

 The LVARCHAR data type is an SQL data type that you can use to create a column of variable-length character data types that are potentially larger than 255 bytes.

The LVARCHAR data type is also used for input and output casts for opaque data types. The LVARCHAR data type stores opaque data types in the string (external) format. Each opaque type has an input support function and cast, which convert it from LVARCHAR to a form that clients can manipulate. Each also has an output support function and cast, which convert it from its internal representation to LVARCHAR.

When used in other contexts, the LVARCHAR data type has the following restrictions:

MONEY(p,s)

 The MONEY data type stores currency amounts. As with the DECIMAL data type, the MONEY data type stores fixed-point numbers up to a maximum of 32 significant digits, where p is the total number of significant digits (the precision) and s is the number of digits to the right of the decimal point (the scale).

Unlike the DECIMAL data type, the MONEY data type always is treated as a fixed-point decimal number. The database server defines the data type MONEY(p) as DECIMAL(p,2). If the precision and scale are not specified, the database server defines a MONEY column as DECIMAL(16,2).

You can use the following formula (rounded up to a whole number of bytes) to calculate the byte storage for a MONEY data type:

For example, a MONEY data type with a precision of 16 and a scale of 2 [MONEY(16,2)] requires 10, or (16 + 3)/2, bytes of storage.

GLS
The default value that the database server uses for scale is locale-dependent. The default locale specifies a default scale of two. For nondefault locales, if the scale is omitted from the declaration, the database server creates MONEY values with a locale-specific scale. For more information, see the Guide to GLS Functionality.

Client applications format values in MONEY columns with the following currency notation:

GLS
The currency notation that client applications use is locale-dependent. If you specify a nondefault locale, the client uses a culture-specific format for MONEY values. For more information, see the Guide to GLS Functionality.

You can change the format for MONEY values by changing the DBMONEY environment variable. See page 3-29 for information on how to set the DBMONEY environment variable.

MULTISET(e)

The MULTISET data type is a collection type that stores nonunique elements: it allows duplicate element values. The elements in a MULTISET have no ordinal position. That is, there is no concept of a first, second, or third element in a MULTISET. (For a collection type with ordinal positions for elements, see the LIST data type on page 2-51.)

All elements in a MULTISET have the same element type. To specify the element type, use the following syntax:

The element_type of a collection can be any of the following:

You must specify the NOT NULL constraint for MULTISET elements. No other constraints are valid for MULTISET columns. For more information on the syntax of the MULTISET collection type, see the Data Type segment in the Informix Guide to SQL: Syntax.

You can use MULTISET anywhere that you use any other data type, unless otherwise indicated. For example:

For more information, see the Condition and Expression segments in the Informix Guide to SQL: Syntax.

You cannot use MULTISET with an aggregate function such as AVG, MAX, MIN, or SUM.

Two multisets are equal if they have the same elements, even if the elements are in different positions in the set. The following examples are multisets but are not equal:

The following multisets are equal:

Named Row Type

 A named row type is defined by its name. That name must be unique within the schema. An unnamed row type is a row type that contains fields but has no user-defined name (see the description on page 2-67). Use a named row type if you want to use type inheritance.

Defining Named Row Types

You must define a named row type in the database. Definitions for named row types are stored in the sysxtdtypes system catalog table.

The fields of a row type can be any data type, except SERIAL and SERIAL8. The fields of a row type that are TEXT or BYTE type can be used in typed tables only. If you want to assign a row type to a column, then the elements of the row cannot be of TEXT and BYTE data types.

In general, the data type of the field of a row type can be any of the following:

The following SQL statements maintain the definitions of named row types in the database:

For details about the SQL syntax statements above, see Informix Guide to SQL: Syntax.

For examples on how to create tables and populate tables with named row type columns, see Chapter 10, "Understanding Complex Data Types" in the Informix Guide to SQL: Tutorial.

Equivalence and Named Row Types

No two named row types can be equal, even if they have identical structures, because they have different names. For example, the following named row types have the same structure but are not equal:

Named Row Types and Inheritance

 Named row types can be part of a type-inheritance hierarchy. That is, one named row type can be the parent (supertype) of another named row type. A subtype in a hierarchy inherits all the properties of its supertype. Type inheritance is discussed in the CREATE ROW TYPE statement in the Informix Guide to SQL: Syntax and in Chapter 10, "Understanding Complex Data Types" in the Informix Guide to SQL: Tutorial.

Typed Tables

Tables that are part of an inheritance hierarchy must be typed tables. Typed tables are tables that have been assigned a named row type. See the CREATE TABLE statement in the Informix Guide to SQL: Syntax to learn how to create typed tables. Table inheritance and how it relates to type inheritance is also discussed in that section.

For examples on creating and populating typed tables, see Chapter 12, "Accessing Complex Data Types," in the Informix Guide to SQL: Tutorial.

NCHAR(n)

GLS
The NCHAR data type stores fixed-length character data. This data can be a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between the CHAR and NCHAR data types is the collation order. While the collation order of the CHAR data type is defined by the code-set order, the collation order of the NCHAR data type depends on the locale-specific localized order. For more information about the NCHAR data type, see the Guide to GLS Functionality.

NUMERIC(p,s)

The NUMERIC data type is a synonym for fixed-point DECIMAL.

NVARCHAR(m,r)

GLS
The NVARCHAR data type stores character data of varying lengths. This data can be a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between VARCHAR and NVARCHAR data types is the collation order. While the collation order of the VARCHAR data type is defined by the code-set order, the collation order of the NVARCHAR data type depends on the locale-specific localized order. For more information about the NVARCHAR data type, see the Guide to GLS Functionality.

Opaque Data Type

An opaque type is a data type for which you provide the following information to the database server:

The internal structure of an opaque type is not visible to the database server. The internal structure can only be accessed through user-defined routines. Definitions for opaque types are stored in the sysxtdtypes system catalog table. The following SQL statements maintain the definitions of opaque types in the database:

For more information on the above-mentioned statements, see the Informix Guide to SQL: Syntax.

For information on how to create opaque types and an example of an opaque type, see the Extending INFORMIX-Universal Server: Data Types manual.

REAL

The REAL data type is a synonym for SMALLFLOAT.

SERIAL(n)

The SERIAL data type stores a sequential integer assigned automatically by the database server when a row is inserted. (For more information on inserting values into SERIAL columns, see the Informix Guide to SQL: Syntax.) A SERIAL data column is commonly used to store unique numeric codes (for example, order, invoice, or customer numbers). SERIAL data values require 4 bytes of storage.

The following restrictions apply to SERIAL columns:

If you are using the interactive schema editor in DB-Access to define the table, a unique index is applied automatically to a SERIAL column.

Assigning a Starting Value for SERIAL

The default serial starting number is 1, but you can assign an initial value, n, when you create or alter the table. If you specify 0 as your starting number when inserting the first row in a table with a SERIAL column, the database server assigns the value 1 to that row in the serial column, and increments the value by one at each subsequent insert. The highest serial number you can assign is 2,147,483,647. If you assign a number greater than 2,147,483,647, you receive a syntax error.

Tip: If you need to store assigned values larger than 2,147,483,647, use the SERIAL8 data type (see page 2-61).
Once a nonzero number is assigned, it cannot be changed. You can, however, insert a value into a SERIAL column (using the INSERT statement) or reset the serial value n (using the ALTER TABLE statement), as long as that value does not duplicate any existing values in the table. When you insert a number into a SERIAL column or reset the next value of a SERIAL column, your database server assigns the next number in sequence to the number entered. However, if you reset the next value of a SERIAL column to a value that is less than the values already in that column, the next value is computed using the following formula:

For example, if you reset the serial value of the customer_num column in the customer table to 50 and the highest-assigned customer number is 128, the next customer number assigned is 129.

Using SERIAL with INTEGER

The database server treats the SERIAL data type as a special case of the INTEGER data type. Therefore, all the arithmetic operators that are legal for INTEGER (such as +,-, *, and /) and all the SQL functions that are legal for INTEGER (such as ABS, MOD, POW, and so on) are also legal for SERIAL values. All data conversion rules that apply to INTEGER also apply to SERIAL.

The value of a SERIAL column of one table can be stored in the columns of another table. However, when the values of the SERIAL column are put into the second table, their values lose their constraints imposed by their original SERIAL column and they are stored as INTEGER values.

SERIAL8(n)

The SERIAL8 data type stores a sequential integer assigned automatically by the database server when a row is inserted. (For more information on how to insert values into SERIAL8 columns, see Chapter 1 of the Informix Guide to SQL: Syntax.) A SERIAL8 data column is commonly used to store large, unique numeric codes (for example, order, invoice, or customer numbers). SERIAL8 data values require 8 bytes of storage. The following restrictions apply to SERIAL8 columns:

If you are using the interactive schema editor in DB-Access to define the table, a unique index is applied automatically to a SERIAL8 column.

Assigning a Starting Value for SERIAL8

The default serial starting number is 1, but you can assign an initial value, n, when you create or alter the table. To start the values at 1 in a serial column of a table, give the value 0 for the SERIAL8 column when you insert rows into that table.The database server will assign the value 1 to the serial column of the first row of the table. The highest serial number you can assign is 263-1 (9,223,372,036,854,775,807). If you assign a number greater than this value, you receive a syntax error. When the database server generates a SERIAL8 value of this maximum number, it wraps around and starts generating values beginning at 1.

Once a nonzero number is assigned, it cannot be changed. You can, however, insert a value into a SERIAL8 column (using the INSERT statement) or reset the serial value n (using the ALTER TABLE statement), as long as that value does not duplicate any existing values in the table. When you insert a number into a SERIAL8 column or reset the next value of a SERIAL8 column, your database server assigns the next number in sequence to the number entered. However, if you reset the next value of a SERIAL8 column to a value that is less than the values already in that column, the next value is computed using the following formula:

For example, if you reset the serial value of the customer_num column in the customer table to 50 and the highest-assigned customer number is 128, the next customer number assigned is 129.

Using SERIAL8 with INT8

The database server treats the SERIAL8 data type as a special case of the INT8 data type. Therefore, all the arithmetic operators that are legal for INT8 (such as +, -, *, and /) and all the SQL functions that are legal for INT8 (such as ABS, MOD, POW, and so on) are also legal for SERIAL8 values. All data conversion rules that apply to INT8 also apply to SERIAL8.

The value of a SERIAL8 column of a table can be stored in the columns of another table. However, when the values of the SERIAL8 column are put into the second table, their values lose the constraints imposed by their original SERIAL8 column and they are stored as INT8 values.

SET(e)

The SET data type is a collection type that stores unique elements: it does not allow duplicate element values. (For a collection type that does allow duplicate values, see the description of the MULTISET in "Supported Data Types".) The elements in a SET have no ordinal position. That is, there is no concept of a first, second, or third element in a SET. (For a collection type with ordinal positions for elements, see the LIST data type on page 2-51.)

All elements in a SET have the same element type. To specify the element type, use the following syntax:

The element_type of a collection can be any of the following:

You must specify the NOT NULL constraint for SET elements. No other constraints are valid for SET columns. For more information on the syntax of the SET collection type, see the Data Type segment in the Informix Guide to SQL: Syntax.

You can use SET anywhere that you use any other data type, unless otherwise indicated. For example:

You cannot use the SET column with an aggregate function such as AVG, MAX, MIN, or SUM.

The following examples declare two sets: the first example declares a set of integers; the second declares a set of character elements:

The following examples construct the same sets from value lists:

In the following example, a SET constructor is part of a CREATE TABLE statement:

The following sets are equal:

SMALLFLOAT

 The SMALLFLOAT data type stores single-precision floating-point numbers with approximately eight significant digits. SMALLFLOAT corresponds to the float data type in C. The range of values for a SMALLFLOAT data type is the same as the range of values for the C float data type on your computer.

A SMALLFLOAT data-type column typically stores scientific numbers that can be calculated only approximately. Because floating-point numbers retain only their most significant digits, the number you enter in this type of column and the number the database displays might differ slightly depending on how your computer stores floating-point numbers internally. For example, you might enter a value of 1.1000001 into a SMALLFLOAT field and, after processing the SQL statement, the application development tool might display this value as 1.1. This difference occurs when a value has more digits than the floating-point number can store. In this case, the value is stored in its approximate form with the least significant digits treated as zeros.

SMALLFLOAT data types usually require 4 bytes per value.

SMALLINT

The SMALLINT data type stores small whole numbers that range from - 32,767 to 32,767. The maximum negative number, - 32,768, is a reserved value and cannot be used. The SMALLINT value is stored as a signed binary integer.

Integer columns typically store counts, quantities, and so on. Because the SMALLINT data type takes up only 2 bytes per value, arithmetic operations are performed very efficiently. However, this data type stores a limited range of values. If the values exceed the range between the minimum and maximum numbers, the database server does not store the value and provides you with an error message.

TEXT

The TEXT simple-large-object data type stores any kind of text data. It can contain both single and multi-byte characters. For more information on multibyte characters of the TEXT data type, see "Multibyte Characters with TEXT".

The TEXT data type has no maximum size. A TEXT column has a theoretical limit of 231 bytes and a practical limit determined by your available disk storage.

TEXT columns typically store memos, manual chapters, business documents, program source files, and so on. In the default locale U.S. ASCII English, a TEXT data type object of type TEXT can contain a combination of printable ASCII characters and the following control characters:

You can store, retrieve, update, or delete the contents of a TEXT column. However, you cannot use TEXT data items in arithmetic or string operations, or assign literals to TEXT items with the SET clause of the UPDATE statement. You also cannot use TEXT items in the following ways:

You can use TEXT objects in Boolean expressions only if you are testing for null values.

You can insert data into TEXT columns in the following ways:

You cannot use a quoted text string, number, or any other actual value to insert or update TEXT columns.

When you select a TEXT column, you can choose to receive all or part of it. To see all of a column, use the regular syntax for selecting a column into a variable. You also can select any part of a TEXT column by using subscripts, as shown in the following example:

This statement reads the first 75 bytes of the cat_descr column associated with catalog number 10001.

Nonprintable Characters with TEXT

Both printable and nonprintable characters can be inserted into text columns. Informix products do not check the data that is inserted into a column with the TEXT data type. For detailed information on entering and displaying nonprintable characters, refer to "Nonprintable Characters with CHAR".

Multibyte Characters with TEXT

GLS
Multibyte TEXT characters must be supported by the database locale. For more information, see the Guide to GLS Functionality.

Collating TEXT Data

GLS
TEXT data type is collated in code-set order. For more information on collation orders, see the Guide to GLS Functionality.

Unnamed Row Type

An unnamed row type contains fields, but has no user-defined name. An unnamed row type is defined by its structure. Two unnamed row types are equal if they have the same structure. If two unnamed row types have the same number of fields, and if the data type of each field in one row type matches the data type of the corresponding field in the other row type, the two unnamed row types are equal.

For example, the following unnamed row types are equal:

The following row types are not equivalent, even though they have the same number of fields and the same data types, because the fields are not in the same order:

The data type of the field of an unnamed row type can be any of the following:

For more information on defining unnamed row types, see the Data Type segment in the Informix Guide to SQL: Syntax.

Unnamed row types cannot be used in type tables or in type inheritance hierarchies.

Defining Unnamed Row Types

You can create an unnamed row type in several ways:

ROW(1, 'abc', 5.30)

ROW (x INTEGER, y VARCHAR, z DECIMAL)

Inserting Values into Unnamed Row Type Columns

 When you specify field values for an unnamed row type, list the field values after the constructor and between parentheses. For example, suppose you have an unnamed row-type column with the following type:

The following INSERT statement adds one group of field values to this ROW column:

You can specify a ROW column in the IN predicate in the WHERE clause of a SELECT statement to search for matching ROW values. For more information, see the Condition segment in the Informix Guide to SQL: Syntax.

VARCHAR(m,r)

The VARCHAR data type stores single-byte and multibyte character sequences of varying length, where m is the maximum byte size of the column and r is the minimum amount of byte space reserved for that column. For more information on multibyte VARCHAR sequences, see "Multibyte Characters with VARCHAR".

The VARCHAR data type is the Informix implementation of a character-varying data type.

The ANSI-standard data type for varying character strings is CHARACTER VARYING, described on page 2-38.

You must specify the maximum size (m) of the VARCHAR column. The size of this parameter can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store shorter, but not longer, character strings than the value you specify.

Specifying the minimum reserved space (r) parameter is optional. This value can range from 0 to 255 bytes but must be less than the maximum size (m) of the VARCHAR column. If you do not specify a minimum space value, it defaults to 0. You should specify this parameter when you initially intend to insert rows with short or null data in this column, but later expect the data to be updated with longer values.

Although the use of VARCHAR economizes on space used in a table, it has no effect on the size of an index. In an index based on a VARCHAR column, each index key has length m, the maximum size of the column.

When you store a VARCHAR value in the database, only its defined characters are stored. The database server does not strip a VARCHAR object of any user-entered trailing blanks, nor does the database server pad the VARCHAR to the full length of the column. However, if you specify a minimum reserved space (r) and some data values are shorter than that amount, some space reserved for rows goes unused.

VARCHAR values are compared to other VARCHAR values and to character values in the same way that character values are compared. The shorter value is padded on the right with spaces until the values have equal lengths; then they are compared for the full length.

Multibyte Characters with VARCHAR

GLS
Multibyte VARCHAR characters must be supported by the database locale. If you are storing multibyte characters, make sure to calculate the number of bytes needed. For more information, see the Guide to GLS Functionality.

Collating VARCHAR

GLS
The main difference between the NVARCHAR and the VARCHAR data types is the difference in collation sequencing. The collation order of NVARCHAR characters depends on the GLS locale chosen, while collation of VARCHAR characters depends on the code set. For more information, see the Guide to GLS Functionality.

Nonprintable Characters with VARCHAR

Nonprintable VARCHAR characters are entered, displayed, and treated in the same way as nonprintable CHAR characters are. For detailed information on entering and displaying nonprintable characters, refer to "Nonprintable Characters with CHAR".

Storing Numeric Values in a VARCHAR Column

When you insert a numeric value into a VARCHAR column, the stored value does not get padded with trailing blanks to the maximum length of the column. The number of digits in a numeric VARCHAR value is the number of characters that you need to store that value. For example, given the following statement, the value that gets stored in table mytab is 1.

create table mytab (col1 varchar(10));

insert into mytab values (1);

Tip: VARCHAR treats C null (binary 0) and string terminators as termination characters for nonprintable characters.



Informix Guide to SQL: Reference, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.