File: typeconv2704.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (237 lines) | stat: -rw-r--r-- 3,508 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>UNION Queries</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="Type Conversion"
HREF="typeconv.html"><LINK
REL="PREVIOUS"
TITLE="Query Targets"
HREF="typeconv2683.html"><LINK
REL="NEXT"
TITLE="Indices and Keys"
HREF="keys.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="typeconv2683.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Type Conversion</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="keys.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
CLASS="SECT1"
><A
NAME="AEN2704"
>UNION Queries</A
></H1
><P
>The UNION construct is somewhat different in that it must match up
possibly dissimilar types to become a single result set.&#13;</P
><DIV
CLASS="PROCEDURE"
><P
><B
>UNION Evaluation</B
></P
><OL
TYPE="1"
><LI
><P
>Check for identical types for all results.&#13;</P
></LI
><LI
><P
>Coerce each result from the UNION clauses to match the type of the
first SELECT clause or the target column.&#13;</P
></LI
></OL
></DIV
><H2
CLASS="SECT2"
><A
NAME="AEN2713"
>Examples</A
></H2
><H3
CLASS="SECT3"
><A
NAME="AEN2715"
>Underspecified Types</A
></H3
><P
><PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT text 'a' AS "Text" UNION SELECT 'b';
Text
----
a
b
(2 rows)</PRE
>&#13;</P
><H3
CLASS="SECT3"
><A
NAME="AEN2719"
>Simple UNION</A
></H3
><P
><PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT 1.2 AS Float8 UNION SELECT 1;
Float8
------
     1
   1.2
(2 rows)</PRE
>&#13;</P
><H3
CLASS="SECT3"
><A
NAME="AEN2723"
>Transposed UNION</A
></H3
><P
>The types of the union are forced to match the types of
the first/top clause in the union:

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT 1 AS "All integers"
tgl-&#62; UNION SELECT '2.2'::float4
tgl-&#62; UNION SELECT 3.3;
All integers
------------
           1
           2
           3
(3 rows)</PRE
>&#13;</P
><P
>An alternate parser strategy could be to choose the "best" type of the bunch, but
this is more difficult because of the nice recursion technique used in the
parser. However, the "best" type is used when selecting <I
CLASS="EMPHASIS"
>into</I
>
a table:

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; CREATE TABLE ff (f float);
CREATE
tgl=&#62; INSERT INTO ff
tgl-&#62; SELECT 1
tgl-&#62; UNION SELECT '2.2'::float4
tgl-&#62; UNION SELECT 3.3;
INSERT 0 3
tgl=&#62; SELECT f AS "Floating point" from ff;
  Floating point
----------------
               1
2.20000004768372
             3.3
(3 rows)</PRE
>&#13;</P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="typeconv2683.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="keys.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Query Targets</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="typeconv.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Indices and Keys</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>