Explicit and implicit casting

Implicit casting is the use of internal rules to try to evaluate and process statements that contain mixed data types. This can be helpful when using a function that expects a particular data type, or when issuing a query that compares values that are similar but of different data types. The IBM® Netezza® SQL language is aligned with the SQL Standard for implicit casting of data types.

For example, in the following statement, the two expressions can be column names, literals, or more complex expressions:
SELECT * FROM tbl WHERE <character expression> > <integer expression>;
Because a character expression is being compared to an integer expression, the character expression is implicitly cast as an integer.
Sometimes implicit casting fails because data cannot be parsed in such a way that it conforms to the new data type. (For example, the character string 'abc' cannot be parsed as either an integer or a date.) In these cases, use the cast function to explicitly cast the data to the appropriate data type. For example, if the column age contains character data that represents integer values, cast that data as integer data like this:
SELECT * FROM tbl WHERE cast(age as integer) > 3;
Examples of error messages that are issued when implicit casting fails:
  • When an expression cannot be parsed as an integer:
    ERROR: pg_atoi: error in "abc": can't parse "abc"
  • When a character expression cannot be parsed as a date value:
    ERROR: Bad date external representation 'abc'
    This error can result from such statements as:
    • SELECT * FROM tbl WHERE <date expression> = <character expression>;
    • INSERT INTO <table having one column of type date> SELECT <character expression> FROM <another table>;
  • When a character expression cannot be parsed as a numeric value:
    ERROR: 65524: Precision range error
    For example, when an expression attempts to multiply a numeric(24,4) by a varchar(50), the system attempts to implicitly cast the varchar to a numeric(24,4). However, if the varchar contains too many digits this can cause a precision range error.
If you receive one of these error messages, look for a situation in which a particular data type is invalid for the operation that you are attempting.