1ALTER TYPE(7)            PostgreSQL 15.4 Documentation           ALTER TYPE(7)
2
3
4

NAME

6       ALTER_TYPE - change the definition of a type
7

SYNOPSIS

9       ALTER TYPE name OWNER TO { new_owner | CURRENT_ROLE | 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
18       where action is one of:
19
20           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

DESCRIPTION

25       ALTER TYPE changes the definition of an existing type. There are
26       several subforms:
27
28       OWNER
29           This form changes the owner of the type.
30
31       RENAME
32           This form changes the name of the type.
33
34       SET SCHEMA
35           This form moves the type into another schema.
36
37       RENAME ATTRIBUTE
38           This form is only usable with composite types. It changes the name
39           of an individual attribute of the type.
40
41       ADD ATTRIBUTE
42           This form adds a new attribute to a composite type, using the same
43           syntax as CREATE TYPE.
44
45       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
50       ALTER ATTRIBUTE ... SET DATA TYPE
51           This form changes the type of an attribute of a composite type.
52
53       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
59           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
64       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
69       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
74           •   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
78           •   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
82           •   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
86           •   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
90           •   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
95           •   SUBSCRIPT can be set to the name of a type-specific
96               subscripting handler function, or NONE to remove the type's
97               subscripting handler function. Using this option requires
98               superuser privilege.
99
100           •   STORAGE can be set to plain, extended, external, or main (see
101               Section 73.2 for more information about what these mean).
102               However, changing from plain to another setting requires
103               superuser privilege (because it requires that the type's C
104               functions all be TOAST-ready), and changing to plain from
105               another setting is not allowed at all (since the type may
106               already have TOASTed values present in the database). Note that
107               changing this option doesn't by itself change any stored data,
108               it just sets the default TOAST strategy to be used for table
109               columns created in the future. See ALTER TABLE (ALTER_TABLE(7))
110               to change the TOAST strategy for existing table columns.
111
112           See CREATE TYPE (CREATE_TYPE(7)) for more details about these type
113           properties. Note that where appropriate, a change in these
114           properties for a base type will be propagated automatically to
115           domains based on that type.
116
117       The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be
118       combined into a list of multiple alterations to apply in parallel. For
119       example, it is possible to add several attributes and/or alter the type
120       of several attributes in a single command.
121
122       You must own the type to use ALTER TYPE. To change the schema of a
123       type, you must also have CREATE privilege on the new schema. To alter
124       the owner, you must also be a direct or indirect member of the new
125       owning role, and that role must have CREATE privilege on the type's
126       schema. (These restrictions enforce that altering the owner doesn't do
127       anything you couldn't do by dropping and recreating the type. However,
128       a superuser can alter ownership of any type anyway.) To add an
129       attribute or alter an attribute type, you must also have USAGE
130       privilege on the attribute's data type.
131

PARAMETERS

133       name
134           The name (possibly schema-qualified) of an existing type to alter.
135
136       new_name
137           The new name for the type.
138
139       new_owner
140           The user name of the new owner of the type.
141
142       new_schema
143           The new schema for the type.
144
145       attribute_name
146           The name of the attribute to add, alter, or drop.
147
148       new_attribute_name
149           The new name of the attribute to be renamed.
150
151       data_type
152           The data type of the attribute to add, or the new type of the
153           attribute to alter.
154
155       new_enum_value
156           The new value to be added to an enum type's list of values, or the
157           new name to be given to an existing value. Like all enum literals,
158           it needs to be quoted.
159
160       neighbor_enum_value
161           The existing enum value that the new value should be added
162           immediately before or after in the enum type's sort ordering. Like
163           all enum literals, it needs to be quoted.
164
165       existing_enum_value
166           The existing enum value that should be renamed. Like all enum
167           literals, it needs to be quoted.
168
169       property
170           The name of a base-type property to be modified; see above for
171           possible values.
172
173       CASCADE
174           Automatically propagate the operation to typed tables of the type
175           being altered, and their descendants.
176
177       RESTRICT
178           Refuse the operation if the type being altered is the type of a
179           typed table. This is the default.
180

NOTES

182       If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum
183       type) is executed inside a transaction block, the new value cannot be
184       used until after the transaction has been committed.
185
186       Comparisons involving an added enum value will sometimes be slower than
187       comparisons involving only original members of the enum type. This will
188       usually only occur if BEFORE or AFTER is used to set the new value's
189       sort position somewhere other than at the end of the list. However,
190       sometimes it will happen even though the new value is added at the end
191       (this occurs if the OID counter “wrapped around” since the original
192       creation of the enum type). The slowdown is usually insignificant; but
193       if it matters, optimal performance can be regained by dropping and
194       recreating the enum type, or by dumping and restoring the database.
195

EXAMPLES

197       To rename a data type:
198
199           ALTER TYPE electronic_mail RENAME TO email;
200
201       To change the owner of the type email to joe:
202
203           ALTER TYPE email OWNER TO joe;
204
205       To change the schema of the type email to customers:
206
207           ALTER TYPE email SET SCHEMA customers;
208
209       To add a new attribute to a composite type:
210
211           ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
212
213       To add a new value to an enum type in a particular sort position:
214
215           ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
216
217       To rename an enum value:
218
219           ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
220
221       To create binary I/O functions for an existing base type:
222
223           CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
224           CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
225           ALTER TYPE mytype SET (
226               SEND = mytypesend,
227               RECEIVE = mytyperecv
228           );
229

COMPATIBILITY

231       The variants to add and drop attributes are part of the SQL standard;
232       the other variants are PostgreSQL extensions.
233

SEE ALSO

235       CREATE TYPE (CREATE_TYPE(7)), DROP TYPE (DROP_TYPE(7))
236
237
238
239PostgreSQL 15.4                      2023                        ALTER TYPE(7)
Impressum