<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>10.4. Value Storage</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="typeconv-func.html" title="10.3. Functions" /><link rel="next" href="typeconv-union-case.html" title="10.5. UNION, CASE, and Related Constructs" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">10.4. Value Storage</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="typeconv-func.html" title="10.3. Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="typeconv.html" title="Chapter 10. Type Conversion">Up</a></td><th width="60%" align="center">Chapter 10. Type Conversion</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="typeconv-union-case.html" title="10.5. UNION, CASE, and Related Constructs">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="TYPECONV-QUERY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">10.4. Value Storage</h2></div></div></div><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" id="id-1.5.9.9.3"><p class="title"><strong>Value Storage Type Conversion</strong></p><ol class="procedure" 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 is possible
if an <em class="firstterm">assignment cast</em> between the two types is registered in the
<code class="structname">pg_cast</code> catalog (see <a class="xref" href="sql-createcast.html" title="CREATE CAST"><span class="refentrytitle">CREATE CAST</span></a>).
Alternatively, 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
<code class="structname">pg_cast</code> 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 <code class="type">integer</code>, which receives
the destination column's <code class="structfield">atttypmod</code> value (typically its
declared length, although the interpretation of <code class="structfield">atttypmod</code>
varies for different data types), and it may take a third <code class="type">boolean</code>
parameter that says whether the cast is explicit or implicit. The cast
function
is responsible for applying any length-dependent semantics such as size
checking or truncation.
</p></li></ol></div><div class="example" id="id-1.5.9.9.4"><p class="title"><strong>Example 10.9. <code class="type">character</code> Storage Type Conversion</strong></p><div class="example-contents"><p>
For a target column declared as <code class="type">character(20)</code> the following
statement shows 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, octet_length(v) FROM vv;
v | octet_length
----------------------+--------------
abcdef | 20
(1 row)
</pre><p>
</p><p>
What has really happened here is that the two unknown literals are resolved
to <code class="type">text</code> by default, allowing the <code class="literal">||</code> operator
to be resolved as <code class="type">text</code> concatenation. Then the <code class="type">text</code>
result of the operator is converted to <code class="type">bpchar</code> (<span class="quote">“<span class="quote">blank-padded
char</span>”</span>, the internal name of the <code class="type">character</code> data type) to match the target
column type. (Since the conversion from <code class="type">text</code> to
<code class="type">bpchar</code> is binary-coercible, this conversion does
not insert any real function call.) Finally, the sizing function
<code class="literal">bpchar(bpchar, integer, boolean)</code> 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><br class="example-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="typeconv-func.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="typeconv.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="typeconv-union-case.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">10.3. Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 10.5. <code class="literal">UNION</code>, <code class="literal">CASE</code>, and Related Constructs</td></tr></table></div></body></html>
