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

NAME

6       ALTER_TYPE - change the definition of a type
7

SYNOPSIS

9       ALTER TYPE name action [, ... ]
10       ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
11       ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
12       ALTER TYPE name RENAME TO new_name
13       ALTER TYPE name SET SCHEMA new_schema
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
17       where action is one of:
18
19           ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
20           DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
21           ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
22

DESCRIPTION

24       ALTER TYPE changes the definition of an existing type. There are
25       several subforms:
26
27       ADD ATTRIBUTE
28           This form adds a new attribute to a composite type, using the same
29           syntax as CREATE TYPE (CREATE_TYPE(7)).
30
31       DROP ATTRIBUTE [ IF EXISTS ]
32           This form drops an attribute from a composite type. If IF EXISTS is
33           specified and the attribute does not exist, no error is thrown. In
34           this case a notice is issued instead.
35
36       SET DATA TYPE
37           This form changes the type of an attribute of a composite type.
38
39       OWNER
40           This form changes the owner of the type.
41
42       RENAME
43           This form changes the name of the type or the name of an individual
44           attribute of a composite type.
45
46       SET SCHEMA
47           This form moves the type into another schema.
48
49       ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]
50           This form adds a new value to an enum type. The new value's place
51           in the enum's ordering can be specified as being BEFORE or AFTER
52           one of the existing values. Otherwise, the new item is added at the
53           end of the list of values.
54
55           If IF NOT EXISTS is specified, it is not an error if the type
56           already contains the new value: a notice is issued but no other
57           action is taken. Otherwise, an error will occur if the new value is
58           already present.
59
60       RENAME VALUE
61           This form renames a value of an enum type. The value's place in the
62           enum's ordering is not affected. An error will occur if the
63           specified value is not present or the new name is already present.
64
65       The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be
66       combined into a list of multiple alterations to apply in parallel. For
67       example, it is possible to add several attributes and/or alter the type
68       of several attributes in a single command.
69
70       You must own the type to use ALTER TYPE. To change the schema of a
71       type, you must also have CREATE privilege on the new schema. To alter
72       the owner, you must also be a direct or indirect member of the new
73       owning role, and that role must have CREATE privilege on the type's
74       schema. (These restrictions enforce that altering the owner doesn't do
75       anything you couldn't do by dropping and recreating the type. However,
76       a superuser can alter ownership of any type anyway.) To add an
77       attribute or alter an attribute type, you must also have USAGE
78       privilege on the data type.
79

PARAMETERS

81       name
82           The name (possibly schema-qualified) of an existing type to alter.
83
84       new_name
85           The new name for the type.
86
87       new_owner
88           The user name of the new owner of the type.
89
90       new_schema
91           The new schema for the type.
92
93       attribute_name
94           The name of the attribute to add, alter, or drop.
95
96       new_attribute_name
97           The new name of the attribute to be renamed.
98
99       data_type
100           The data type of the attribute to add, or the new type of the
101           attribute to alter.
102
103       new_enum_value
104           The new value to be added to an enum type's list of values, or the
105           new name to be given to an existing value. Like all enum literals,
106           it needs to be quoted.
107
108       neighbor_enum_value
109           The existing enum value that the new value should be added
110           immediately before or after in the enum type's sort ordering. Like
111           all enum literals, it needs to be quoted.
112
113       existing_enum_value
114           The existing enum value that should be renamed. Like all enum
115           literals, it needs to be quoted.
116
117       CASCADE
118           Automatically propagate the operation to typed tables of the type
119           being altered, and their descendants.
120
121       RESTRICT
122           Refuse the operation if the type being altered is the type of a
123           typed table. This is the default.
124

NOTES

126       If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum
127       type) is executed inside a transaction block, the new value cannot be
128       used until after the transaction has been committed.
129
130       Comparisons involving an added enum value will sometimes be slower than
131       comparisons involving only original members of the enum type. This will
132       usually only occur if BEFORE or AFTER is used to set the new value's
133       sort position somewhere other than at the end of the list. However,
134       sometimes it will happen even though the new value is added at the end
135       (this occurs if the OID counter “wrapped around” since the original
136       creation of the enum type). The slowdown is usually insignificant; but
137       if it matters, optimal performance can be regained by dropping and
138       recreating the enum type, or by dumping and reloading the database.
139

EXAMPLES

141       To rename a data type:
142
143           ALTER TYPE electronic_mail RENAME TO email;
144
145       To change the owner of the type email to joe:
146
147           ALTER TYPE email OWNER TO joe;
148
149       To change the schema of the type email to customers:
150
151           ALTER TYPE email SET SCHEMA customers;
152
153       To add a new attribute to a type:
154
155           ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
156
157       To add a new value to an enum type in a particular sort position:
158
159           ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
160
161       To rename an enum value:
162
163           ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
164

COMPATIBILITY

166       The variants to add and drop attributes are part of the SQL standard;
167       the other variants are PostgreSQL extensions.
168

SEE ALSO

170       CREATE TYPE (CREATE_TYPE(7)), DROP TYPE (DROP_TYPE(7))
171
172
173
174PostgreSQL 12.2                      2020                        ALTER TYPE(7)
Impressum