Explicit and Implicit Casts

To process queries with multiple data types often requires casts that convert data from one data type to another. You can use the CREATE CAST statement to create the following kinds of casts:

• Use the CREATE EXPLICIT CAST statement to define an explicit cast.
• Use the CREATE IMPLICIT CAST statement to define an implicit cast.

Explicit Casts

An explicit cast is a cast that you must specifically invoke, with either the CAST AS keywords or with the cast operator ( :: ). The database server does not automatically invoke an explicit cast to resolve data type conversions. The EXPLICIT keyword is optional; by default, the CREATE CAST statement creates an explicit cast.

The following CREATE CAST statement defines an explicit cast from the rate_of_return opaque data type to the percent distinct data type:

CREATE EXPLICIT CAST (rate_of_return AS percent 
   WITH rate_to_prcnt)

The following SELECT statement explicitly invokes this explicit cast in its WHERE clause to compare the bond_rate column (of type rate_of_return) to the initial_APR column (of type percent):

SELECT bond_rate FROM bond
WHERE bond_rate::percent > initial_APR

Implicit Casts

The database server invokes built-in casts to convert from one built-in data type to another built-in type that is not directly substitutable. For example, the database server performs conversion of a character type such as CHAR to a numeric type such as INTEGER through a built-in cast.

An implicit cast is a cast that the database server can invoke automatically when it encounters data types that cannot be compared with built-in casts. This type of cast enables the database server to automatically handle conversions between other data types.

To define an implicit cast, specify the IMPLICIT keyword in the CREATE CAST statement. For example, the following CREATE CAST statement specifies that the database server should automatically use the prcnt_to_char( ) function to convert from the CHAR data type to a distinct data type, percent:

CREATE IMPLICIT CAST (CHAR AS percent WITH char_to_prcnt) 

This cast only supports automatic conversion from the CHAR data type to percent. For the database server to convert from percent to CHAR, you also need to define another implicit cast, as follows:

CREATE IMPLICIT CAST (percent AS CHAR WITH prcnt_to_char) 

The database server automatically invokes the char_to_prcnt( ) function to evaluate the WHERE clause of the following SELECT statement:

SELECT commission FROM sales_rep WHERE commission > "25%"

Users can also invoke implicit casts explicitly. For more information on how to explicitly invoke a cast function, see Explicit Casts.

When a built-in cast does not exist for conversion between data types, you can create user-defined casts to make the necessary conversion.