1 <chapter Id="typeconv">
2 <title>Type Conversion</title>
5 <acronym>SQL</acronym> queries can, intentionally or not, require
6 mixing of different data types in the same expression.
7 <productname>Postgres</productname> has extensive facilities for
8 evaluating mixed-type expressions.
12 In many cases a user will not need
13 to understand the details of the type conversion mechanism.
14 However, the implicit conversions done by <productname>Postgres</productname>
15 can affect the results of a query. When necessary, these results
16 can be tailored by a user or programmer
17 using <emphasis>explicit</emphasis> type coercion.
21 This chapter introduces the <productname>Postgres</productname>
22 type conversion mechanisms and conventions.
23 Refer to the relevant sections in the User's Guide and Programmer's Guide
24 for more information on specific data types and allowed functions and
29 The Programmer's Guide has more details on the exact algorithms used for
30 implicit type conversion and coercion.
33 <sect1 id="typeconv-overview">
34 <title>Overview</title>
37 <acronym>SQL</acronym> is a strongly typed language. That is, every data item
38 has an associated data type which determines its behavior and allowed usage.
39 <productname>Postgres</productname> has an extensible type system that is
40 much more general and flexible than other <acronym>RDBMS</acronym> implementations.
41 Hence, most type conversion behavior in <productname>Postgres</productname>
42 should be governed by general rules rather than by ad-hoc heuristics to allow
43 mixed-type expressions to be meaningful, even with user-defined types.
47 The <productname>Postgres</productname> scanner/parser decodes lexical
48 elements into only five fundamental categories: integers, floats, strings,
49 names, and keywords. Most extended types are first tokenized into
50 strings. The <acronym>SQL</acronym> language definition allows specifying type
51 names with strings, and this mechanism can be used in
52 <productname>Postgres</productname> to start the parser down the correct
53 path. For example, the query
56 tgl=> SELECT text 'Origin' AS "Label", point '(0,0)' AS "Value";
63 has two strings, of type <type>text</type> and <type>point</type>.
64 If a type is not specified for a string, then the placeholder type
65 <firstterm>unknown</firstterm> is assigned initially, to be resolved in later
66 stages as described below.
70 There are four fundamental <acronym>SQL</acronym> constructs requiring
71 distinct type conversion rules in the <productname>Postgres</productname>
82 <productname>Postgres</productname> allows expressions with
83 left- and right-unary (one argument) operators,
84 as well as binary (two argument) operators.
94 Much of the <productname>Postgres</productname> type system is built around a
95 rich set of functions. Function calls have one or more arguments which, for
96 any specific query, must be matched to the functions available in the system
97 catalog. Since <productname>Postgres</productname> permits function
98 overloading, the function name alone does not uniquely identify the function
99 to be called --- the parser must select the right function based on the data
100 types of the supplied arguments.
110 <acronym>SQL</acronym> INSERT and UPDATE statements place the results of
111 expressions into a table. The expressions in the query must be matched up
112 with, and perhaps converted to, the types of the target columns.
118 UNION and CASE constructs
122 Since all select results from a UNION SELECT statement must appear in a single
123 set of columns, the types of the results
124 of each SELECT clause must be matched up and converted to a uniform set.
125 Similarly, the result expressions of a CASE construct must be coerced to
126 a common type so that the CASE expression as a whole has a known output type.
133 Many of the general type conversion rules use simple conventions built on
134 the <productname>Postgres</productname> function and operator system tables.
135 There are some heuristics included in the conversion rules to better support
136 conventions for the <acronym>SQL92</acronym> standard native types such as
137 <type>smallint</type>, <type>integer</type>, and <type>float</type>.
141 The <productname>Postgres</productname> parser uses the convention that all
142 type conversion functions take a single argument of the source type and are
143 named with the same name as the target type. Any function meeting these
144 criteria is considered to be a valid conversion function, and may be used
145 by the parser as such. This simple assumption gives the parser the power
146 to explore type conversion possibilities without hardcoding, allowing
147 extended user-defined types to use these same features transparently.
151 An additional heuristic is provided in the parser to allow better guesses
152 at proper behavior for <acronym>SQL</acronym> standard types. There are
153 several basic <firstterm>type categories</firstterm> defined: <type>boolean</type>,
154 <type>numeric</type>, <type>string</type>, <type>bitstring</type>, <type>datetime</type>, <type>timespan</type>, <type>geometric</type>, <type>network</type>,
155 and user-defined. Each category, with the exception of user-defined, has
156 a <firstterm>preferred type</firstterm> which is preferentially selected
157 when there is ambiguity.
158 In the user-defined category, each type is its own preferred type.
159 Ambiguous expressions (those with multiple candidate parsing solutions)
160 can often be resolved when there are multiple possible built-in types, but
161 they will raise an error when there are multiple choices for user-defined
166 <title>Guidelines</title>
169 All type conversion rules are designed with several principles in mind:
171 <itemizedlist mark="bullet" spacing="compact">
174 Implicit conversions should never have surprising or unpredictable outcomes.
180 User-defined types, of which the parser has no a-priori knowledge, should be
181 "higher" in the type hierarchy. In mixed-type expressions, native types shall always
182 be converted to a user-defined type (of course, only if conversion is necessary).
188 User-defined types are not related. Currently, <productname>Postgres</productname>
189 does not have information available to it on relationships between types, other than
190 hardcoded heuristics for built-in types and implicit relationships based on available functions
197 There should be no extra overhead from the parser or executor
198 if a query does not need implicit type conversion.
199 That is, if a query is well formulated and the types already match up, then the query should proceed
200 without spending extra time in the parser and without introducing unnecessary implicit conversion
201 functions into the query.
205 Additionally, if a query usually requires an implicit conversion for a function, and
206 if then the user defines an explicit function with the correct argument types, the parser
207 should use this new function and will no longer do the implicit conversion using the old function.
215 <sect1 id="typeconv-oper">
216 <title>Operators</title>
219 <title>Operator Type Resolution</title>
221 <step performance="required">
223 Check for an exact match in the pg_operator system catalog.
227 <step performance="optional">
229 If one argument of a binary operator is <type>unknown</type> type,
230 then assume it is the same type as the other argument for this check.
231 Other cases involving <type>unknown</type> will never find a match at
238 <step performance="required">
240 Look for the best match.
243 <step performance="required">
245 Make a list of all operators of the same name for which the input types
246 match or can be coerced to match. (<type>unknown</type> literals are
247 assumed to be coercible to anything for this purpose.) If there is only
248 one, use it; else continue to the next step.
251 <step performance="required">
253 Run through all candidates and keep those with the most exact matches
254 on input types. Keep all candidates if none have any exact matches.
255 If only one candidate remains, use it; else continue to the next step.
258 <step performance="required">
260 Run through all candidates and keep those with the most exact or
261 binary-compatible matches on input types. Keep all candidates if none have
262 any exact or binary-compatible matches.
263 If only one candidate remains, use it; else continue to the next step.
266 <step performance="required">
268 Run through all candidates and keep those that accept preferred types at
269 the most positions where type coercion will be required.
270 Keep all candidates if none accept preferred types.
271 If only one candidate remains, use it; else continue to the next step.
274 <step performance="required">
276 If any input arguments are <quote>unknown</quote>, check the type categories accepted
277 at those argument positions by the remaining candidates. At each position,
279 category if any candidate accepts that category (this bias towards string
280 is appropriate since an unknown-type literal does look like a string).
281 Otherwise, if all the remaining candidates accept the same type category,
282 select that category; otherwise fail because
283 the correct choice cannot be deduced without more clues. Also note whether
284 any of the candidates accept a preferred data type within the selected category.
285 Now discard operator candidates that do not accept the selected type category;
286 furthermore, if any candidate accepts a preferred type at a given argument
287 position, discard candidates that accept non-preferred types for that
291 <step performance="required">
293 If only one candidate remains, use it. If no candidate or more than one
303 <title>Examples</title>
306 <title>Exponentiation Operator</title>
309 There is only one exponentiation
310 operator defined in the catalog, and it takes arguments of type
311 <type>double precision</type>.
312 The scanner assigns an initial type of <type>int4</type> to both arguments
313 of this query expression:
315 tgl=> select 2 ^ 3 AS "Exp";
322 So the parser does a type conversion on both operands and the query
326 tgl=> select CAST(2 AS double precision) ^ CAST(3 AS double precision) AS "Exp";
336 tgl=> select 2.0 ^ 3.0 AS "Exp";
345 This last form has the least overhead, since no functions are called to do
346 implicit type conversion. This is not an issue for small queries, but may
347 have an impact on the performance of queries involving large tables.
354 <title>String Concatenation</title>
357 A string-like syntax is used for working with string types as well as for
358 working with complex extended types.
359 Strings with unspecified type are matched with likely operator candidates.
363 One unspecified argument:
365 tgl=> SELECT text 'abc' || 'def' AS "Text and Unknown";
374 In this case the parser looks to see if there is an operator taking <type>text</type>
375 for both arguments. Since there is, it assumes that the second argument should
376 be interpreted as of type <type>text</type>.
380 Concatenation on unspecified types:
382 tgl=> SELECT 'abc' || 'def' AS "Unspecified";
391 In this case there is no initial hint for which type to use, since no types
392 are specified in the query. So, the parser looks for all candidate operators
393 and finds that there are candidates accepting both string-category and
394 bit-string-category inputs. Since string category is preferred when available,
395 that category is selected, and then the
396 "preferred type" for strings, <type>text</type>, is used as the specific
397 type to resolve the unknown literals to.
402 <title>Factorial</title>
405 This example illustrates an interesting result. Traditionally, the
406 factorial operator is defined for integers only. The <productname>Postgres</productname>
407 operator catalog has only one entry for factorial, taking an integer operand.
408 If given a non-integer numeric argument, <productname>Postgres</productname>
409 will try to convert that argument to an integer for evaluation of the
413 tgl=> select (4.3 !);
422 Of course, this leads to a mathematically suspect result,
423 since in principle the factorial of a non-integer is not defined.
424 However, the role of a database is not to teach mathematics, but
425 to be a tool for data manipulation. If a user chooses to take the
426 factorial of a floating point number, <productname>Postgres</productname>
435 <sect1 id="typeconv-func">
436 <title>Functions</title>
439 <title>Function Call Type Resolution</title>
441 <step performance="required">
443 Check for an exact match in the <classname>pg_proc</classname> system catalog.
444 (Cases involving <type>unknown</type> will never find a match at
447 <step performance="required">
449 Look for the best match.
452 <step performance="required">
454 Make a list of all functions of the same name with the same number of
455 arguments for which the input types
456 match or can be coerced to match. (<type>unknown</type> literals are
457 assumed to be coercible to anything for this purpose.) If there is only
458 one, use it; else continue to the next step.
461 <step performance="required">
463 Run through all candidates and keep those with the most exact matches
464 on input types. Keep all candidates if none have any exact matches.
465 If only one candidate remains, use it; else continue to the next step.
468 <step performance="required">
470 Run through all candidates and keep those with the most exact or
471 binary-compatible matches on input types. Keep all candidates if none have
472 any exact or binary-compatible matches.
473 If only one candidate remains, use it; else continue to the next step.
476 <step performance="required">
478 Run through all candidates and keep those that accept preferred types at
479 the most positions where type coercion will be required.
480 Keep all candidates if none accept preferred types.
481 If only one candidate remains, use it; else continue to the next step.
484 <step performance="required">
486 If any input arguments are "unknown", check the type categories accepted
487 at those argument positions by the remaining candidates. At each position,
489 category if any candidate accepts that category (this bias towards string
490 is appropriate since an unknown-type literal does look like a string).
491 Otherwise, if all the remaining candidates accept the same type category,
492 select that category; otherwise fail because
493 the correct choice cannot be deduced without more clues. Also note whether
494 any of the candidates accept a preferred data type within the selected category.
495 Now discard operator candidates that do not accept the selected type category;
496 furthermore, if any candidate accepts a preferred type at a given argument
497 position, discard candidates that accept non-preferred types for that
501 <step performance="required">
503 If only one candidate remains, use it. If no candidate or more than one
510 <step performance="required">
512 If no best match could be identified, see whether the function call appears
513 to be a trivial type coercion request. This happens if the function call
514 has just one argument and the function name is the same as the (internal)
515 name of some data type. Furthermore, the function argument must be either
516 an unknown-type literal or a type that is binary-compatible with the named
517 data type. When these conditions are met, the function argument is coerced
518 to the named data type.
524 <title>Examples</title>
527 <title>Factorial Function</title>
530 There is only one factorial function defined in the <classname>pg_proc</classname> catalog.
531 So the following query automatically converts the <type>int2</type> argument
532 to <type>int4</type>:
535 tgl=> select int4fac(int2 '4');
542 and is actually transformed by the parser to
544 tgl=> select int4fac(int4(int2 '4'));
554 <title>Substring Function</title>
557 There are two <function>substr</function> functions declared in <classname>pg_proc</classname>. However,
558 only one takes two arguments, of types <type>text</type> and <type>int4</type>.
562 If called with a string constant of unspecified type, the type is matched up
563 directly with the only candidate function type:
565 tgl=> select substr('1234', 3);
574 If the string is declared to be of type <type>varchar</type>, as might be the case
575 if it comes from a table, then the parser will try to coerce it to become <type>text</type>:
577 tgl=> select substr(varchar '1234', 3);
583 which is transformed by the parser to become
585 tgl=> select substr(text(varchar '1234'), 3);
594 Actually, the parser is aware that <type>text</type> and <type>varchar</type>
595 are "binary compatible", meaning that one can be passed to a function that
596 accepts the other without doing any physical conversion. Therefore, no
597 explicit type conversion call is really inserted in this case.
602 And, if the function is called with an <type>int4</type>, the parser will
603 try to convert that to <type>text</type>:
605 tgl=> select substr(1234, 3);
613 tgl=> select substr(text(1234), 3);
619 This succeeds because there is a conversion function text(int4) in the
626 <sect1 id="typeconv-query">
627 <title>Query Targets</title>
630 <title>Query Target Type Resolution</title>
632 <step performance="required">
634 Check for an exact match with the target.
636 <step performance="required">
638 Otherwise, try to coerce the expression to the target type. This will succeed
639 if the two types are known binary-compatible, or if there is a conversion
640 function. If the expression is an unknown-type literal, the contents of
641 the literal string will be fed to the input conversion routine for the target
645 <step performance="required">
647 If the target is a fixed-length type (e.g. <type>char</type> or <type>varchar</type>
648 declared with a length) then try to find a sizing function for the target
649 type. A sizing function is a function of the same name as the type,
650 taking two arguments of which the first is that type and the second is an
651 integer, and returning the same type. If one is found, it is applied,
652 passing the column's declared length as the second parameter.
658 <title>Examples</title>
661 <title><type>varchar</type> Storage</title>
664 For a target column declared as <type>varchar(4)</type> the following query
665 ensures that the target is sized correctly:
668 tgl=> CREATE TABLE vv (v varchar(4));
670 tgl=> INSERT INTO vv SELECT 'abc' || 'def';
672 tgl=> SELECT * FROM vv;
679 What's really happened here is that the two unknown literals are resolved
680 to text by default, allowing the <literal>||</literal> operator to be
681 resolved as text concatenation. Then the text result of the operator
682 is coerced to <type>varchar</type> to match the target column type. (But, since the
683 parser knows that text and <type>varchar</type> are binary-compatible, this coercion
684 is implicit and does not insert any real function call.) Finally, the
685 sizing function <literal>varchar(varchar,int4)</literal> is found in the system
686 catalogs and applied to the operator's result and the stored column length.
687 This type-specific function performs the desired truncation.
693 <sect1 id="typeconv-union-case">
694 <title>UNION and CASE Constructs</title>
697 The UNION and CASE constructs must match up possibly dissimilar types to
698 become a single result set. The resolution algorithm is applied separately to
699 each output column of a UNION. CASE uses the identical algorithm to match
700 up its result expressions.
703 <title>UNION and CASE Type Resolution</title>
705 <step performance="required">
707 If all inputs are of type <type>unknown</type>, resolve as type
708 <type>text</type> (the preferred type for string category).
709 Otherwise, ignore the <type>unknown</type> inputs while choosing the type.
712 <step performance="required">
714 If the non-unknown inputs are not all of the same type category, fail.
717 <step performance="required">
719 If one or more non-unknown inputs are of a preferred type in that category,
720 resolve as that type.
723 <step performance="required">
725 Otherwise, resolve as the type of the first non-unknown input.
728 <step performance="required">
730 Coerce all inputs to the selected type.
735 <title>Examples</title>
738 <title>Underspecified Types</title>
742 tgl=> SELECT text 'a' AS "Text" UNION SELECT 'b';
749 Here, the unknown-type literal 'b' will be resolved as type text.
754 <title>Simple UNION</title>
758 tgl=> SELECT 1.2 AS "Double" UNION SELECT 1;
769 <title>Transposed UNION</title>
772 Here the output type of the union is forced to match the type of
773 the first/top clause in the union:
776 tgl=> SELECT 1 AS "All integers"
777 tgl-> UNION SELECT CAST('2.2' AS REAL);
786 Since <type>REAL</type> is not a preferred type, the parser sees no reason
787 to select it over <type>INTEGER</type> (which is what the 1 is), and instead
788 falls back on the use-the-first-alternative rule.
789 This example demonstrates that the preferred-type mechanism doesn't encode
790 as much information as we'd like. Future versions of
791 <productname>Postgres</productname> may support a more general notion of
799 <!-- Keep this comment at the end of the file
804 sgml-minimize-attributes:nil
805 sgml-always-quote-attributes:t
808 sgml-parent-document:nil
809 sgml-default-dtd-file:"./reference.ced"
810 sgml-exposed-tags:nil
811 sgml-local-catalogs:("/usr/lib/sgml/catalog")
812 sgml-local-ecat-files:nil
projects / postgresql / blob