<!--
doc/src/sgml/ref/create_cast.sgml
PostgreSQL documentation
-->

<refentry id="sql-createcast">
 <indexterm zone="sql-createcast">
  <primary>CREATE CAST</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE CAST</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE CAST</refname>
  <refpurpose>define a new cast</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
    WITH FUNCTION <replaceable>function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]
</synopsis>
 </refsynopsisdiv>

 <refsect1 id="sql-createcast-description">
  <title>Description</title>

  <para>
   <command>CREATE CAST</command> defines a new cast.  A cast
   specifies how to perform a conversion between
   two data types.  For example,
<programlisting>
SELECT CAST(42 AS float8);
</programlisting>
   converts the integer constant 42 to type <type>float8</type> by
   invoking a previously specified function, in this case
   <literal>float8(int4)</literal>. (If no suitable cast has been defined, the
   conversion fails.)
  </para>

  <para>
   Two types can be <firstterm>binary coercible</firstterm>, which
   means that the conversion can be performed <quote>for free</quote>
   without invoking any function.  This requires that corresponding
   values use the same internal representation.  For instance, the
   types <type>text</type> and <type>varchar</type> are binary
   coercible both ways.  Binary coercibility is not necessarily a
   symmetric relationship.  For example, the cast
   from <type>xml</type> to <type>text</type> can be performed for
   free in the present implementation, but the reverse direction
   requires a function that performs at least a syntax check.  (Two
   types that are binary coercible both ways are also referred to as
   binary compatible.)
  </para>

  <para>
   You can define a cast as an <firstterm>I/O conversion cast</firstterm> by using
   the <literal>WITH INOUT</literal> syntax. An I/O conversion cast is
   performed by invoking the output function of the source data type, and
   passing the resulting string to the input function of the target data type.
   In many common cases, this feature avoids the need to write a separate
   cast function for conversion. An I/O conversion cast acts the same as
   a regular function-based cast; only the implementation is different.
  </para>

  <para>
   By default, a cast can be invoked only by an explicit cast request,
   that is an explicit <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