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 Netezza Performance Server 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>;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.