12
12
<acronym>SQL</acronym> statements can, intentionally or not, require
13
mixing of different data types in the same expression.
13
the mixing of different data types in the same expression.
14
14
<productname>PostgreSQL</productname> has extensive facilities for
15
15
evaluating mixed-type expressions.
19
In many cases a user will not need
19
In many cases a user does not need
20
20
to understand the details of the type conversion mechanism.
21
However, the implicit conversions done by <productname>PostgreSQL</productname>
21
However, implicit conversions done by <productname>PostgreSQL</productname>
22
22
can affect the results of a query. When necessary, these results
23
23
can be tailored by using <emphasis>explicit</emphasis> type conversion.
38
38
<acronym>SQL</acronym> is a strongly typed language. That is, every data item
39
39
has an associated data type which determines its behavior and allowed usage.
40
40
<productname>PostgreSQL</productname> has an extensible type system that is
41
much more general and flexible than other <acronym>SQL</acronym> implementations.
41
more general and flexible than other <acronym>SQL</acronym> implementations.
42
42
Hence, most type conversion behavior in <productname>PostgreSQL</productname>
43
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.
44
heuristics. This allows the use of mixed-type expressions even with
49
49
The <productname>PostgreSQL</productname> scanner/parser divides lexical
50
elements into only five fundamental categories: integers, non-integer numbers,
50
elements into five fundamental categories: integers, non-integer numbers,
51
51
strings, identifiers, and key words. Constants of most non-numeric types are
52
52
first classified as strings. The <acronym>SQL</acronym> language definition
53
53
allows specifying type names with strings, and this mechanism can be used in
54
54
<productname>PostgreSQL</productname> to start the parser down the correct
55
path. For example, the query
55
path. For example, the query:
58
58
SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
99
99
<productname>PostgreSQL</productname> allows expressions with
100
100
prefix and postfix unary (one-argument) operators,
101
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
102
be overloaded, so the same problem of selecting the right operator
139
The system catalogs store information about which conversions, called
140
<firstterm>casts</firstterm>, between data types are valid, and how to
139
The system catalogs store information about which conversions, or
140
<firstterm>casts</firstterm>, exist between which data types, and how to
141
141
perform those conversions. Additional casts can be added by the user
142
142
with the <xref linkend="sql-createcast" endterm="sql-createcast-title">
143
143
command. (This is usually
144
144
done in conjunction with defining new data types. The set of casts
145
between the built-in types has been carefully crafted and is best not
145
between built-in types has been carefully crafted and is best not
155
An additional heuristic is provided in the parser to allow better guesses
156
at proper casting behavior among groups of types that have implicit casts.
155
An additional heuristic provided by the parser allows improved determination
156
of the proper casting behavior among groups of types that have implicit casts.
157
157
Data types are divided into several basic <firstterm>type
158
158
categories</firstterm>, including <type>boolean</type>, <type>numeric</type>,
159
159
<type>string</type>, <type>bitstring</type>, <type>datetime</type>,
161
161
user-defined. (For a list see <xref linkend="catalog-typcategory-table">;
162
162
but note it is also possible to create custom type categories.) Within each
163
163
category there can be one or more <firstterm>preferred types</firstterm>, which
164
are preferentially selected when there is ambiguity. With careful selection
164
are selected when there is ambiguity. With careful selection
165
165
of preferred types and available implicit casts, it is possible to ensure that
166
166
ambiguous expressions (those with multiple candidate parsing solutions) can be
167
167
resolved in a useful way.
182
There should be no extra overhead from the parser or executor
182
There should be no extra overhead in the parser or executor
183
183
if a query does not need implicit type conversion.
184
That is, if a query is well formulated and the types already match up, then the query should proceed
184
That is, if a query is well-formed and the types already match, then the query should execute
185
185
without spending extra time in the parser and without introducing unnecessary implicit conversion
186
calls into the query.
190
190
Additionally, if a query usually requires an implicit conversion for a function, and
191
191
if then the user defines a new function with the correct argument types, the parser
192
should use this new function and will no longer do the implicit conversion using the old function.
192
should use this new function and no longer do implicit conversion using the old function.
209
The specific operator to be used in an operator invocation is determined
211
the procedure below. Note that this procedure is indirectly affected
209
The specific operator invoked is determined by the following
210
steps. Note that this procedure is affected
212
211
by the precedence of the involved operators. See <xref
213
212
linkend="sql-precedence"> for more information.
219
218
<step performance="required">
221
220
Select the operators to be considered from the
222
<classname>pg_operator</classname> system catalog. If an unqualified
221
<classname>pg_operator</classname> system catalog. If a non-schema-qualified
223
222
operator name was used (the usual case), the operators
224
considered are those of the right name and argument count that are
223
considered are those with a matching name and argument count that are
225
224
visible in the current search path (see <xref linkend="ddl-schemas-path">).
226
225
If a qualified operator name was given, only operators in the specified
227
226
schema are considered.
231
230
<step performance="optional">
233
If the search path finds multiple operators of identical argument types,
234
only the one appearing earliest in the path is considered. But operators of
232
If the search path finds multiple operators with identical argument types,
233
only the one appearing earliest in the path is considered. Operators with
235
234
different argument types are considered on an equal footing regardless of
236
235
search path position.
252
251
If one argument of a binary operator invocation is of the <type>unknown</type> type,
253
252
then assume it is the same type as the other argument for this check.
254
Other cases involving <type>unknown</type> will never find a match at
253
Cases involving two <type>unknown</type> types will never find a match at
277
276
Run through all candidates and keep those with the most exact matches
278
277
on input types. (Domains are considered the same as their base type
279
for this purpose.) Keep all candidates if none have any exact matches.
278
for this purpose.) Keep all candidates if none have exact matches.
280
279
If only one candidate remains, use it; else continue to the next step.
296
295
candidates. At each position, select the <type>string</type> category
298
297
candidate accepts that category. (This bias towards string is appropriate
299
since an unknown-type literal does look like a string.) Otherwise, if
298
since an unknown-type literal looks like a string.) Otherwise, if
300
299
all the remaining candidates accept the same type category, select that
301
300
category; otherwise fail because the correct choice cannot be deduced
302
301
without more clues. Now discard
351
350
<title>String Concatenation Operator Type Resolution</title>
354
A string-like syntax is used for working with string types as well as for
353
A string-like syntax is used for working with string types and for
355
354
working with complex extension types.
356
355
Strings with unspecified type are matched with likely operator candidates.
372
371
In this case the parser looks to see if there is an operator taking <type>text</type>
373
372
for both arguments. Since there is, it assumes that the second argument should
374
be interpreted as of type <type>text</type>.
373
be interpreted as type <type>text</type>.
391
390
are specified in the query. So, the parser looks for all candidate operators
392
391
and finds that there are candidates accepting both string-category and
393
392
bit-string-category inputs. Since string category is preferred when available,
394
that category is selected, and then the
393
that category is selected, and the
395
394
preferred type for strings, <type>text</type>, is used as the specific
396
type to resolve the unknown literals to.
395
type to resolve the unknown literals.
470
469
<step performance="required">
472
471
Select the functions to be considered from the
473
<classname>pg_proc</classname> system catalog. If an unqualified
472
<classname>pg_proc</classname> system catalog. If a non-schema-qualified
474
473
function name was used, the functions
475
considered are those of the right name and argument count that are
474
considered are those with a matching name and argument count that are
476
475
visible in the current search path (see <xref linkend="ddl-schemas-path">).
477
476
If a qualified function name was given, only functions in the specified
478
477
schema are considered.
482
481
<step performance="optional">
484
483
If the search path finds multiple functions of identical argument types,
485
only the one appearing earliest in the path is considered. But functions of
484
only the one appearing earliest in the path is considered. Functions of
486
485
different argument types are considered on an equal footing regardless of
487
486
search path position.
528
527
<step performance="required">
530
If no exact match is found, see whether the function call appears
529
If no exact match is found, see if the function call appears
531
530
to be a special type conversion request. This happens if the function call
532
531
has just one argument and the function name is the same as the (internal)
533
532
name of some data type. Furthermore, the function argument must be either
556
555
<step performance="required">
558
Discard candidate functions for which the input types do not match
557
Discard candidate functions in which the input types do not match
559
558
and cannot be converted (using an implicit conversion) to match.
560
559
<type>unknown</type> literals are
561
560
assumed to be convertible to anything for this purpose. If only one
567
566
Run through all candidates and keep those with the most exact matches
568
567
on input types. (Domains are considered the same as their base type
569
for this purpose.) Keep all candidates if none have any exact matches.
568
for this purpose.) Keep all candidates if none have exact matches.
570
569
If only one candidate remains, use it; else continue to the next step.
586
585
at those argument positions by the remaining candidates. At each position,
587
586
select the <type>string</type> category if any candidate accepts that category.
588
587
(This bias towards string
589
is appropriate since an unknown-type literal does look like a string.)
588
is appropriate since an unknown-type literal looks like a string.)
590
589
Otherwise, if all the remaining candidates accept the same type category,
591
590
select that category; otherwise fail because
592
591
the correct choice cannot be deduced without more clues.
616
615
<title>Rounding Function Argument Type Resolution</title>
619
There is only one <function>round</function> function with two
620
arguments. (The first is <type>numeric</type>, the second is
621
<type>integer</type>.) So the following query automatically converts
618
There is only one <function>round</function> function which takes two
619
arguments; it takes a first argument of <type>numeric</type> and
620
a second argument of <type>integer</type>. So the following query automatically converts
622
621
the first argument of type <type>integer</type> to
623
622
<type>numeric</type>:
641
640
Since numeric constants with decimal points are initially assigned the
642
641
type <type>numeric</type>, the following query will require no type
643
conversion and might therefore be slightly more efficient:
642
conversion and therefore might be slightly more efficient:
645
644
SELECT round(4.0, 4);
864
863
If all inputs are of type <type>unknown</type>, resolve as type
865
864
<type>text</type> (the preferred type of the string category).
866
Otherwise, the <type>unknown</type> inputs will be ignored.
865
Otherwise, <type>unknown</type> inputs are ignored.
added
removed
doc/src/sgml/typeconv.sgml
