Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/ref/alter_type.sgml`
0ce8635a21cdca83887707117cba0cbeecdb627d306718f50000000100000da8
 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>

Title: ALTER TYPE Command: Notes, Examples, Compatibility, and See Also
Summary
This section contains notes about the performance of comparisons involving added enum values, examples of using ALTER TYPE to rename a data type, change the owner or schema, add attributes to a composite type, add and rename enum values, and create binary I/O functions for a base type. It also mentions the SQL standard compatibility of certain variants of the command and provides a list of related commands (CREATE TYPE, DROP TYPE).