1 <!-- $PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.51 2007/02/01 19:10:24 momjian Exp $ -->
3 <chapter Id="typeconv">
4 <title>Type Conversion</title>
6 <indexterm zone="typeconv">
7 <primary>data type</primary>
8 <secondary>conversion</secondary>
12 <acronym>SQL</acronym> statements can, intentionally or not, require
13 mixing of different data types in the same expression.
14 <productname>PostgreSQL</productname> has extensive facilities for
15 evaluating mixed-type expressions.
19 In many cases a user will not need
20 to understand the details of the type conversion mechanism.
21 However, the implicit conversions done by <productname>PostgreSQL</productname>
22 can affect the results of a query. When necessary, these results
23 can be tailored by using <emphasis>explicit</emphasis> type conversion.
27 This chapter introduces the <productname>PostgreSQL</productname>
28 type conversion mechanisms and conventions.
29 Refer to the relevant sections in <xref linkend="datatype"> and <xref linkend="functions">
30 for more information on specific data types and allowed functions and
34 <sect1 id="typeconv-overview">
35 <title>Overview</title>
38 <acronym>SQL</acronym> is a strongly typed language. That is, every data item
39 has an associated data type which determines its behavior and allowed usage.
40 <productname>PostgreSQL</productname> has an extensible type system that is
41 much more general and flexible than other <acronym>SQL</acronym> implementations.
42 Hence, most type conversion behavior in <productname>PostgreSQL</productname>
43 is governed by general rules rather than by <foreignphrase>ad hoc</>
44 heuristics. This allows
45 mixed-type expressions to be meaningful even with user-defined types.
49 The <productname>PostgreSQL</productname> scanner/parser divides lexical
50 elements into only five fundamental categories: integers, non-integer numbers,
51 strings, identifiers, and key words. Constants of most non-numeric types are
52 first classified as strings. The <acronym>SQL</acronym> language definition
53 allows specifying type names with strings, and this mechanism can be used in
54 <productname>PostgreSQL</productname> to start the parser down the correct
55 path. For example, the query
58 SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
66 has two literal constants, of type <type>text</type> and <type>point</type>.
67 If a type is not specified for a string literal, then the placeholder type
68 <type>unknown</type> is assigned initially, to be resolved in later
69 stages as described below.
73 There are four fundamental <acronym>SQL</acronym> constructs requiring
74 distinct type conversion rules in the <productname>PostgreSQL</productname>
84 Much of the <productname>PostgreSQL</productname> type system is built around a
85 rich set of functions. Functions can have one or more arguments.
86 Since <productname>PostgreSQL</productname> permits function
87 overloading, the function name alone does not uniquely identify the function
88 to be called; the parser must select the right function based on the data
89 types of the supplied arguments.
99 <productname>PostgreSQL</productname> allows expressions with
100 prefix and postfix unary (one-argument) operators,
101 as well as binary (two-argument) operators. Like functions, operators can
102 be overloaded, and so the same problem of selecting the right operator
113 <acronym>SQL</acronym> <command>INSERT</command> and <command>UPDATE</command> statements place the results of
114 expressions into a table. The expressions in the statement must be matched up
115 with, and perhaps converted to, the types of the target columns.
121 <literal>UNION</literal>, <literal>CASE</literal>, and related constructs
125 Since all query results from a unionized <command>SELECT</command> statement
126 must appear in a single set of columns, the types of the results of each
127 <command>SELECT</> clause must be matched up and converted to a uniform set.
128 Similarly, the result expressions of a <literal>CASE</> construct must be
129 converted to a common type so that the <literal>CASE</> expression as a whole
130 has a known output type. The same holds for <literal>ARRAY</> constructs,
131 and for the <function>GREATEST</> and <function>LEAST</> functions.
139 The system catalogs store information about which conversions, called
140 <firstterm>casts</firstterm>, between data types are valid, and how to
141 perform those conversions. Additional casts can be added by the user
142 with the <command>CREATE CAST</command> command. (This is usually
143 done in conjunction with defining new data types. The set of casts
144 between the built-in types has been carefully crafted and is best not
149 <primary>data type</primary>
150 <secondary>category</secondary>
154 An additional heuristic is provided in the parser to allow better guesses
155 at proper behavior for <acronym>SQL</acronym> standard types. There are
156 several basic <firstterm>type categories</firstterm> defined: <type>boolean</type>,
157 <type>numeric</type>, <type>string</type>, <type>bitstring</type>, <type>datetime</type>, <type>timespan</type>, <type>geometric</type>, <type>network</type>,
158 and user-defined. Each category, with the exception of user-defined, has
159 one or more <firstterm>preferred types</firstterm> which are preferentially
160 selected when there is ambiguity.
161 In the user-defined category, each type is its own preferred type.
162 Ambiguous expressions (those with multiple candidate parsing solutions)
163 can therefore often be resolved when there are multiple possible built-in types, but
164 they will raise an error when there are multiple choices for user-defined
169 All type conversion rules are designed with several principles in mind:
174 Implicit conversions should never have surprising or unpredictable outcomes.
180 User-defined types, of which the parser has no <foreignphrase>a priori</> knowledge, should be
181 <quote>higher</quote> 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>PostgreSQL</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
191 available functions and casts.
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 calls into the query.
205 Additionally, if a query usually requires an implicit conversion for a function, and
206 if then the user defines a new 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>
218 <indexterm zone="typeconv-oper">
219 <primary>operator</primary>
220 <secondary>type resolution in an invocation</secondary>
224 The specific operator to be used in an operator invocation is determined
226 the procedure below. Note that this procedure is indirectly affected
227 by the precedence of the involved operators. See <xref
228 linkend="sql-precedence"> for more information.
232 <title>Operator Type Resolution</title>
234 <step performance="required">
236 Select the operators to be considered from the
237 <classname>pg_operator</classname> system catalog. If an unqualified
238 operator name was used (the usual case), the operators
239 considered are those of the right name and argument count that are
240 visible in the current search path (see <xref linkend="ddl-schemas-path">).
241 If a qualified operator name was given, only operators in the specified
242 schema are considered.
246 <step performance="optional">
248 If the search path finds multiple operators of identical argument types,
249 only the one appearing earliest in the path is considered. But operators of
250 different argument types are considered on an equal footing regardless of
251 search path position.
257 <step performance="required">
259 Check for an operator accepting exactly the input argument types.
260 If one exists (there can be only one exact match in the set of
261 operators considered), use it.
265 <step performance="optional">
267 If one argument of a binary operator invocation is of the <type>unknown</type> type,
268 then assume it is the same type as the other argument for this check.
269 Other cases involving <type>unknown</type> will never find a match at
276 <step performance="required">
278 Look for the best match.
281 <step performance="required">
283 Discard candidate operators for which the input types do not match
284 and cannot be converted (using an implicit conversion) to match.
285 <type>unknown</type> literals are
286 assumed to be convertible to anything for this purpose. If only one
287 candidate remains, use it; else continue to the next step.
290 <step performance="required">
292 Run through all candidates and keep those with the most exact matches
293 on input types. (Domains are considered the same as their base type
294 for this purpose.) Keep all candidates if none have any exact matches.
295 If only one candidate remains, use it; else continue to the next step.
298 <step performance="required">
300 Run through all candidates and keep those that accept preferred types (of the
301 input data type's type category) at the most positions where type conversion
303 Keep all candidates if none accept preferred types.
304 If only one candidate remains, use it; else continue to the next step.
307 <step performance="required">
309 If any input arguments are <type>unknown</type>, check the type
310 categories accepted at those argument positions by the remaining
311 candidates. At each position, select the <type>string</type> category
313 candidate accepts that category. (This bias towards string is appropriate
314 since an unknown-type literal does look like a string.) Otherwise, if
315 all the remaining candidates accept the same type category, select that
316 category; otherwise fail because the correct choice cannot be deduced
317 without more clues. Now discard
318 candidates that do not accept the selected type category. Furthermore,
319 if any candidate accepts a preferred type at a given argument position,
320 discard candidates that accept non-preferred types for that argument.
323 <step performance="required">
325 If only one candidate remains, use it. If no candidate or more than one
335 Some examples follow.
339 <title>Exponentiation Operator Type Resolution</title>
342 There is only one exponentiation
343 operator defined in the catalog, and it takes arguments of type
344 <type>double precision</type>.
345 The scanner assigns an initial type of <type>integer</type> to both arguments
346 of this query expression:
348 SELECT 2 ^ 3 AS "exp";
356 So the parser does a type conversion on both operands and the query
360 SELECT CAST(2 AS double precision) ^ CAST(3 AS double precision) AS "exp";
366 <title>String Concatenation Operator Type Resolution</title>
369 A string-like syntax is used for working with string types as well as for
370 working with complex extension types.
371 Strings with unspecified type are matched with likely operator candidates.
375 An example with one unspecified argument:
377 SELECT text 'abc' || 'def' AS "text and unknown";
387 In this case the parser looks to see if there is an operator taking <type>text</type>
388 for both arguments. Since there is, it assumes that the second argument should
389 be interpreted as of type <type>text</type>.
393 Here is a concatenation on unspecified types:
395 SELECT 'abc' || 'def' AS "unspecified";
405 In this case there is no initial hint for which type to use, since no types
406 are specified in the query. So, the parser looks for all candidate operators
407 and finds that there are candidates accepting both string-category and
408 bit-string-category inputs. Since string category is preferred when available,
409 that category is selected, and then the
410 preferred type for strings, <type>text</type>, is used as the specific
411 type to resolve the unknown literals to.
416 <title>Absolute-Value and Negation Operator Type Resolution</title>
419 The <productname>PostgreSQL</productname> operator catalog has several
420 entries for the prefix operator <literal>@</>, all of which implement
421 absolute-value operations for various numeric data types. One of these
422 entries is for type <type>float8</type>, which is the preferred type in
423 the numeric category. Therefore, <productname>PostgreSQL</productname>
424 will use that entry when faced with a non-numeric input:
426 SELECT @ '-4.5' AS "abs";
432 Here the system has performed an implicit conversion from <type>text</type> to <type>float8</type>
433 before applying the chosen operator. We can verify that <type>float8</type> and
434 not some other type was used:
436 SELECT @ '-4.5e500' AS "abs";
438 ERROR: "-4.5e500" is out of range for type double precision
443 On the other hand, the prefix operator <literal>~</> (bitwise negation)
444 is defined only for integer data types, not for <type>float8</type>. So, if we
445 try a similar case with <literal>~</>, we get:
447 SELECT ~ '20' AS "negation";
449 ERROR: operator is not unique: ~ "unknown"
450 HINT: Could not choose a best candidate operator. You might need to add explicit
453 This happens because the system cannot decide which of the several
454 possible <literal>~</> operators should be preferred. We can help
455 it out with an explicit cast:
457 SELECT ~ CAST('20' AS int8) AS "negation";
469 <sect1 id="typeconv-func">
470 <title>Functions</title>
472 <indexterm zone="typeconv-func">
473 <primary>function</primary>
474 <secondary>type resolution in an invocation</secondary>
478 The specific function to be used in a function invocation is determined
479 according to the following steps.
483 <title>Function Type Resolution</title>
485 <step performance="required">
487 Select the functions to be considered from the
488 <classname>pg_proc</classname> system catalog. If an unqualified
489 function name was used, the functions
490 considered are those of the right name and argument count that are
491 visible in the current search path (see <xref linkend="ddl-schemas-path">).
492 If a qualified function name was given, only functions in the specified
493 schema are considered.
497 <step performance="optional">
499 If the search path finds multiple functions of identical argument types,
500 only the one appearing earliest in the path is considered. But functions of
501 different argument types are considered on an equal footing regardless of
502 search path position.
508 <step performance="required">
510 Check for a function accepting exactly the input argument types.
511 If one exists (there can be only one exact match in the set of
512 functions considered), use it.
513 (Cases involving <type>unknown</type> will never find a match at
518 <step performance="required">
520 If no exact match is found, see whether the function call appears
521 to be a trivial type conversion request. This happens if the function call
522 has just one argument and the function name is the same as the (internal)
523 name of some data type. Furthermore, the function argument must be either
524 an unknown-type literal or a type that is binary-compatible with the named
525 data type. When these conditions are met, the function argument is converted
526 to the named data type without any actual function call.
529 <step performance="required">
531 Look for the best match.
534 <step performance="required">
536 Discard candidate functions for which the input types do not match
537 and cannot be converted (using an implicit conversion) to match.
538 <type>unknown</type> literals are
539 assumed to be convertible to anything for this purpose. If only one
540 candidate remains, use it; else continue to the next step.
543 <step performance="required">
545 Run through all candidates and keep those with the most exact matches
546 on input types. (Domains are considered the same as their base type
547 for this purpose.) Keep all candidates if none have any exact matches.
548 If only one candidate remains, use it; else continue to the next step.
551 <step performance="required">
553 Run through all candidates and keep those that accept preferred types (of the
554 input data type's type category) at the most positions where type conversion
556 Keep all candidates if none accept preferred types.
557 If only one candidate remains, use it; else continue to the next step.
560 <step performance="required">
562 If any input arguments are <type>unknown</type>, check the type categories
564 at those argument positions by the remaining candidates. At each position,
565 select the <type>string</type> category if any candidate accepts that category.
566 (This bias towards string
567 is appropriate since an unknown-type literal does look like a string.)
568 Otherwise, if all the remaining candidates accept the same type category,
569 select that category; otherwise fail because
570 the correct choice cannot be deduced without more clues.
571 Now discard candidates that do not accept the selected type category.
572 Furthermore, if any candidate accepts a preferred type at a given argument
573 position, discard candidates that accept non-preferred types for that
577 <step performance="required">
579 If only one candidate remains, use it. If no candidate or more than one
589 Note that the <quote>best match</> rules are identical for operator and
590 function type resolution.
591 Some examples follow.
595 <title>Rounding Function Argument Type Resolution</title>
598 There is only one <function>round</function> function with two
599 arguments. (The first is <type>numeric</type>, the second is
600 <type>integer</type>.) So the following query automatically converts
601 the first argument of type <type>integer</type> to
602 <type>numeric</type>:
613 That query is actually transformed by the parser to
615 SELECT round(CAST (4 AS numeric), 4);
620 Since numeric constants with decimal points are initially assigned the
621 type <type>numeric</type>, the following query will require no type
622 conversion and might therefore be slightly more efficient:
624 SELECT round(4.0, 4);
630 <title>Substring Function Type Resolution</title>
633 There are several <function>substr</function> functions, one of which
634 takes types <type>text</type> and <type>integer</type>. If called
635 with a string constant of unspecified type, the system chooses the
636 candidate function that accepts an argument of the preferred category
637 <literal>string</literal> (namely of type <type>text</type>).
640 SELECT substr('1234', 3);
650 If the string is declared to be of type <type>varchar</type>, as might be the case
651 if it comes from a table, then the parser will try to convert it to become <type>text</type>:
653 SELECT substr(varchar '1234', 3);
661 This is transformed by the parser to effectively become
663 SELECT substr(CAST (varchar '1234' AS text), 3);
669 The parser learns from the <structname>pg_cast</> catalog that
670 <type>text</type> and <type>varchar</type>
671 are binary-compatible, meaning that one can be passed to a function that
672 accepts the other without doing any physical conversion. Therefore, no
673 explicit type conversion call is really inserted in this case.
679 And, if the function is called with an argument of type <type>integer</type>, the parser will
680 try to convert that to <type>text</type>:
682 SELECT substr(1234, 3);
690 This actually executes as
692 SELECT substr(CAST (1234 AS text), 3);
694 This automatic transformation can succeed because there is an
695 implicitly invocable cast from <type>integer</type> to
702 <sect1 id="typeconv-query">
703 <title>Value Storage</title>
706 Values to be inserted into a table are converted to the destination
707 column's data type according to the
712 <title>Value Storage Type Conversion</title>
714 <step performance="required">
716 Check for an exact match with the target.
720 <step performance="required">
722 Otherwise, try to convert the expression to the target type. This will succeed
723 if there is a registered cast between the two types.
724 If the expression is an unknown-type literal, the contents of
725 the literal string will be fed to the input conversion routine for the target
730 <step performance="required">
732 Check to see if there is a sizing cast for the target type. A sizing
733 cast is a cast from that type to itself. If one is found in the
734 <structname>pg_cast</> catalog, apply it to the expression before storing
735 into the destination column. The implementation function for such a cast
736 always takes an extra parameter of type <type>integer</type>, which receives
737 the destination column's declared length (actually, its
738 <structfield>atttypmod</> value; the interpretation of
739 <structfield>atttypmod</> varies for different data types). The cast function
740 is responsible for applying any length-dependent semantics such as size
741 checking or truncation.
748 <title><type>character</type> Storage Type Conversion</title>
751 For a target column declared as <type>character(20)</type> the following statement
752 ensures that the stored value is sized correctly:
755 CREATE TABLE vv (v character(20));
756 INSERT INTO vv SELECT 'abc' || 'def';
757 SELECT v, length(v) FROM vv;
760 ----------------------+--------
767 What has really happened here is that the two unknown literals are resolved
768 to <type>text</type> by default, allowing the <literal>||</literal> operator
769 to be resolved as <type>text</type> concatenation. Then the <type>text</type>
770 result of the operator is converted to <type>bpchar</type> (<quote>blank-padded
771 char</>, the internal name of the <type>character</type> data type) to match the target
772 column type. (Since the types <type>text</type> and
773 <type>bpchar</type> are binary-compatible, this conversion does
774 not insert any real function call.) Finally, the sizing function
775 <literal>bpchar(bpchar, integer)</literal> is found in the system catalog
776 and applied to the operator's result and the stored column length. This
777 type-specific function performs the required length check and addition of
783 <sect1 id="typeconv-union-case">
784 <title><literal>UNION</literal>, <literal>CASE</literal>, and Related Constructs</title>
786 <indexterm zone="typeconv-union-case">
787 <primary>UNION</primary>
788 <secondary>determination of result type</secondary>
791 <indexterm zone="typeconv-union-case">
792 <primary>CASE</primary>
793 <secondary>determination of result type</secondary>
796 <indexterm zone="typeconv-union-case">
797 <primary>ARRAY</primary>
798 <secondary>determination of result type</secondary>
801 <indexterm zone="typeconv-union-case">
802 <primary>VALUES</primary>
803 <secondary>determination of result type</secondary>
806 <indexterm zone="typeconv-union-case">
807 <primary>GREATEST</primary>
808 <secondary>determination of result type</secondary>
811 <indexterm zone="typeconv-union-case">
812 <primary>LEAST</primary>
813 <secondary>determination of result type</secondary>
817 SQL <literal>UNION</> constructs must match up possibly dissimilar
818 types to become a single result set. The resolution algorithm is
819 applied separately to each output column of a union query. The
820 <literal>INTERSECT</> and <literal>EXCEPT</> constructs resolve
821 dissimilar types in the same way as <literal>UNION</>. The
822 <literal>CASE</>, <literal>ARRAY</>, <literal>VALUES</>,
823 <function>GREATEST</> and <function>LEAST</> constructs use the identical
824 algorithm to match up their component expressions and select a result
829 <title>Type Resolution for <literal>UNION</literal>, <literal>CASE</literal>,
830 and Related Constructs</title>
832 <step performance="required">
834 If all inputs are of type <type>unknown</type>, resolve as type
835 <type>text</type> (the preferred type of the string category).
836 Otherwise, ignore the <type>unknown</type> inputs while choosing the result type.
840 <step performance="required">
842 If the non-unknown inputs are not all of the same type category, fail.
846 <step performance="required">
848 Choose the first non-unknown input type which is a preferred type in
849 that category or allows all the non-unknown inputs to be implicitly
854 <step performance="required">
856 Convert all inputs to the selected type.
862 Some examples follow.
866 <title>Type Resolution with Underspecified Types in a Union</title>
870 SELECT text 'a' AS "text" UNION SELECT 'b';
878 Here, the unknown-type literal <literal>'b'</literal> will be resolved as type <type>text</type>.
883 <title>Type Resolution in a Simple Union</title>
887 SELECT 1.2 AS "numeric" UNION SELECT 1;
895 The literal <literal>1.2</> is of type <type>numeric</>,
896 and the <type>integer</type> value <literal>1</> can be cast implicitly to
897 <type>numeric</>, so that type is used.
902 <title>Type Resolution in a Transposed Union</title>
906 SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);
914 Here, since type <type>real</> cannot be implicitly cast to <type>integer</>,
915 but <type>integer</> can be implicitly cast to <type>real</>, the union
916 result type is resolved as <type>real</>.
projects / postgresql / blob