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.
Because a character expression is being compared to an integer expression, the character expression is implicitly cast as an integer.
SELECT * FROM tbl WHERE <character expression> > <integer expression>;
castfunction 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;
- 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 errorFor 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.