ALTER_TYPE(7)

1ALTER TYPE(7)           PostgreSQL 9.2.24 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
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 new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
15
16       where action is one of:
17
18           ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
19           DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
20           ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
21

DESCRIPTION

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

PARAMETERS

78       name
79           The name (possibly schema-qualified) of an existing type to alter.
80
81       new_name
82           The new name for the type.
83
84       new_owner
85           The user name of the new owner of the type.
86
87       new_schema
88           The new schema for the type.
89
90       attribute_name
91           The name of the attribute to add, alter, or drop.
92
93       new_attribute_name
94           The new name of the attribute to be renamed.
95
96       data_type
97           The data type of the attribute to add, or the new type of the
98           attribute to alter.
99
100       new_enum_value
101           The new value to be added to an enum type's list of values. Like
102           all enum literals, it needs to be quoted.
103
104       existing_enum_value
105           The existing enum value that the new value should be added
106           immediately before or after in the enum type's sort ordering. Like
107           all enum literals, it needs to be quoted.
108

NOTES

110       ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum
111       type) cannot be executed inside a transaction block.
112
113       Comparisons involving an added enum value will sometimes be slower than
114       comparisons involving only original members of the enum type. This will
115       usually only occur if BEFORE or AFTER is used to set the new value's
116       sort position somewhere other than at the end of the list. However,
117       sometimes it will happen even though the new value is added at the end
118       (this occurs if the OID counter “wrapped around” since the original
119       creation of the enum type). The slowdown is usually insignificant; but
120       if it matters, optimal performance can be regained by dropping and
121       recreating the enum type, or by dumping and reloading the database.
122

EXAMPLES

124       To rename a data type:
125
126           ALTER TYPE electronic_mail RENAME TO email;
127
128       To change the owner of the type email to joe:
129
130           ALTER TYPE email OWNER TO joe;
131
132       To change the schema of the type email to customers:
133
134           ALTER TYPE email SET SCHEMA customers;
135
136       To add a new attribute to a type:
137
138           ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
139
140       To add a new value to an enum type in a particular sort position:
141
142           ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
143

COMPATIBILITY

145       The variants to add and drop attributes are part of the SQL standard;
146       the other variants are PostgreSQL extensions.
147