1ALTER TYPE(7) PostgreSQL 13.3 Documentation ALTER TYPE(7)
2
6 ALTER_TYPE - change the definition of a type
7
9 ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
10 ALTER TYPE name RENAME TO new_name
11 ALTER TYPE name SET SCHEMA new_schema
12 ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
13 ALTER TYPE name action [, ... ]
14 ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
15 ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
16 ALTER TYPE name SET ( property = value [, ... ] )
17 where action is one of:
19 ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
21 DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
22 ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
23
25 ALTER TYPE changes the definition of an existing type. There are
26 several subforms:
27 OWNER
29 This form changes the owner of the type.
30 RENAME
32 This form changes the name of the type.
33 SET SCHEMA
35 This form moves the type into another schema.
36 RENAME ATTRIBUTE
38 This form is only usable with composite types. It changes the name
39 of an individual attribute of the type.
40 ADD ATTRIBUTE
42 This form adds a new attribute to a composite type, using the same
43 syntax as CREATE TYPE (CREATE_TYPE(7)).
44 DROP ATTRIBUTE [ IF EXISTS ]
46 This form drops an attribute from a composite type. If IF EXISTS is
47 specified and the attribute does not exist, no error is thrown. In
48 this case a notice is issued instead.
49 ALTER ATTRIBUTE ... SET DATA TYPE
51 This form changes the type of an attribute of a composite type.
52 ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]
54 This form adds a new value to an enum type. The new value's place
55 in the enum's ordering can be specified as being BEFORE or AFTER
56 one of the existing values. Otherwise, the new item is added at the
57 end of the list of values.
58 If IF NOT EXISTS is specified, it is not an error if the type
60 already contains the new value: a notice is issued but no other
61 action is taken. Otherwise, an error will occur if the new value is
62 already present.
63 RENAME VALUE
65 This form renames a value of an enum type. The value's place in the
66 enum's ordering is not affected. An error will occur if the
67 specified value is not present or the new name is already present.
68 SET ( property = value [, ... ] )
70 This form is only applicable to base types. It allows adjustment of
71 a subset of the base-type properties that can be set in CREATE
72 TYPE. Specifically, these properties can be changed:
73 • RECEIVE can be set to the name of a binary input function, or
75 NONE to remove the type's binary input function. Using this
76 option requires superuser privilege.
77 • SEND can be set to the name of a binary output function, or
79 NONE to remove the type's binary output function. Using this
80 option requires superuser privilege.
81 • TYPMOD_IN can be set to the name of a type modifier input
83 function, or NONE to remove the type's type modifier input
84 function. Using this option requires superuser privilege.
85 • TYPMOD_OUT can be set to the name of a type modifier output
87 function, or NONE to remove the type's type modifier output
88 function. Using this option requires superuser privilege.
89 • ANALYZE can be set to the name of a type-specific statistics
91 collection function, or NONE to remove the type's statistics
92 collection function. Using this option requires superuser
93 privilege.
94 • STORAGE can be set to plain, extended, external, or main (see
96 Section 68.2 for more information about what these mean).
97 However, changing from plain to another setting requires
98 superuser privilege (because it requires that the type's C
99 functions all be TOAST-ready), and changing to plain from
100 another setting is not allowed at all (since the type may
101 already have TOASTed values present in the database). Note that
102 changing this option doesn't by itself change any stored data,
103 it just sets the default TOAST strategy to be used for table
104 columns created in the future. See ALTER TABLE (ALTER_TABLE(7))
105 to change the TOAST strategy for existing table columns.
106 See CREATE TYPE (CREATE_TYPE(7)) for more details about these type
108 properties. Note that where appropriate, a change in these
109 properties for a base type will be propagated automatically to
110 domains based on that type.
111 The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be
113 combined into a list of multiple alterations to apply in parallel. For
114 example, it is possible to add several attributes and/or alter the type
115 of several attributes in a single command.
116 You must own the type to use ALTER TYPE. To change the schema of a
118 type, you must also have CREATE privilege on the new schema. To alter
119 the owner, you must also be a direct or indirect member of the new
120 owning role, and that role must have CREATE privilege on the type's
121 schema. (These restrictions enforce that altering the owner doesn't do
122 anything you couldn't do by dropping and recreating the type. However,
123 a superuser can alter ownership of any type anyway.) To add an
124 attribute or alter an attribute type, you must also have USAGE
125 privilege on the attribute's data type.
126
128 name
129 The name (possibly schema-qualified) of an existing type to alter.
130 new_name
132 The new name for the type.
133 new_owner
135 The user name of the new owner of the type.
136 new_schema
138 The new schema for the type.
139 attribute_name
141 The name of the attribute to add, alter, or drop.
142 new_attribute_name
144 The new name of the attribute to be renamed.
145 data_type
147 The data type of the attribute to add, or the new type of the
148 attribute to alter.
149 new_enum_value
151 The new value to be added to an enum type's list of values, or the
152 new name to be given to an existing value. Like all enum literals,
153 it needs to be quoted.
154 neighbor_enum_value
156 The existing enum value that the new value should be added
157 immediately before or after in the enum type's sort ordering. Like
158 all enum literals, it needs to be quoted.
159 existing_enum_value
161 The existing enum value that should be renamed. Like all enum
162 literals, it needs to be quoted.
163 property
165 The name of a base-type property to be modified; see above for
166 possible values.
167 CASCADE
169 Automatically propagate the operation to typed tables of the type
170 being altered, and their descendants.
171 RESTRICT
173 Refuse the operation if the type being altered is the type of a
174 typed table. This is the default.
175
177 If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum
178 type) is executed inside a transaction block, the new value cannot be
179 used until after the transaction has been committed.
180 Comparisons involving an added enum value will sometimes be slower than
182 comparisons involving only original members of the enum type. This will
183 usually only occur if BEFORE or AFTER is used to set the new value's
184 sort position somewhere other than at the end of the list. However,
185 sometimes it will happen even though the new value is added at the end
186 (this occurs if the OID counter “wrapped around” since the original
187 creation of the enum type). The slowdown is usually insignificant; but
188 if it matters, optimal performance can be regained by dropping and
189 recreating the enum type, or by dumping and reloading the database.
190
192 To rename a data type:
193 ALTER TYPE electronic_mail RENAME TO email;
195 To change the owner of the type email to joe:
197 ALTER TYPE email OWNER TO joe;
199 To change the schema of the type email to customers:
201 ALTER TYPE email SET SCHEMA customers;
203 To add a new attribute to a composite type:
205 ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
207 To add a new value to an enum type in a particular sort position:
209 ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
211 To rename an enum value:
213 ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
215 To create binary I/O functions for an existing base type:
217 CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
219 CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
220 ALTER TYPE mytype SET (
221 SEND = mytypesend,
222 RECEIVE = mytyperecv
223 );
224
226 The variants to add and drop attributes are part of the SQL standard;
227 the other variants are PostgreSQL extensions.
228
230 CREATE TYPE (CREATE_TYPE(7)), DROP TYPE (DROP_TYPE(7))
231PostgreSQL 13.3 2021 ALTER TYPE(7)