Home Explore Blog CI



postgresql

2nd chunk of `doc/src/sgml/ref/create_cast.sgml`
6ba8e4320a044b1451aa1354cdf879fd960b6fbc3de6531f0000000100000fa1
 <literal>CAST(<replaceable>x</replaceable> AS
   <replaceable>typename</replaceable>)</literal> or
   <replaceable>x</replaceable><literal>::</literal><replaceable>typename</replaceable>
   construct.
  </para>

  <para>
   If the cast is marked <literal>AS ASSIGNMENT</literal> then it can be invoked
   implicitly when assigning a value to a column of the target data type.
   For example, supposing that <literal>foo.f1</literal> is a column of
   type <type>text</type>, then:
<programlisting>
INSERT INTO foo (f1) VALUES (42);
</programlisting>
   will be allowed if the cast from type <type>integer</type> to type
   <type>text</type> is marked <literal>AS ASSIGNMENT</literal>, otherwise not.
   (We generally use the term <firstterm>assignment
   cast</firstterm> to describe this kind of cast.)
  </para>

  <para>
   If the cast is marked <literal>AS IMPLICIT</literal> then it can be invoked
   implicitly in any context, whether assignment or internally in an
   expression.  (We generally use the term <firstterm>implicit
   cast</firstterm> to describe this kind of cast.)
   For example, consider this query:
<programlisting>
SELECT 2 + 4.0;
</programlisting>
   The parser initially marks the constants as being of type <type>integer</type>
   and <type>numeric</type> respectively.  There is no <type>integer</type>
   <literal>+</literal> <type>numeric</type> operator in the system catalogs,
   but there is a <type>numeric</type> <literal>+</literal> <type>numeric</type> operator.
   The query will therefore succeed if a cast from <type>integer</type> to
   <type>numeric</type> is available and is marked <literal>AS IMPLICIT</literal> &mdash;
   which in fact it is.  The parser will apply the implicit cast and resolve
   the query as if it had been written
<programlisting>
SELECT CAST ( 2 AS numeric ) + 4.0;
</programlisting>
  </para>

  <para>
   Now, the catalogs also provide a cast from <type>numeric</type> to
   <type>integer</type>.  If that cast were marked <literal>AS IMPLICIT</literal> &mdash;
   which it is not &mdash; then the parser would be faced with choosing
   between the above interpretation and the alternative of casting the
   <type>numeric</type> constant to <type>integer</type> and applying the
   <type>integer</type> <literal>+</literal> <type>integer</type> operator.  Lacking any
   knowledge of which choice to prefer, it would give up and declare the
   query ambiguous.  The fact that only one of the two casts is
   implicit is the way in which we teach the parser to prefer resolution
   of a mixed <type>numeric</type>-and-<type>integer</type> expression as
   <type>numeric</type>; there is no built-in knowledge about that.
  </para>

  <para>
   It is wise to be conservative about marking casts as implicit.  An
   overabundance of implicit casting paths can cause
   <productname>PostgreSQL</productname> to choose surprising
   interpretations of commands, or to be unable to resolve commands at
   all because there are multiple possible interpretations.  A good
   rule of thumb is to make a cast implicitly invokable only for
   information-preserving transformations between types in the same
   general type category.  For example, the cast from <type>int2</type> to
   <type>int4</type> can reasonably be implicit, but the cast from
   <type>float8</type> to <type>int4</type> should probably be
   assignment-only.  Cross-type-category casts, such as <type>text</type>
   to <type>int4</type>, are best made explicit-only.
  </para>

  <note>
   <para>
    Sometimes it is necessary for usability or standards-compliance reasons
    to provide multiple implicit casts among a set of types, resulting in
    ambiguity that cannot be avoided as above.  The parser has a fallback
    heuristic based on <firstterm>type categories</firstterm> and <firstterm>preferred
    types</firstterm> that can help to provide desired behavior in such cases.  See
    <xref linkend="sql-createtype"/> for
    more information.
   </para>

Title: Implicit Casts and Their Implications
Summary
This section discusses implicit casts and their impact on query resolution. Implicit casts, marked 'AS IMPLICIT', are automatically invoked in expressions, potentially leading to ambiguity if multiple implicit casts exist. The parser uses type categories and preferred types to resolve ambiguities, but it's crucial to be conservative with implicit casts to avoid surprising interpretations or resolution failures. Information-preserving transformations within the same type category are good candidates for implicit casts, while cross-type-category casts should generally be explicit.