Data Type Casting and Conversion

About this task

Occasionally, the data type that was assigned to a column with the CREATE TABLE statement is inappropriate. You can change the data type of a column when you are required 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 must 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 and 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 of the old value.

Both data-type conversion and casting depend on casts registered 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 data types, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide. For information about casting other extended data types see, the HCL OneDB Database Design and Implementation Guide.