Summary of Netezza casting

To do an explicit cast, use the CAST operator as follows:

   CAST (<from-type> AS <to-type>)

For example:

   CAST ( <int2-column> AS NUMERIC(12,3) )

The IBM® Netezza® system can create an implicit cast in an expression that involves functions or operators. For example, the Netezza built-in function SQRT takes a float8 (that is, double) argument. For example, consider the following expression:
```
    SQRT ( <int4-column> )
```
The system does an implicit cast, converting the sample expression as follows:
```
    SQRT ( CAST ( <int4-column> AS FLOAT8 ) )
```
As another example, consider the following expression:
```
   <varchar-column> + <int4-column>
```
The system implicitly casts the varchar column to an int4 type by transforming the expression as follows:
```
   CAST ( <varchar-column> AS INT4 ) + <int4-column>
```
Casting can also help to avoid situations where some operations can result in an overflow error for the query. For example, if you add a large byteint value to another large byteint value, the result could overflow the resulting byteint value. To avoid the overflow error, you could cast the result to a larger value such as a smallint or integer data type.
In general, it is better to use explicit casts rather than rely on the implicit casting behavior of the system. The implicit cast choice that is made by the system might not yield the behavior that you want. Also, implicit casting behavior can change from one release of Netezza to another. In the previous example where a varchar is added to an int4, the system chose an implicit cast from varchar to int4. But if your varchar column contained strings that represent numbers with decimal points, as in '25.7', the cast to integer would generate an error.
When the system runs an INSERT or UPDATE statement, the Netezza system implicitly casts the values that are inserted into table columns to the types of those columns, as needed.
When the system compares a string to a non-string value such as a numeric, integer, or timestamp, the Netezza system implicitly casts the string value to the type of the non-string value.
When the system converts a string to a float4 or float8 value, the SQL system accepts the string values infinity and not-a-number (NaN).
When the system converts a string to a float4 or float8 value, and when you are loading data into a float4 or float8 column, the SQL system accepts values which are very close to zero. In previous releases, the system returns an error for values which are so small that they cannot be distinguished from zero. Those values are now supported during loads and produce the value 0.0.
When the system compares a string to a NUMERIC type, or conducts an arithmetic operation on a string and a NUMERIC type, the Netezza system implicitly casts the string to a NUMERIC of the same precision and scale as those of the second operand. Therefore, if your string contains data that requires a different precision and scale, use explicit casting.
When the system concatenates a string and a non-string type such as a numeric, integer, or timestamp, the Netezza system implicitly casts the non-string to a varchar.
When the system casts from a Unicode type (nchar or nvarchar) to an 8-bit Latin 9 character type (char or varchar), the Netezza system converts any characters that do not exist in Latin 9 encoding into a question mark character ('?').
Arithmetic operations on a temporal type (timestamp, date, interval) along with a string type can produce unexpected results. Therefore, use explicit casts as needed in such expressions.