SQLis a strongly typed language. That is, every data item has an associated data type which determines its behavior and allowed usage.PostgreSQLhas an extensible type system that is more general and flexible than otherSQLimplementations. Hence, most type conversion behavior inPostgreSQLis governed by general rules rather than by_ad hoc_heuristics. This allows the use of mixed-type expressions even with user-defined types.
ThePostgreSQLscanner/parser divides lexical elements into five fundamental categories: integers, non-integer numbers, strings, identifiers, and key words. Constants of most non-numeric types are first classified as strings. TheSQLlanguage definition allows specifying type names with strings, and this mechanism can be used inPostgreSQLto start the parser down the correct path. For example, the query:
has two literal constants, of typetextandpoint. If a type is not specified for a string literal, then the placeholder typeunknownis assigned initially, to be resolved in later stages as described below.
There are four fundamentalSQLconstructs requiring distinct type conversion rules in thePostgreSQLparser:
Function calls
Much of thePostgreSQLtype system is built around a rich set of functions. Functions can have one or more arguments. SincePostgreSQLpermits function overloading, the function name alone does not uniquely identify the function to be called; the parser must select the right function based on the data types of the supplied arguments.
Operators
PostgreSQLallows expressions with prefix and postfix unary (one-argument) operators, as well as binary (two-argument) operators. Like functions, operators can be overloaded, so the same problem of selecting the right operator exists.
Value Storage
SQLINSERTandUPDATEstatements place the results of expressions into a table. The expressions in the statement must be matched up with, and perhaps converted to, the types of the target columns.
UNION
,
CASE
, and related constructs
Since all query results from a unionizedSELECTstatement must appear in a single set of columns, the types of the results of eachSELECTclause must be matched up and converted to a uniform set. Similarly, the result expressions of aCASEconstruct must be converted to a common type so that theCASEexpression as a whole has a known output type. The same holds forARRAYconstructs, and for theGREATESTandLEASTfunctions.
The system catalogs store information about which conversions, orcasts, exist between which data types, and how to perform those conversions. Additional casts can be added by the user with theCREATE CASTcommand. (This is usually done in conjunction with defining new data types. The set of casts between built-in types has been carefully crafted and is best not altered.)
An additional heuristic provided by the parser allows improved determination of the proper casting behavior among groups of types that have implicit casts. Data types are divided into several basictype categories, includingboolean,numeric,string,bitstring,datetime,timespan,geometric,network, and user-defined. (For a list seeTable 51.63; but note it is also possible to create custom type categories.) Within each category there can be one or morepreferred types, which are preferred when there is a choice of possible types. With careful selection of preferred types and available implicit casts, it is possible to ensure that ambiguous expressions (those with multiple candidate parsing solutions) can be resolved in a useful way.
All type conversion rules are designed with several principles in mind:
Implicit conversions should never have surprising or unpredictable outcomes.
There should be no extra overhead in the parser or executor if a query does not need implicit type conversion. That is, if a query is well-formed and the types already match, then the query should execute without spending extra time in the parser and without introducing unnecessary implicit conversion calls in the query.
Additionally, if a query usually requires an implicit conversion for a function, and if then the user defines a new function with the correct argument types, the parser should use this new function and no longer do implicit conversion to use the old function.