Informix Guide to SQL: Reference Data Types

Occasionally, the data type that was assigned to a column with the CREATE TABLE statement is inappropriate. You might want to change the data type of a column when you need to store larger values than the current data type can accommodate. The database server allows you to change the data type of the column or to cast its values to a different data type with either of the following methods:

• Use the ALTER TABLE statement to modify the data type of a column.

For example, if you create a SMALLINT column and later find that you need to store integers larger than 32,767, you must change the data type of that column to store the larger value. You can use ALTER TABLE to change the data type to INTEGER. The conversion changes the data type of all values that currently exist in the column as well as any new values that might be added.

• Use the CAST AS keywords or the double colon (::) cast operator to cast a value to a different data type.

Casting does not permanently alter the data type of a value; it expresses the value in a more convenient form. Casting user-defined data types into built-in types allows client programs to manipulate data types without knowledge of their internal structure.

If you change data types, the new data type must be able to store all the old values. For example, if you try to convert a column from the INTEGER data type to the SMALLINT data type and the following values exist in the INTEGER column, the database server does not change the data type because SMALLINT columns cannot accommodate numbers greater than 32,768:

100	400	700	50000	700

The same situation might occur if you attempt to transfer data from FLOAT or SMALLFLOAT columns to INTEGER, SMALLINT, or DECIMAL columns.

Both data type conversion and casting depend on casts defined in the syscasts system catalog table. For information about syscasts, see SYSCASTS.

A cast is either built-in or user-defined. Guidelines exist for casting distinct and extended data types.

For more information about casting opaque types, see Extending Informix Dynamic Server 2000. For information about casting other extended types, see the Informix Guide to Database Design and Implementation.

User informix owns built-in casts. They govern conversions from one built-in data type to another. Built-in casts allow the database server to convert:

• A character type to any other character type
• A character type to or from any other built-in type
• A numeric type to any other numeric type
• A time data type to or from a datetime type

The database server automatically invokes the appropriate built-in casts when required. An infinite number of built-in casts might be invoked to evaluate and compare expressions or to change a column from one built-in data type to another.

When you convert a column from one built-in data type to another, the database server applies the appropriate built-in casts to each value already in the column. If the new data type cannot store any of the resulting values, the ALTER TABLE statement fails.

For example, if you try to convert a column from the INTEGER data type to the SMALLINT data type and the following values exist in the INTEGER column, the database server does not change the data type because SMALLINT columns cannot accommodate numbers greater than 32,767:

100	400	700	50000	700

The same situation might occur if you attempt to transfer data from FLOAT or SMALLFLOAT columns to INTEGER, SMALLINT, or DECIMAL columns.

The following sections describe database server behavior during certain types of casts and conversions.

When you convert data from one number data type to another, you occasionally find rounding errors. Figure 2-14 on page 2-65 indicates which numeric data type conversions are acceptable and what kinds of errors you can encounter when you convert between certain numeric data types.

For example, if you convert a FLOAT value to DECIMAL(4,2), your database server rounds off the floating-point numbers before storing them as decimal numbers. This conversion can result in an error depending on the precision assigned to the DECIMAL column.

You can convert a CHAR (or NCHAR) column to a numeric column. However, if the CHAR or NCHAR column contains any characters that are not valid in a number column (for example, the letter l instead of the number 1), your database server returns an error.

You can also convert a numeric column to a character column. However, if the character column is not large enough to receive the number, the database server generates an error.

If the database server generates an error, it cannot complete the ALTER TABLE statement or cast and leaves the column values as characters. You receive an error message and the statement is rolled back (whether you are in a transaction or not).

You can convert an integer column (SMALLINT, INTEGER, or INT8) to a DATE or DATETIME value. The database server interprets the integer as a value in the internal format of the DATE or DATETIME column. You can also convert a DATE or DATETIME column to an integer column. The database server stores the internal format of the DATE or DATETIME column as an integer.

For a DATE column, the internal format is a Julian date. For a DATETIME column, the internal format stores the date and time in a condensed integer format.

You can convert DATE columns to DATETIME columns. However, if the DATETIME column contains more fields than the DATE column, the database server either ignores the fields or fills them with zeros. The illustrations in the following list show how these two data types are converted (assuming that the default date format is mm/dd/yyyy):

• If you convert DATE to DATETIME YEAR TO DAY, the database server converts the existing DATE values to DATETIME values. For example, the value 08/15/1999 becomes 1999-08-15.
• If you convert DATETIME YEAR TO DAY to the DATE format, the value 1999-08-15 becomes 08/15/1999.
• If you convert DATE to DATETIME YEAR TO SECOND, the database server converts existing DATE values to DATETIME values and fills in the additional DATETIME fields with zeros. For example, 08/15/1999 becomes 1999-08-15 00:00:00.
• If you convert DATETIME YEAR TO SECOND to DATE, the database server converts existing DATETIME to DATE values but drops fields more precise than DAY. For example, 1999-08-15 12:15:37 becomes 08/15/1999.

Implicit and explicit casts are owned by the users who create them. They govern casts and conversions between user-defined data types and other data types.

Developers of user-defined data types must create certain implicit and explicit casts and the functions that are used to implement them. The casts allow user-defined types to be expressed in a form that clients can manipulate.

For information on how to create and use implicit and explicit casts, see the CREATE CAST statement in the Informix Guide to SQL: Syntax and the Informix Guide to Database Design and Implementation.

The database server automatically invokes a single implicit cast when needed to evaluate and compare expressions or pass arguments. Operations that require more than one implicit cast fail.

Implicit casts allow you to convert a user-defined data type to a built-in type or vice versa.

Users can explicitly invoke an implicit cast using the CAST AS keywords or the double colon (::) cast operator.

Explicit casts, unlike implicit casts or built-in casts, are never invoked automatically by the database server. Users must invoke them explicitly with the CAST AS keywords or the double colon (::) cast operator.

Explicit casts do not allow you to convert a user-defined data type to a built-in data type or vice versa.

The database server uses the following rules to determine which cast to apply in a particular situation:

• To compare two built-in types, the database server automatically invokes the appropriate built-in casts.
• The database server applies only one implicit cast per operand. If two or more casts are needed to convert the operand to the desired type, the user must explicitly invoke the additional casts.
• To compare a distinct type to its source type, the user must explicitly cast one type to the other.
• To compare a distinct type to a type other than its source, the database server looks for an implicit cast between the source type and the desired type

If neither cast is registered, the user must invoke an explicit cast between the distinct type and the desired type. If this cast is not registered, the database server automatically invokes a cast from the source type to the desired type.

If none of these casts is defined, the comparison fails.

• To compare an opaque type to a built-in type, the user must explicitly cast the opaque type to a form that the database server understands (LVARCHAR, SENDRECV, IMPEX, or IMPEXBIN). The database server then invokes built-in casts to convert the results to the desired built-in type.
• To compare two opaque types, the user must explicitly cast one opaque type to a form that the database server understands (LVARCHAR, SENDRECV, IMPEX, or IMPEXBIN), then explicitly cast this type to the second opaque type.

For information about casting and the IMPEX, IMPEXBIN, LVARCHAR, and SENDRECV data types, see Extending Informix Dynamic Server 2000.

You define a distinct type based on a built-in type or an existing opaque type or row type. Although data of the distinct type has the same length and alignment and is passed in the same way as data of the source type, the two cannot be compared directly. To compare a distinct type and its source type, you must explicitly cast one type to the other.

When you create a new distinct type, the database server automatically registers two explicit casts:

• A cast from the distinct type to its source type
• A cast from the source type to the distinct type

You can create an implicit cast between a distinct type and its source type. However, to create an implicit cast, you must first drop the default explicit cast between the distinct type and its source type.

You also can use all casts that have been registered for the source type without modification on the distinct type. You can also define new casts and support functions that apply only to the distinct type.

For examples that show how to create a cast function for a distinct type and register the function as cast, see the Informix Guide to Database Design and Implementation.

The following table shows the data type combinations that you can cast. The table shows only whether or not a cast between a source type and a target type are possible. In some cases, you must first create a user-defined cast before you can perform a conversion between two data types. In other cases, the database server automatically provides a cast that is implicitly invoked or that you must explicitly invoke.