<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Value Storage</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="Type Conversion" HREF="typeconv.html"><LINK REL="PREVIOUS" TITLE="Functions" HREF="typeconv-func.html"><LINK REL="NEXT" TITLE="UNION, CASE, and ARRAY Constructs" HREF="typeconv-union-case.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="SECT1" ><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="typeconv-func.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="typeconv.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 10. Type Conversion</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="typeconv.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="typeconv-union-case.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="TYPECONV-QUERY" >10.4. Value Storage</A ></H1 ><P > Values to be inserted into a table are converted to the destination column's data type according to the following steps. </P ><DIV CLASS="PROCEDURE" ><P ><B >Value Storage Type Conversion</B ></P ><OL TYPE="1" ><LI CLASS="STEP" ><P >Check for an exact match with the target.</P ></LI ><LI CLASS="STEP" ><P >Otherwise, try to convert the expression to the target type. This will succeed if there is a registered cast between the two types. If the expression is an unknown-type literal, the contents of the literal string will be fed to the input conversion routine for the target type.</P ></LI ><LI CLASS="STEP" ><P >Check to see if there is a sizing cast for the target type. A sizing cast is a cast from that type to itself. If one is found in the <TT CLASS="STRUCTNAME" >pg_cast</TT > catalog, apply it to the expression before storing into the destination column. The implementation function for such a cast always takes an extra parameter of type <TT CLASS="TYPE" >integer</TT >, which receives the destination column's declared length (actually, its <TT CLASS="STRUCTFIELD" >atttypmod</TT > value; the interpretation of <TT CLASS="STRUCTFIELD" >atttypmod</TT > varies for different datatypes). The cast function is responsible for applying any length-dependent semantics such as size checking or truncation.</P ></LI ></OL ></DIV ><DIV CLASS="EXAMPLE" ><A NAME="AEN13950" ></A ><P ><B >Example 10-6. <TT CLASS="TYPE" >character</TT > Storage Type Conversion</B ></P ><P >For a target column declared as <TT CLASS="TYPE" >character(20)</TT > the following statement ensures that the stored value is sized correctly: </P><PRE CLASS="SCREEN" >CREATE TABLE vv (v character(20)); INSERT INTO vv SELECT 'abc' || 'def'; SELECT v, length(v) FROM vv; v | length ----------------------+-------- abcdef | 20 (1 row)</PRE ><P></P ><P >What has really happened here is that the two unknown literals are resolved to <TT CLASS="TYPE" >text</TT > by default, allowing the <TT CLASS="LITERAL" >||</TT > operator to be resolved as <TT CLASS="TYPE" >text</TT > concatenation. Then the <TT CLASS="TYPE" >text</TT > result of the operator is converted to <TT CLASS="TYPE" >bpchar</TT > (<SPAN CLASS="QUOTE" >"blank-padded char"</SPAN >, the internal name of the <TT CLASS="TYPE" >character</TT > data type) to match the target column type. (Since the types <TT CLASS="TYPE" >text</TT > and <TT CLASS="TYPE" >bpchar</TT > are binary-compatible, this conversion does not insert any real function call.) Finally, the sizing function <TT CLASS="LITERAL" >bpchar(bpchar, integer)</TT > is found in the system catalog and applied to the operator's result and the stored column length. This type-specific function performs the required length check and addition of padding spaces.</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="typeconv-func.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-union-case.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Functions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="typeconv.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><TT CLASS="LITERAL" >UNION</TT >, <TT CLASS="LITERAL" >CASE</TT >, and <TT CLASS="LITERAL" >ARRAY</TT > Constructs</TD ></TR ></TABLE ></DIV ></BODY ></HTML >