<!-- doc/src/sgml/typeconv.sgml -->

<chapter id="typeconv">
<title>Type Conversion</title>

<indexterm zone="typeconv">
 <primary>data type</primary>
 <secondary>conversion</secondary>
</indexterm>

<para>
<acronym>SQL</acronym> statements can, intentionally or not, require
the mixing of different data types in the same expression.
<productname>PostgreSQL</productname> has extensive facilities for
evaluating mixed-type expressions.
</para>

<para>
In many cases a user does not need
to understand the details of the type conversion mechanism.
However, implicit conversions done by <productname>PostgreSQL</productname>
can affect the results of a query.  When necessary, these results
can be tailored by using <emphasis>explicit</emphasis> type conversion.
</para>

<para>
This chapter introduces the <productname>PostgreSQL</productname>
type conversion mechanisms and conventions.
Refer to the relevant sections in <xref linkend="datatype"/> and <xref linkend="functions"/>
for more information on specific data types and allowed functions and
operators.
</para>

<sect1 id="typeconv-overview">
<title>Overview</title>

<para>
<acronym>SQL</acronym> is a strongly typed language. That is, every data item
has an associated data type which determines its behavior and allowed usage.
<productname>PostgreSQL</productname> has an extensible type system that is
more general and flexible than other <acronym>SQL</acronym> implementations.
Hence, most type conversion behavior in <productname>PostgreSQL</productname>
is governed by general rules rather than by ad hoc
heuristics.  This allows the use of mixed-type expressions even with
user-defined types.
</para>

<para>
The <productname>PostgreSQL</productname> scanner/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. The <acronym>SQL</acronym> language definition
allows specifying type names with strings, and this mechanism can be used in
<productname>PostgreSQL</productname> to start the parser down the correct
path. For example, the query:

<screen>
SELECT text 'Origin' AS "label", point '(0,0)' AS "value";

 label  | value
--------+-------
 Origin | (0,0)
(1 row)
</screen>

has two literal constants, of type <type>text</type> and <type>point</type>.
If a type is not specified for a string literal, then the placeholder type
<type>unknown</type> is assigned initially, to be resolved in later
stages as described below.
</para>

<para>
There are four fundamental <acronym>SQL</acronym> constructs requiring
distinct type conversion rules in the <productname>PostgreSQL</productname>
parser:

<variablelist>
<varlistentry>
<term>
Function calls
</term>
<listitem>
<para>
Much of the <productname>PostgreSQL</productname> type system is built around a
rich set of functions. Functions can have one or more arguments.
Since <productname>PostgreSQL</productname> permits 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.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
Operators
</term>
<listitem>
<para>
<productname>PostgreSQL</productname> allows expressions with
prefix (one-argument) operators,
as well as infix (two-argument) operators.  Like functions, operators can
be overloaded, so the same problem of selecting the right operator
exists.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
Value Storage
</term>
<listitem>
<para>
<acronym>SQL</acronym> <command>INSERT</command> and <command>UPDATE</command> statements 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.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
<literal>UNION</literal>, <literal>CASE</literal>, and related constructs