codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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>CREATE CAST</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="sql-createaggregate.html" title="CREATE AGGREGATE" /><link rel="next" href="sql-createcollation.html" title="CREATE COLLATION" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE CAST</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createaggregate.html" title="CREATE AGGREGATE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createcollation.html" title="CREATE COLLATION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATECAST"><div class="titlepage"></div><a id="id-1.9.3.58.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE CAST</span></h2><p>CREATE CAST — define a new cast</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)4 WITH FUNCTION <em class="replaceable"><code>function_name</code></em> [ (<em class="replaceable"><code>argument_type</code></em> [, ...]) ]5 [ AS ASSIGNMENT | AS IMPLICIT ]6 7CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)8 WITHOUT FUNCTION9 [ AS ASSIGNMENT | AS IMPLICIT ]10 11CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)12 WITH INOUT13 [ AS ASSIGNMENT | AS IMPLICIT ]14</pre></div><div class="refsect1" id="SQL-CREATECAST-DESCRIPTION"><h2>Description</h2><p>15 <code class="command">CREATE CAST</code> defines a new cast. A cast16 specifies how to perform a conversion between17 two data types. For example,18</p><pre class="programlisting">19SELECT CAST(42 AS float8);20</pre><p>21 converts the integer constant 42 to type <code class="type">float8</code> by22 invoking a previously specified function, in this case23 <code class="literal">float8(int4)</code>. (If no suitable cast has been defined, the24 conversion fails.)25 </p><p>26 Two types can be <em class="firstterm">binary coercible</em>, which27 means that the conversion can be performed <span class="quote">“<span class="quote">for free</span>”</span>28 without invoking any function. This requires that corresponding29 values use the same internal representation. For instance, the30 types <code class="type">text</code> and <code class="type">varchar</code> are binary31 coercible both ways. Binary coercibility is not necessarily a32 symmetric relationship. For example, the cast33 from <code class="type">xml</code> to <code class="type">text</code> can be performed for34 free in the present implementation, but the reverse direction35 requires a function that performs at least a syntax check. (Two36 types that are binary coercible both ways are also referred to as37 binary compatible.)38 </p><p>39 You can define a cast as an <em class="firstterm">I/O conversion cast</em> by using40 the <code class="literal">WITH INOUT</code> syntax. An I/O conversion cast is41 performed by invoking the output function of the source data type, and42 passing the resulting string to the input function of the target data type.43 In many common cases, this feature avoids the need to write a separate44 cast function for conversion. An I/O conversion cast acts the same as45 a regular function-based cast; only the implementation is different.46 </p><p>47 By default, a cast can be invoked only by an explicit cast request,48 that is an explicit <code class="literal">CAST(<em class="replaceable"><code>x</code></em> AS49 <em class="replaceable"><code>typename</code></em>)</code> or50 <em class="replaceable"><code>x</code></em><code class="literal">::</code><em class="replaceable"><code>typename</code></em>51 construct.52 </p><p>53 If the cast is marked <code class="literal">AS ASSIGNMENT</code> then it can be invoked54 implicitly when assigning a value to a column of the target data type.55 For example, supposing that <code class="literal">foo.f1</code> is a column of56 type <code class="type">text</code>, then:57</p><pre class="programlisting">58INSERT INTO foo (f1) VALUES (42);59</pre><p>60 will be allowed if the cast from type <code class="type">integer</code> to type61 <code class="type">text</code> is marked <code class="literal">AS ASSIGNMENT</code>, otherwise not.62 (We generally use the term <em class="firstterm">assignment63 cast</em> to describe this kind of cast.)64 </p><p>65 If the cast is marked <code class="literal">AS IMPLICIT</code> then it can be invoked66 implicitly in any context, whether assignment or internally in an67 expression. (We generally use the term <em class="firstterm">implicit68 cast</em> to describe this kind of cast.)69 For example, consider this query:70</p><pre class="programlisting">71SELECT 2 + 4.0;72</pre><p>73 The parser initially marks the constants as being of type <code class="type">integer</code>74 and <code class="type">numeric</code> respectively. There is no <code class="type">integer</code>75 <code class="literal">+</code> <code class="type">numeric</code> operator in the system catalogs,76 but there is a <code class="type">numeric</code> <code class="literal">+</code> <code class="type">numeric</code> operator.77 The query will therefore succeed if a cast from <code class="type">integer</code> to78 <code class="type">numeric</code> is available and is marked <code class="literal">AS IMPLICIT</code> —79 which in fact it is. The parser will apply the implicit cast and resolve80 the query as if it had been written81</p><pre class="programlisting">82SELECT CAST ( 2 AS numeric ) + 4.0;83</pre><p>84 </p><p>85 Now, the catalogs also provide a cast from <code class="type">numeric</code> to86 <code class="type">integer</code>. If that cast were marked <code class="literal">AS IMPLICIT</code> —87 which it is not — then the parser would be faced with choosing88 between the above interpretation and the alternative of casting the89 <code class="type">numeric</code> constant to <code class="type">integer</code> and applying the90 <code class="type">integer</code> <code class="literal">+</code> <code class="type">integer</code> operator. Lacking any91 knowledge of which choice to prefer, it would give up and declare the92 query ambiguous. The fact that only one of the two casts is93 implicit is the way in which we teach the parser to prefer resolution94 of a mixed <code class="type">numeric</code>-and-<code class="type">integer</code> expression as95 <code class="type">numeric</code>; there is no built-in knowledge about that.96 </p><p>97 It is wise to be conservative about marking casts as implicit. An98 overabundance of implicit casting paths can cause99 <span class="productname">PostgreSQL</span> to choose surprising100 interpretations of commands, or to be unable to resolve commands at101 all because there are multiple possible interpretations. A good102 rule of thumb is to make a cast implicitly invokable only for103 information-preserving transformations between types in the same104 general type category. For example, the cast from <code class="type">int2</code> to105 <code class="type">int4</code> can reasonably be implicit, but the cast from106 <code class="type">float8</code> to <code class="type">int4</code> should probably be107 assignment-only. Cross-type-category casts, such as <code class="type">text</code>108 to <code class="type">int4</code>, are best made explicit-only.109 </p><div class="note"><h3 class="title">Note</h3><p>110 Sometimes it is necessary for usability or standards-compliance reasons111 to provide multiple implicit casts among a set of types, resulting in112 ambiguity that cannot be avoided as above. The parser has a fallback113 heuristic based on <em class="firstterm">type categories</em> and <em class="firstterm">preferred114 types</em> that can help to provide desired behavior in such cases. See115 <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for116 more information.117 </p></div><p>118 To be able to create a cast, you must own the source or the target data type119 and have <code class="literal">USAGE</code> privilege on the other type. To create a120 binary-coercible cast, you must be superuser. (This restriction is made121 because an erroneous binary-coercible cast conversion can easily crash the122 server.)123 </p></div><div class="refsect1" id="id-1.9.3.58.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>source_type</code></em></span></dt><dd><p>124 The name of the source data type of the cast.125 </p></dd><dt><span class="term"><em class="replaceable"><code>target_type</code></em></span></dt><dd><p>126 The name of the target data type of the cast.127 </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>function_name</code></em>[(<em class="replaceable"><code>argument_type</code></em> [, ...])]</code></span></dt><dd><p>128 The function used to perform the cast. The function name can129 be schema-qualified. If it is not, the function will be looked130 up in the schema search path. The function's result data type must131 match the target type of the cast. Its arguments are discussed below.132 If no argument list is specified, the function name must be unique in133 its schema.134 </p></dd><dt><span class="term"><code class="literal">WITHOUT FUNCTION</code></span></dt><dd><p>135 Indicates that the source type is binary-coercible to the target type,136 so no function is required to perform the cast.137 </p></dd><dt><span class="term"><code class="literal">WITH INOUT</code></span></dt><dd><p>138 Indicates that the cast is an I/O conversion cast, performed by139 invoking the output function of the source data type, and passing the140 resulting string to the input function of the target data type.141 </p></dd><dt><span class="term"><code class="literal">AS ASSIGNMENT</code></span></dt><dd><p>142 Indicates that the cast can be invoked implicitly in assignment143 contexts.144 </p></dd><dt><span class="term"><code class="literal">AS IMPLICIT</code></span></dt><dd><p>145 Indicates that the cast can be invoked implicitly in any context.146 </p></dd></dl></div><p>147 Cast implementation functions can have one to three arguments.148 The first argument type must be identical to or binary-coercible from149 the cast's source type. The second argument,150 if present, must be type <code class="type">integer</code>; it receives the type151 modifier associated with the destination type, or <code class="literal">-1</code>152 if there is none. The third argument,153 if present, must be type <code class="type">boolean</code>; it receives <code class="literal">true</code>154 if the cast is an explicit cast, <code class="literal">false</code> otherwise.155 (Bizarrely, the SQL standard demands different behaviors for explicit and156 implicit casts in some cases. This argument is supplied for functions157 that must implement such casts. It is not recommended that you design158 your own data types so that this matters.)159 </p><p>160 The return type of a cast function must be identical to or161 binary-coercible to the cast's target type.162 </p><p>163 Ordinarily a cast must have different source and target data types.164 However, it is allowed to declare a cast with identical source and165 target types if it has a cast implementation function with more than one166 argument. This is used to represent type-specific length coercion167 functions in the system catalogs. The named function is used to168 coerce a value of the type to the type modifier value given by its169 second argument.170 </p><p>171 When a cast has different source and172 target types and a function that takes more than one argument, it173 supports converting from one type to another and applying a length174 coercion in a single step. When no such entry is available, coercion175 to a type that uses a type modifier involves two cast steps, one to176 convert between data types and a second to apply the modifier.177 </p><p>178 A cast to or from a domain type currently has no effect. Casting179 to or from a domain uses the casts associated with its underlying type.180 </p></div><div class="refsect1" id="SQL-CREATECAST-NOTES"><h2>Notes</h2><p>181 Use <a class="link" href="sql-dropcast.html" title="DROP CAST"><code class="command">DROP CAST</code></a> to remove user-defined casts.182 </p><p>183 Remember that if you want to be able to convert types both ways you184 need to declare casts both ways explicitly.185 </p><a id="id-1.9.3.58.7.4" class="indexterm"></a><p>186 It is normally not necessary to create casts between user-defined types187 and the standard string types (<code class="type">text</code>, <code class="type">varchar</code>, and188 <code class="type">char(<em class="replaceable"><code>n</code></em>)</code>, as well as user-defined types that189 are defined to be in the string category). <span class="productname">PostgreSQL</span>190 provides automatic I/O conversion casts for that. The automatic casts to191 string types are treated as assignment casts, while the automatic casts192 from string types are193 explicit-only. You can override this behavior by declaring your own194 cast to replace an automatic cast, but usually the only reason to195 do so is if you want the conversion to be more easily invokable than the196 standard assignment-only or explicit-only setting. Another possible197 reason is that you want the conversion to behave differently from the198 type's I/O function; but that is sufficiently surprising that you199 should think twice about whether it's a good idea. (A small number of200 the built-in types do indeed have different behaviors for conversions,201 mostly because of requirements of the SQL standard.)202 </p><p>203 While not required, it is recommended that you continue to follow this old204 convention of naming cast implementation functions after the target data205 type. Many users are used to being able to cast data types using a206 function-style notation, that is207 <em class="replaceable"><code>typename</code></em>(<em class="replaceable"><code>x</code></em>). This notation is in fact208 nothing more nor less than a call of the cast implementation function; it209 is not specially treated as a cast. If your conversion functions are not210 named to support this convention then you will have surprised users.211 Since <span class="productname">PostgreSQL</span> allows overloading of the same function212 name with different argument types, there is no difficulty in having213 multiple conversion functions from different types that all use the214 target type's name.215 </p><div class="note"><h3 class="title">Note</h3><p>216 Actually the preceding paragraph is an oversimplification: there are217 two cases in which a function-call construct will be treated as a cast218 request without having matched it to an actual function.219 If a function call <em class="replaceable"><code>name</code></em>(<em class="replaceable"><code>x</code></em>) does not220 exactly match any existing function, but <em class="replaceable"><code>name</code></em> is the name221 of a data type and <code class="structname">pg_cast</code> provides a binary-coercible cast222 to this type from the type of <em class="replaceable"><code>x</code></em>, then the call will be223 construed as a binary-coercible cast. This exception is made so that224 binary-coercible casts can be invoked using functional syntax, even225 though they lack any function. Likewise, if there is no226 <code class="structname">pg_cast</code> entry but the cast would be to or from a string227 type, the call will be construed as an I/O conversion cast. This228 exception allows I/O conversion casts to be invoked using functional229 syntax.230 </p></div><div class="note"><h3 class="title">Note</h3><p>231 There is also an exception to the exception: I/O conversion casts from232 composite types to string types cannot be invoked using functional233 syntax, but must be written in explicit cast syntax (either234 <code class="literal">CAST</code> or <code class="literal">::</code> notation). This exception was added235 because after the introduction of automatically-provided I/O conversion236 casts, it was found too easy to accidentally invoke such a cast when237 a function or column reference was intended.238 </p></div></div><div class="refsect1" id="SQL-CREATECAST-EXAMPLES"><h2>Examples</h2><p>239 To create an assignment cast from type <code class="type">bigint</code> to type240 <code class="type">int4</code> using the function <code class="literal">int4(bigint)</code>:241</p><pre class="programlisting">242CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;243</pre><p>244 (This cast is already predefined in the system.)245 </p></div><div class="refsect1" id="SQL-CREATECAST-COMPAT"><h2>Compatibility</h2><p>246 The <code class="command">CREATE CAST</code> command conforms to the247 <acronym class="acronym">SQL</acronym> standard,248 except that SQL does not make provisions for binary-coercible249 types or extra arguments to implementation functions.250 <code class="literal">AS IMPLICIT</code> is a <span class="productname">PostgreSQL</span>251 extension, too.252 </p></div><div class="refsect1" id="SQL-CREATECAST-SEEALSO"><h2>See Also</h2><p>253 <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>,254 <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a>,255 <a class="xref" href="sql-dropcast.html" title="DROP CAST"><span class="refentrytitle">DROP CAST</span></a>256 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createaggregate.html" title="CREATE AGGREGATE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createcollation.html" title="CREATE COLLATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE AGGREGATE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE COLLATION</td></tr></table></div></body></html>