<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Type Conversion</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="The SQL Language" HREF="sql.html"><LINK REL="PREVIOUS" TITLE="System Administration Functions" HREF="functions-admin.html"><LINK REL="NEXT" TITLE="Operators" HREF="typeconv-oper.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="CHAPTER" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-admin.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="indexes.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="typeconv-oper.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="CHAPTER" ><H1 ><A NAME="TYPECONV" ></A >Chapter 10. Type Conversion</H1 ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT >10.1. <A HREF="typeconv.html#TYPECONV-OVERVIEW" >Overview</A ></DT ><DT >10.2. <A HREF="typeconv-oper.html" >Operators</A ></DT ><DT >10.3. <A HREF="typeconv-func.html" >Functions</A ></DT ><DT >10.4. <A HREF="typeconv-query.html" >Value Storage</A ></DT ><DT >10.5. <A HREF="typeconv-union-case.html" ><TT CLASS="LITERAL" >UNION</TT >, <TT CLASS="LITERAL" >CASE</TT >, and <TT CLASS="LITERAL" >ARRAY</TT > Constructs</A ></DT ></DL ></DIV ><A NAME="AEN13684" ></A ><P ><ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > statements can, intentionally or not, require mixing of different data types in the same expression. <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > has extensive facilities for evaluating mixed-type expressions.</P ><P >In many cases a user will not need to understand the details of the type conversion mechanism. However, the implicit conversions done by <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > can affect the results of a query. When necessary, these results can be tailored by using <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >explicit</I ></SPAN > type conversion.</P ><P >This chapter introduces the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > type conversion mechanisms and conventions. Refer to the relevant sections in <A HREF="datatype.html" >Chapter 8</A > and <A HREF="functions.html" >Chapter 9</A > for more information on specific data types and allowed functions and operators.</P ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="TYPECONV-OVERVIEW" >10.1. Overview</A ></H1 ><P ><ACRONYM CLASS="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. <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > has an extensible type system that is much more general and flexible than other <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > implementations. Hence, most type conversion behavior in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > is governed by general rules rather than by <I CLASS="FOREIGNPHRASE" >ad hoc</I > heuristics. This allows mixed-type expressions to be meaningful even with user-defined types.</P ><P >The <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > scanner/parser divides lexical elements into only 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 CLASS="ACRONYM" >SQL</ACRONYM > language definition allows specifying type names with strings, and this mechanism can be used in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > to start the parser down the correct path. For example, the query </P><PRE CLASS="SCREEN" >SELECT text 'Origin' AS "label", point '(0,0)' AS "value"; label | value --------+------- Origin | (0,0) (1 row)</PRE ><P> has two literal constants, of type <TT CLASS="TYPE" >text</TT > and <TT CLASS="TYPE" >point</TT >. If a type is not specified for a string literal, then the placeholder type <TT CLASS="TYPE" >unknown</TT > is assigned initially, to be resolved in later stages as described below.</P ><P >There are four fundamental <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > constructs requiring distinct type conversion rules in the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > parser: <P ></P ></P><DIV CLASS="VARIABLELIST" ><DL ><DT >Function calls</DT ><DD ><P >Much of the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > type system is built around a rich set of functions. Functions can have one or more arguments. Since <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > 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.</P ></DD ><DT >Operators</DT ><DD ><P ><SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > allows expressions with prefix and postfix unary (one-argument) operators, as well as binary (two-argument) operators. Like functions, operators can be overloaded, and so the same problem of selecting the right operator exists.</P ></DD ><DT >Value Storage</DT ><DD ><P ><ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > <TT CLASS="COMMAND" >INSERT</TT > and <TT CLASS="COMMAND" >UPDATE</TT > 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.</P ></DD ><DT ><TT CLASS="LITERAL" >UNION</TT >, <TT CLASS="LITERAL" >CASE</TT >, and <TT CLASS="LITERAL" >ARRAY</TT > constructs</DT ><DD ><P >Since all query results from a unionized <TT CLASS="COMMAND" >SELECT</TT > statement must appear in a single set of columns, the types of the results of each <TT CLASS="COMMAND" >SELECT</TT > clause must be matched up and converted to a uniform set. Similarly, the result expressions of a <TT CLASS="LITERAL" >CASE</TT > construct must be converted to a common type so that the <TT CLASS="LITERAL" >CASE</TT > expression as a whole has a known output type. The same holds for <TT CLASS="LITERAL" >ARRAY</TT > constructs.</P ></DD ></DL ></DIV ><P></P ><P >The system catalogs store information about which conversions, called <I CLASS="FIRSTTERM" >casts</I >, between data types are valid, and how to perform those conversions. Additional casts can be added by the user with the <TT CLASS="COMMAND" >CREATE CAST</TT > command. (This is usually done in conjunction with defining new data types. The set of casts between the built-in types has been carefully crafted and is best not altered.)</P ><A NAME="AEN13750" ></A ><P >An additional heuristic is provided in the parser to allow better guesses at proper behavior for <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > standard types. There are several basic <I CLASS="FIRSTTERM" >type categories</I > defined: <TT CLASS="TYPE" >boolean</TT >, <TT CLASS="TYPE" >numeric</TT >, <TT CLASS="TYPE" >string</TT >, <TT CLASS="TYPE" >bitstring</TT >, <TT CLASS="TYPE" >datetime</TT >, <TT CLASS="TYPE" >timespan</TT >, <TT CLASS="TYPE" >geometric</TT >, <TT CLASS="TYPE" >network</TT >, and user-defined. Each category, with the exception of user-defined, has one or more <I CLASS="FIRSTTERM" >preferred types</I > which are preferentially selected when there is ambiguity. In the user-defined category, each type is its own preferred type. Ambiguous expressions (those with multiple candidate parsing solutions) can therefore often be resolved when there are multiple possible built-in types, but they will raise an error when there are multiple choices for user-defined types.</P ><P >All type conversion rules are designed with several principles in mind: <P ></P ></P><UL ><LI ><P >Implicit conversions should never have surprising or unpredictable outcomes.</P ></LI ><LI ><P >User-defined types, of which the parser has no <I CLASS="FOREIGNPHRASE" >a priori</I > knowledge, should be <SPAN CLASS="QUOTE" >"higher"</SPAN > in the type hierarchy. In mixed-type expressions, native types shall always be converted to a user-defined type (of course, only if conversion is necessary).</P ></LI ><LI ><P >User-defined types are not related. Currently, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > does not have information available to it on relationships between types, other than hardcoded heuristics for built-in types and implicit relationships based on available functions and casts.</P ></LI ><LI ><P >There should be no extra overhead from the parser or executor if a query does not need implicit type conversion. That is, if a query is well formulated and the types already match up, then the query should proceed without spending extra time in the parser and without introducing unnecessary implicit conversion calls into the query.</P ><P >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 will no longer do the implicit conversion using the old function.</P ></LI ></UL ><P></P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions-admin.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="typeconv-oper.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >System Administration Functions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="sql.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Operators</TD ></TR ></TABLE ></DIV ></BODY ></HTML >