2 doc/src/sgml/ref/alter_type.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERTYPE">
8 <refentrytitle>ALTER TYPE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>ALTER TYPE</refname>
16 change the definition of a type
20 <indexterm zone="sql-altertype">
21 <primary>ALTER TYPE</primary>
26 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> <replaceable class="PARAMETER">action</replaceable> [, ... ]
27 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
28 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> TO <replaceable class="PARAMETER">new_attribute_name</replaceable>
29 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> [ CASCADE | RESTRICT ]
30 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</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">existing_enum_value</replaceable> ]
33 <phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
35 ADD ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ CASCADE | RESTRICT ]
36 DROP ATTRIBUTE [ IF EXISTS ] <replaceable class="PARAMETER">attribute_name</replaceable> [ CASCADE | RESTRICT ]
37 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 ]
42 <title>Description</title>
45 <command>ALTER TYPE</command> changes the definition of an existing type.
46 There are several subforms:
50 <term><literal>ADD ATTRIBUTE</literal></term>
53 This form adds a new attribute to a composite type, using the same syntax as
54 <xref linkend="SQL-CREATETYPE">.
60 <term><literal>DROP ATTRIBUTE [ IF EXISTS ]</literal></term>
63 This form drops an attribute from a composite type.
64 If <literal>IF EXISTS</literal> is specified and the attribute
65 does not exist, no error is thrown. In this case a notice
72 <term><literal>SET DATA TYPE</literal></term>
75 This form changes the type of an attribute of a composite type.
81 <term><literal>OWNER</literal></term>
84 This form changes the owner of the type.
90 <term><literal>RENAME</literal></term>
93 This form changes the name of the type or the name of an
94 individual attribute of a composite type.
100 <term><literal>SET SCHEMA</literal></term>
103 This form moves the type into another schema.
109 <term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
112 This form adds a new value to an enum type. The new value's place in
113 the enum's ordering can be specified as being <literal>BEFORE</literal>
114 or <literal>AFTER</literal> one of the existing values. Otherwise,
115 the new item is added at the end of the list of values.
118 If <literal>IF NOT EXISTS</literal> is specified, it is not an error if
119 the type already contains the new value: a notice is issued but no other
120 action is taken. Otherwise, an error will occur if the new value is
127 <term><literal>CASCADE</literal></term>
130 Automatically propagate the operation to typed tables of the
131 type being altered, and their descendants.
137 <term><literal>RESTRICT</literal></term>
140 Refuse the operation if the type being altered is the type of a
141 typed table. This is the default.
149 The <literal>ADD ATTRIBUTE</literal>, <literal>DROP
150 ATTRIBUTE</literal>, and <literal>ALTER ATTRIBUTE</literal> actions
151 can be combined into a list of multiple alterations to apply in
152 parallel. For example, it is possible to add several attributes
153 and/or alter the type of several attributes in a single command.
157 You must own the type to use <command>ALTER TYPE</>.
158 To change the schema of a type, you must also have
159 <literal>CREATE</literal> privilege on the new schema.
160 To alter the owner, you must also be a direct or indirect member of the new
161 owning role, and that role must have <literal>CREATE</literal> privilege on
162 the type's schema. (These restrictions enforce that altering the owner
163 doesn't do anything you couldn't do by dropping and recreating the type.
164 However, a superuser can alter ownership of any type anyway.)
165 To add an attribute or alter an attribute type, you must also
166 have <literal>USAGE</literal> privilege on the data type.
171 <title>Parameters</title>
176 <term><replaceable class="PARAMETER">name</replaceable></term>
179 The name (possibly schema-qualified) of an existing type to
186 <term><replaceable class="PARAMETER">new_name</replaceable></term>
189 The new name for the type.
195 <term><replaceable class="PARAMETER">new_owner</replaceable></term>
198 The user name of the new owner of the type.
204 <term><replaceable class="PARAMETER">new_schema</replaceable></term>
207 The new schema for the type.
213 <term><replaceable class="PARAMETER">attribute_name</replaceable></term>
216 The name of the attribute to add, alter, or drop.
222 <term><replaceable class="PARAMETER">new_attribute_name</replaceable></term>
225 The new name of the attribute to be renamed.
231 <term><replaceable class="PARAMETER">data_type</replaceable></term>
234 The data type of the attribute to add, or the new type of the
241 <term><replaceable class="PARAMETER">new_enum_value</replaceable></term>
244 The new value to be added to an enum type's list of values.
245 Like all enum literals, it needs to be quoted.
251 <term><replaceable class="PARAMETER">existing_enum_value</replaceable></term>
254 The existing enum value that the new value should be added immediately
255 before or after in the enum type's sort ordering.
256 Like all enum literals, it needs to be quoted.
269 <command>ALTER TYPE ... ADD VALUE</> (the form that adds a new value to an
270 enum type) cannot be executed inside a transaction block.
274 Comparisons involving an added enum value will sometimes be slower than
275 comparisons involving only original members of the enum type. This will
276 usually only occur if <literal>BEFORE</literal> or <literal>AFTER</literal>
277 is used to set the new value's sort position somewhere other than at the
278 end of the list. However, sometimes it will happen even though the new
279 value is added at the end (this occurs if the OID counter <quote>wrapped
280 around</> since the original creation of the enum type). The slowdown is
281 usually insignificant; but if it matters, optimal performance can be
282 regained by dropping and recreating the enum type, or by dumping and
283 reloading the database.
288 <title>Examples</title>
291 To rename a data type:
293 ALTER TYPE electronic_mail RENAME TO email;
298 To change the owner of the type <literal>email</literal>
299 to <literal>joe</literal>:
301 ALTER TYPE email OWNER TO joe;
306 To change the schema of the type <literal>email</literal>
307 to <literal>customers</literal>:
309 ALTER TYPE email SET SCHEMA customers;
314 To add a new attribute to a type:
316 ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
321 To add a new value to an enum type in a particular sort position:
323 ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
324 </programlisting></para>
328 <title>Compatibility</title>
331 The variants to add and drop attributes are part of the SQL
332 standard; the other variants are PostgreSQL extensions.
336 <refsect1 id="SQL-ALTERTYPE-see-also">
337 <title>See Also</title>
339 <simplelist type="inline">
340 <member><xref linkend="sql-createtype"></member>
341 <member><xref linkend="sql-droptype"></member>