Quoted and mixed literals

SQL statements can contain quoted literals '...' (single quotation mark - series of characters - single quotation mark). A quoted literal is a constant that is expressed as itself rather than as a result of an expression, such as an arithmetic formula.

Netezza Performance Server SQL considers quoted literals typeless unless they have an associated data type keyword, or are explicitly typecast. When Netezza Performance Server SQL cannot readily determine the data type, it deduces the type from the context.
  • Quoted literals with a keyword: date '2004-Mar-09'
  • Quoted literals with explicit typecasting: cast('2004-Mar-09' as date) or '2004-Mar-09'::date
  • Quoted literals whose type is determined by context: date_column = '2004-Mar-09'

    This type is an unknown type until Netezza Performance Server SQL compares it to date column, then Netezza Performance Server SQL assigns it to the date type and parses the string as a date constant.

When literals mix integers and numerics in a statement, Netezza Performance Server SQL must determine how to parse them.
  • Quoted literals that mix integers and numerics: integer_column = '5' OR integer_column = '5.4'

    Netezza Performance Server SQL treats quoted literals with decimal points as numerics when it compares them to integers and thus allows this syntax.

  • Unquoted literals that mix integers and numerics: integer_column = 5 OR integer_column = 5.4

    The parser recognizes the constants as integer and numeric. To run the clause <integer_column = 5.4>, the parser promotes the integer_column to numeric type because the built-in promotion rules states that the common data type of integer and numeric is numeric. Netezza Performance Server SQL allows this syntax and runs the clause as if it is <integer_column::numeric = 5.4>.