doc/src/sgml/ref/alter_type.sgml

   1 <!--
   2 doc/src/sgml/ref/alter_type.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-altertype">
   7  <indexterm zone="sql-altertype">
   8   <primary>ALTER TYPE</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>ALTER TYPE</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>ALTER TYPE</refname>
  19   <refpurpose>
  20    change the definition of a type
  21   </refpurpose>
  22  </refnamediv>
  23
  24  <refsynopsisdiv>
  25 <synopsis>
  26 ALTER TYPE <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  27 ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
  28 ALTER TYPE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
  29 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 ]
  30 ALTER TYPE <replaceable class="parameter">name</replaceable> <replaceable class="parameter">action</replaceable> [, ... ]
  31 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> ]
  32 ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME VALUE <replaceable class="parameter">existing_enum_value</replaceable> TO <replaceable class="parameter">new_enum_value</replaceable>
  33 ALTER TYPE <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">property</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
  34
  35 <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
  36
  37     ADD ATTRIBUTE <replaceable class="parameter">attribute_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ CASCADE | RESTRICT ]
  38     DROP ATTRIBUTE [ IF EXISTS ] <replaceable class="parameter">attribute_name</replaceable> [ CASCADE | RESTRICT ]
  39     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 ]
  40 </synopsis>
  41  </refsynopsisdiv>
  42
  43  <refsect1>
  44   <title>Description</title>
  45
  46   <para>
  47    <command>ALTER TYPE</command> changes the definition of an existing type.
  48    There are several subforms:
  49
  50   <variablelist>
  51    <varlistentry>
  52     <term><literal>OWNER</literal></term>
  53     <listitem>
  54      <para>
  55       This form changes the owner of the type.
  56      </para>
  57     </listitem>
  58    </varlistentry>
  59
  60    <varlistentry>
  61     <term><literal>RENAME</literal></term>
  62     <listitem>
  63      <para>
  64       This form changes the name of the type.
  65      </para>
  66     </listitem>
  67    </varlistentry>
  68
  69    <varlistentry>
  70     <term><literal>SET SCHEMA</literal></term>
  71     <listitem>
  72      <para>
  73       This form moves the type into another schema.
  74      </para>
  75     </listitem>
  76    </varlistentry>
  77
  78    <varlistentry>
  79     <term><literal>RENAME ATTRIBUTE</literal></term>
  80     <listitem>
  81      <para>
  82       This form is only usable with composite types.
  83       It changes the name of an individual attribute of the type.
  84      </para>
  85     </listitem>
  86    </varlistentry>
  87
  88    <varlistentry>
  89     <term><literal>ADD ATTRIBUTE</literal></term>
  90     <listitem>
  91      <para>
  92       This form adds a new attribute to a composite type, using the same syntax as
  93       <link linkend="sql-createtype"><command>CREATE TYPE</command></link>.
  94      </para>
  95     </listitem>
  96    </varlistentry>
  97
  98    <varlistentry>
  99     <term><literal>DROP ATTRIBUTE [ IF EXISTS ]</literal></term>
 100     <listitem>
 101      <para>
 102       This form drops an attribute from a composite type.
 103       If <literal>IF EXISTS</literal> is specified and the attribute
 104       does not exist, no error is thrown. In this case a notice
 105       is issued instead.
 106      </para>
 107     </listitem>
 108    </varlistentry>
 109
 110    <varlistentry>
 111     <term><literal>ALTER ATTRIBUTE ... SET DATA TYPE</literal></term>
 112     <listitem>
 113      <para>
 114       This form changes the type of an attribute of a composite type.
 115      </para>
 116     </listitem>
 117    </varlistentry>
 118
 119    <varlistentry>
 120     <term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
 121     <listitem>
 122      <para>
 123       This form adds a new value to an enum type.  The new value's place in
 124       the enum's ordering can be specified as being <literal>BEFORE</literal>
 125       or <literal>AFTER</literal> one of the existing values.  Otherwise,
 126       the new item is added at the end of the list of values.
 127      </para>
 128      <para>
 129       If <literal>IF NOT EXISTS</literal> is specified, it is not an error if
 130       the type already contains the new value: a notice is issued but no other
 131       action is taken. Otherwise, an error will occur if the new value is
 132       already present.
 133      </para>
 134     </listitem>
 135    </varlistentry>
 136
 137    <varlistentry>
 138     <term><literal>RENAME VALUE</literal></term>
 139     <listitem>
 140      <para>
 141       This form renames a value of an enum type.
 142       The value's place in the enum's ordering is not affected.
 143       An error will occur if the specified value is not present or the new
 144       name is already present.
 145      </para>
 146     </listitem>
 147    </varlistentry>
 148
 149    <varlistentry>
 150     <term>
 151      <literal>SET ( <replaceable class="parameter">property</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )</literal>
 152     </term>
 153     <listitem>
 154      <para>
 155       This form is only applicable to base types.  It allows adjustment of a
 156       subset of the base-type properties that can be set in <command>CREATE
 157       TYPE</command>.  Specifically, these properties can be changed:
 158       <itemizedlist>
 159        <listitem>
 160         <para>
 161          <literal>RECEIVE</literal> can be set to the name of a binary input
 162          function, or <literal>NONE</literal> to remove the type's binary
 163          input function.  Using this option requires superuser privilege.
 164         </para>
 165        </listitem>
 166        <listitem>
 167         <para>
 168          <literal>SEND</literal> can be set to the name of a binary output
 169          function, or <literal>NONE</literal> to remove the type's binary
 170          output function.  Using this option requires superuser privilege.
 171         </para>
 172        </listitem>
 173        <listitem>
 174         <para>
 175          <literal>TYPMOD_IN</literal> can be set to the name of a type
 176          modifier input function, or <literal>NONE</literal> to remove the
 177          type's type modifier input function.  Using this option requires
 178          superuser privilege.
 179         </para>
 180        </listitem>
 181        <listitem>
 182         <para>
 183          <literal>TYPMOD_OUT</literal> can be set to the name of a type
 184          modifier output function, or <literal>NONE</literal> to remove the
 185          type's type modifier output function.  Using this option requires
 186          superuser privilege.
 187         </para>
 188        </listitem>
 189        <listitem>
 190         <para>
 191          <literal>ANALYZE</literal> can be set to the name of a type-specific
 192          statistics collection function, or <literal>NONE</literal> to remove
 193          the type's statistics collection function.  Using this option
 194          requires superuser privilege.
 195         </para>
 196        </listitem>
 197        <listitem>
 198         <para>
 199          <literal>SUBSCRIPT</literal> can be set to the name of a type-specific
 200          subscripting handler function, or <literal>NONE</literal> to remove
 201          the type's subscripting handler function.  Using this option
 202          requires superuser privilege.
 203         </para>
 204        </listitem>
 205        <listitem>
 206         <para>
 207          <literal>STORAGE</literal><indexterm>
 208           <primary>TOAST</primary>
 209           <secondary>per-type storage settings</secondary>
 210          </indexterm>
 211          can be set to <literal>plain</literal>,
 212          <literal>extended</literal>, <literal>external</literal>,
 213          or <literal>main</literal> (see <xref linkend="storage-toast"/> for
 214          more information about what these mean).  However, changing
 215          from <literal>plain</literal> to another setting requires superuser
 216          privilege (because it requires that the type's C functions all be
 217          TOAST-ready), and changing to <literal>plain</literal> from another
 218          setting is not allowed at all (since the type may already have
 219          TOASTed values present in the database).  Note that changing this
 220          option doesn't by itself change any stored data, it just sets the
 221          default TOAST strategy to be used for table columns created in the
 222          future.  See <xref linkend="sql-altertable"/> to change the TOAST
 223          strategy for existing table columns.
 224         </para>
 225        </listitem>
 226       </itemizedlist>
 227       See <xref linkend="sql-createtype"/> for more details about these
 228       type properties.  Note that where appropriate, a change in these
 229       properties for a base type will be propagated automatically to domains
 230       based on that type.
 231      </para>
 232     </listitem>
 233    </varlistentry>
 234   </variablelist>
 235   </para>
 236
 237   <para>
 238    The <literal>ADD ATTRIBUTE</literal>, <literal>DROP
 239    ATTRIBUTE</literal>, and <literal>ALTER ATTRIBUTE</literal> actions
 240    can be combined into a list of multiple alterations to apply in
 241    parallel.  For example, it is possible to add several attributes
 242    and/or alter the type of several attributes in a single command.
 243   </para>
 244
 245   <para>
 246    You must own the type to use <command>ALTER TYPE</command>.
 247    To change the schema of a type, you must also have
 248    <literal>CREATE</literal> privilege on the new schema.
 249    To alter the owner, you must be able to <literal>SET ROLE</literal> to the
 250    new owning role, and that role must have <literal>CREATE</literal>
 251    privilege on the type's schema.
 252    (These restrictions enforce that altering the owner
 253    doesn't do anything you couldn't do by dropping and recreating the type.
 254    However, a superuser can alter ownership of any type anyway.)
 255    To add an attribute or alter an attribute type, you must also
 256    have <literal>USAGE</literal> privilege on the attribute's data type.
 257   </para>
 258  </refsect1>
 259
 260  <refsect1>
 261   <title>Parameters</title>
 262
 263    <para>
 264     <variablelist>
 265      <varlistentry>
 266       <term><replaceable class="parameter">name</replaceable></term>
 267       <listitem>
 268        <para>
 269         The name (possibly schema-qualified) of an existing type to
 270         alter.
 271        </para>
 272       </listitem>
 273      </varlistentry>
 274
 275      <varlistentry>
 276       <term><replaceable class="parameter">new_name</replaceable></term>
 277       <listitem>
 278        <para>
 279         The new name for the type.
 280        </para>
 281       </listitem>
 282      </varlistentry>
 283
 284      <varlistentry>
 285       <term><replaceable class="parameter">new_owner</replaceable></term>
 286       <listitem>
 287        <para>
 288         The user name of the new owner of the type.
 289        </para>
 290       </listitem>
 291      </varlistentry>
 292
 293      <varlistentry>
 294       <term><replaceable class="parameter">new_schema</replaceable></term>
 295       <listitem>
 296        <para>
 297         The new schema for the type.
 298        </para>
 299       </listitem>
 300      </varlistentry>
 301
 302      <varlistentry>
 303       <term><replaceable class="parameter">attribute_name</replaceable></term>
 304       <listitem>
 305        <para>
 306         The name of the attribute to add, alter, or drop.
 307        </para>
 308       </listitem>
 309      </varlistentry>
 310
 311      <varlistentry>
 312       <term><replaceable class="parameter">new_attribute_name</replaceable></term>
 313       <listitem>
 314        <para>
 315         The new name of the attribute to be renamed.
 316        </para>
 317       </listitem>
 318      </varlistentry>
 319
 320      <varlistentry>
 321       <term><replaceable class="parameter">data_type</replaceable></term>
 322       <listitem>
 323        <para>
 324         The data type of the attribute to add, or the new type of the
 325         attribute to alter.
 326        </para>
 327       </listitem>
 328      </varlistentry>
 329
 330      <varlistentry>
 331       <term><replaceable class="parameter">new_enum_value</replaceable></term>
 332       <listitem>
 333        <para>
 334         The new value to be added to an enum type's list of values,
 335         or the new name to be given to an existing value.
 336         Like all enum literals, it needs to be quoted.
 337        </para>
 338       </listitem>
 339      </varlistentry>
 340
 341      <varlistentry>
 342       <term><replaceable class="parameter">neighbor_enum_value</replaceable></term>
 343       <listitem>
 344        <para>
 345         The existing enum value that the new value should be added immediately
 346         before or after in the enum type's sort ordering.
 347         Like all enum literals, it needs to be quoted.
 348        </para>
 349       </listitem>
 350      </varlistentry>
 351
 352      <varlistentry>
 353       <term><replaceable class="parameter">existing_enum_value</replaceable></term>
 354       <listitem>
 355        <para>
 356         The existing enum value that should be renamed.
 357         Like all enum literals, it needs to be quoted.
 358        </para>
 359       </listitem>
 360      </varlistentry>
 361
 362      <varlistentry>
 363       <term><replaceable class="parameter">property</replaceable></term>
 364       <listitem>
 365        <para>
 366         The name of a base-type property to be modified; see above for
 367         possible values.
 368        </para>
 369       </listitem>
 370      </varlistentry>
 371
 372      <varlistentry>
 373       <term><literal>CASCADE</literal></term>
 374       <listitem>
 375        <para>
 376         Automatically propagate the operation to typed tables of the
 377         type being altered, and their descendants.
 378        </para>
 379       </listitem>
 380      </varlistentry>
 381
 382      <varlistentry>
 383       <term><literal>RESTRICT</literal></term>
 384       <listitem>
 385        <para>
 386         Refuse the operation if the type being altered is the type of a
 387         typed table.  This is the default.
 388        </para>
 389       </listitem>
 390      </varlistentry>
 391
 392     </variablelist>
 393    </para>
 394   </refsect1>
 395
 396  <refsect1>
 397   <title>Notes</title>
 398
 399   <para>
 400    If <command>ALTER TYPE ... ADD VALUE</command> (the form that adds a new
 401    value to an enum type) is executed inside a transaction block, the new
 402    value cannot be used until after the transaction has been committed.
 403   </para>
 404
 405   <para>
 406    Comparisons involving an added enum value will sometimes be slower than
 407    comparisons involving only original members of the enum type.  This will
 408    usually only occur if <literal>BEFORE</literal> or <literal>AFTER</literal>
 409    is used to set the new value's sort position somewhere other than at the
 410    end of the list.  However, sometimes it will happen even though the new
 411    value is added at the end (this occurs if the OID counter <quote>wrapped
 412    around</quote> since the original creation of the enum type).  The slowdown is
 413    usually insignificant; but if it matters, optimal performance can be
 414    regained by dropping and recreating the enum type, or by dumping and
 415    restoring the database.
 416   </para>
 417  </refsect1>
 418
 419  <refsect1>
 420   <title>Examples</title>
 421
 422   <para>
 423    To rename a data type:
 424 <programlisting>
 425 ALTER TYPE electronic_mail RENAME TO email;
 426 </programlisting>
 427   </para>
 428
 429   <para>
 430    To change the owner of the type <literal>email</literal>
 431    to <literal>joe</literal>:
 432 <programlisting>
 433 ALTER TYPE email OWNER TO joe;
 434 </programlisting>
 435   </para>
 436
 437   <para>
 438    To change the schema of the type <literal>email</literal>
 439    to <literal>customers</literal>:
 440 <programlisting>
 441 ALTER TYPE email SET SCHEMA customers;
 442 </programlisting>
 443   </para>
 444
 445   <para>
 446    To add a new attribute to a composite type:
 447 <programlisting>
 448 ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
 449 </programlisting>
 450   </para>
 451
 452   <para>
 453    To add a new value to an enum type in a particular sort position:
 454 <programlisting>
 455 ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
 456 </programlisting>
 457   </para>
 458
 459   <para>
 460    To rename an enum value:
 461 <programlisting>
 462 ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
 463 </programlisting>
 464   </para>
 465
 466   <para>
 467    To create binary I/O functions for an existing base type:
 468 <programlisting>
 469 CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
 470 CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
 471 ALTER TYPE mytype SET (
 472     SEND = mytypesend,
 473     RECEIVE = mytyperecv
 474 );
 475 </programlisting></para>
 476  </refsect1>
 477
 478  <refsect1>
 479   <title>Compatibility</title>
 480
 481   <para>
 482    The variants to add and drop attributes are part of the SQL
 483    standard; the other variants are PostgreSQL extensions.
 484   </para>
 485  </refsect1>
 486
 487  <refsect1 id="sql-altertype-see-also">
 488   <title>See Also</title>
 489
 490   <simplelist type="inline">
 491    <member><xref linkend="sql-createtype"/></member>
 492    <member><xref linkend="sql-droptype"/></member>
 493   </simplelist>
 494  </refsect1>
 495 </refentry>