Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-createcast.html256 linesDownload Raw Back to html
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>