PolarDB for Oracle uses the PostgreSQL type system, which is more general and flexible than other SQL implementations. Most type conversion behavior follows general rules rather than ad hoc heuristics, so mixed-type expressions work reliably—including with user-defined types.
In most cases, you don't need to understand type conversion in detail. However, implicit conversions can affect query results in ways that aren't immediately obvious. When that happens, use explicit type conversion to get predictable behavior.
How type conversion works
The scanner/parser divides lexical elements into five fundamental categories: integers, non-integer numbers, strings, identifiers, and keywords. Constants of most non-numeric types are first classified as strings. If no type is specified for a string literal, the placeholder type unknown is assigned and resolved in a later parsing stage.
The following query specifies types explicitly using string literal syntax:
SELECT text 'Origin' AS "label", point '(0,0)' AS "value"; label | value
--------+-------
Origin | (0,0)
(1 row)The two literal constants are typed as text and point because the type name precedes the string.
SQL constructs that require type conversion
Four SQL constructs require distinct type conversion rules in the parser.
Function calls
Functions can have one or more arguments. Because function overloading is supported, the function name alone does not uniquely identify which function to call. The parser selects the correct function based on the data types of the supplied arguments.
Operators
Prefix (one-argument) and infix (two-argument) operators are supported. Like functions, operators can be overloaded, so the parser applies the same resolution logic to select the correct operator.
Value storage
INSERT and UPDATE statements place the results of expressions into table columns. The expression types must match, or be convertible to, the target column types.
UNION, CASE, and related constructs
All query results from a unionized SELECT must appear in a single set of columns, so the result types of each SELECT clause are matched and converted to a uniform set. Similarly, the result expressions of a CASE construct must resolve to a common type. ARRAY[], GREATEST, and LEAST apply the same logic across their subexpressions.
Casts and type categories
System catalogs store information about which casts exist between data types and how to perform them.
Use CREATE CAST to add custom casts when defining new data types. The set of casts between built-in types is carefully designed—avoid altering it.
Data types are divided into basic type categories: boolean, numeric, string, bitstring, datetime, timespan, geometric, network, and user-defined. Within each category there can be one or more preferred types. With careful selection of preferred types and available implicit casts, ambiguous expressions—those with multiple valid parsing solutions—are resolved in a predictable way.
Type conversion design principles
All type conversion rules follow three principles:
-
Implicit type conversions never produce surprising or unpredictable outcomes.
-
If a query is well-formed and types already match, the query executes without extra parser overhead or unnecessary implicit conversion calls.
-
If a user defines a new function with the correct argument types, the parser uses that function instead of implicitly converting to use an older one.