path: root/doc/src/sgml/ref/alter_type.sgml

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

<refentry id="sql-altertype">
 <indexterm zone="sql-altertype">
  <primary>ALTER TYPE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>ALTER TYPE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>ALTER TYPE</refname>
  <refpurpose>
   change the definition of a type
  </refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
ALTER TYPE <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
ALTER TYPE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME ATTRIBUTE <replaceable class="parameter">attribute_name</replaceable> TO <replaceable class="parameter">new_attribute_name</replaceable> [ CASCADE | RESTRICT ]
ALTER TYPE <replaceable class="parameter">name</replaceable> <replaceable class="parameter">action</replaceable> [, ... ]
ALTER TYPE <replaceable class="parameter">name</replaceable> ADD VALUE [ IF NOT EXISTS ] <replaceable class="parameter">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="parameter">neighbor_enum_value</replaceable> ]
ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME VALUE <replaceable class="parameter">existing_enum_value</replaceable> TO <replaceable class="parameter">new_enum_value</replaceable>
ALTER TYPE <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">property</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )

<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>

    ADD ATTRIBUTE <replaceable class="parameter">attribute_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ CASCADE | RESTRICT ]
    DROP ATTRIBUTE [ IF EXISTS ] <replaceable class="parameter">attribute_name</replaceable> [ CASCADE | RESTRICT ]
    ALTER ATTRIBUTE <replaceable class="parameter">attribute_name</replaceable> [ SET DATA ] TYPE <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ CASCADE | RESTRICT ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>ALTER TYPE</command> changes the definition of an existing type.
   There are several subforms:

  <variablelist>
   <varlistentry>
    <term><literal>OWNER</literal></term>
    <listitem>
     <para>
      This form changes the owner of the type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>RENAME</literal></term>
    <listitem>
     <para>
      This form changes the name of the type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>SET SCHEMA</literal></term>
    <listitem>
     <para>
      This form moves the type into another schema.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>RENAME ATTRIBUTE</literal></term>
    <listitem>
     <para>
      This form is only usable with composite types.
      It changes the name of an individual attribute of the type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>ADD ATTRIBUTE</literal></term>
    <listitem>
     <para>
      This form adds a new attribute to a composite type, using the same syntax as
      <link linkend="sql-createtype"><command>CREATE TYPE</command></link>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>DROP ATTRIBUTE [ IF EXISTS ]</literal></term>
    <listitem>
     <para>
      This form drops an attribute from a composite type.
      If <literal>IF EXISTS</literal> is specified and the attribute
      does not exist, no error is thrown. In this case a notice
      is issued instead.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>ALTER ATTRIBUTE ... SET DATA TYPE</literal></term>
    <listitem>
     <para>
      This form changes the type of an attribute of a composite type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
    <listitem>
     <para>
      This form adds a new value to an enum type.  The new value's place in
      the enum's ordering can be specified as being <literal>BEFORE</literal>
      or <literal>AFTER</literal> one of the existing values.  Otherwise,
      the new item is added at the end of the list of values.
     </para>
     <para>
      If <literal>IF NOT EXISTS</literal> is specified, it is not an error if
      the type already contains the new value: a notice is issued but no other
      action is taken. Otherwise, an error will occur if the new value is
      already present.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>RENAME VALUE</literal></term>
    <listitem>
     <para>
      This form renames a value of an enum type.
      The value's place in the enum's ordering is not affected.
      An error will occur if the specified value is not present or the new
      name is already present.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <literal>SET ( <replaceable class="parameter">property</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )</literal>
    </term>
    <listitem>
     <para>
      This form is only applicable to base types.  It allows adjustment of a
      subset of the base-type properties that can be set in <command>CREATE
      TYPE</command>.  Specifically, these properties can be changed:
      <itemizedlist>
       <listitem>
        <para>
         <literal>RECEIVE</literal> can be set to the name of a binary input
         function, or <literal>NONE</literal> to remove the type's binary
         input function.  Using this option requires superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>SEND</literal> can be set to the name of a binary output
         function, or <literal>NONE</literal> to remove the type's binary
         output function.  Using this option requires superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>TYPMOD_IN</literal> can be set to the name of a type
         modifier input function, or <literal>NONE</literal> to remove the
         type's type modifier input function.  Using this option requires
         superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>TYPMOD_OUT</literal> can be set to the name of a type
         modifier output function, or <literal>NONE</literal> to remove the
         type's type modifier output function.  Using this option requires
         superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>ANALYZE</literal> can be set to the name of a type-specific
         statistics collection function, or <literal>NONE</literal> to remove
         the type's statistics collection function.  Using this option
         requires superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>SUBSCRIPT</literal> can be set to the name of a type-specific
         subscripting handler function, or <literal>NONE</literal> to remove
         the type's subscripting handler function.  Using this option
         requires superuser privilege.
        </para>
       </listitem>
       <listitem>
        <para>
         <literal>STORAGE</literal><indexterm>
          <primary>TOAST</primary>
          <secondary>per-type storage settings</secondary>
         </indexterm>
         can be set to <literal>plain</literal>,
         <literal>extended</literal>, <literal>external</literal>,
         or <literal>main</literal> (see <xref linkend="storage-toast"/> for
         more information about what these mean).  However, changing
         from <literal>plain</literal> to another setting requires superuser
         privilege (because it requires that the type's C functions all be
         TOAST-ready), and changing to <literal>plain</literal> from another
         setting is not allowed at all (since the type may already have
         TOASTed values present in the database).  Note that changing this
         option doesn't by itself change any stored data, it just sets the
         default TOAST strategy to be used for table columns created in the
         future.  See <xref linkend="sql-altertable"/> to change the TOAST
         strategy for existing table columns.
        </para>
       </listitem>
      </itemizedlist>
      See <xref linkend="sql-createtype"/> for more details about these
      type properties.  Note that where appropriate, a change in these
      properties for a base type will be propagated automatically to domains
      based on that type.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
  </para>

  <para>
   The <literal>ADD ATTRIBUTE</literal>, <literal>DROP
   ATTRIBUTE</literal>, and <literal>ALTER ATTRIBUTE</literal> actions
   can be combined into a list of multiple alterations to apply in
   parallel.  For example, it is possible to add several attributes
   and/or alter the type of several attributes in a single command.
  </para>

  <para>
   You must own the type to use <command>ALTER TYPE</command>.
   To change the schema of a type, you must also have
   <literal>CREATE</literal> privilege on the new schema.
   To alter the owner, you must be able to <literal>SET ROLE</literal> to the
   new owning role, and that role must have <literal>CREATE</literal>
   privilege on the type's schema.
   (These restrictions enforce that altering the owner
   doesn't do anything you couldn't do by dropping and recreating the type.
   However, a superuser can alter ownership of any type anyway.)
   To add an attribute or alter an attribute type, you must also
   have <literal>USAGE</literal> privilege on the attribute's data type.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

   <para>
    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
        The name (possibly schema-qualified) of an existing type to
        alter.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">new_name</replaceable></term>
      <listitem>
       <para>
        The new name for the type.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">new_owner</replaceable></term>
      <listitem>
       <para>
        The user name of the new owner of the type.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">new_schema</replaceable></term>
      <listitem>
       <para>
        The new schema for the type.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">attribute_name</replaceable></term>
      <listitem>
       <para>
        The name of the attribute to add, alter, or drop.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">new_attribute_name</replaceable></term>
      <listitem>
       <para>
        The new name of the attribute to be renamed.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">data_type</replaceable></term>
      <listitem>
       <para>
        The data type of the attribute to add, or the new type of the
        attribute to alter.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">new_enum_value</replaceable></term>
      <listitem>
       <para>
        The new value to be added to an enum type's list of values,
        or the new name to be given to an existing value.
        Like all enum literals, it needs to be quoted.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">neighbor_enum_value</replaceable></term>
      <listitem>
       <para>
        The existing enum value that the new value should be added immediately
        before or after in the enum type's sort ordering.
        Like all enum literals, it needs to be quoted.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">existing_enum_value</replaceable></term>
      <listitem>
       <para>
        The existing enum value that should be renamed.
        Like all enum literals, it needs to be quoted.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">property</replaceable></term>
      <listitem>
       <para>
        The name of a base-type property to be modified; see above for
        possible values.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>CASCADE</literal></term>
      <listitem>
       <para>
        Automatically propagate the operation to typed tables of the
        type being altered, and their descendants.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>RESTRICT</literal></term>
      <listitem>
       <para>
        Refuse the operation if the type being altered is the type of a
        typed table.  This is the default.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>
  </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   If <command>ALTER TYPE ... ADD VALUE</command> (the form that adds a new
   value to an enum type) is executed inside a transaction block, the new
   value cannot be used until after the transaction has been committed.
  </para>

  <para>
   Comparisons involving an added enum value will sometimes be slower than
   comparisons involving only original members of the enum type.  This will
   usually only occur if <literal>BEFORE</literal> or <literal>AFTER</literal>
   is used to set the new value's sort position somewhere other than at the
   end of the list.  However, sometimes it will happen even though the new
   value is added at the end (this occurs if the OID counter <quote>wrapped
   around</quote> since the original creation of the enum type).  The slowdown is
   usually insignificant; but if it matters, optimal performance can be
   regained by dropping and recreating the enum type, or by dumping and
   restoring the database.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To rename a data type:
<programlisting>
ALTER TYPE electronic_mail RENAME TO email;
</programlisting>
  </para>

  <para>
   To change the owner of the type <literal>email</literal>
   to <literal>joe</literal>:
<programlisting>
ALTER TYPE email OWNER TO joe;
</programlisting>
  </para>

  <para>
   To change the schema of the type <literal>email</literal>
   to <literal>customers</literal>:
<programlisting>
ALTER TYPE email SET SCHEMA customers;
</programlisting>
  </para>

  <para>
   To add a new attribute to a composite type:
<programlisting>
ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
</programlisting>
  </para>

  <para>
   To add a new value to an enum type in a particular sort position:
<programlisting>
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
</programlisting>
  </para>

  <para>
   To rename an enum value:
<programlisting>
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
</programlisting>
  </para>

  <para>
   To create binary I/O functions for an existing base type:
<programlisting>
CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
ALTER TYPE mytype SET (
    SEND = mytypesend,
    RECEIVE = mytyperecv
);
</programlisting></para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   The variants to add and drop attributes are part of the SQL
   standard; the other variants are PostgreSQL extensions.
  </para>
 </refsect1>

 <refsect1 id="sql-altertype-see-also">
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createtype"/></member>
   <member><xref linkend="sql-droptype"/></member>
  </simplelist>
 </refsect1>
</refentry>