Alter a UDT
Use the ALTER TYPE command to add fields to a user-defined type (UDT) or to rename an existing field in a UDT.
Limitations
You cannot modify UDTs that are used in primary keys or indexed columns.
You cannot change the field type for any UDT. If you need to change the field type, you must create a new UDT with the correct type, add the new type to your table definitions, and reinsert all of the data to the new column. Then, you can drop the old UDT.
Add a user-defined data type (UDT)
Add a middlename field of type text to the user-defined type cycling.fullname.
ALTER TYPE cycling.fullname
ADD middlename text;The ALTER TYPE command adds the field to the type.
Check that the UDT is created
Verify the changes, use the DESC TYPE command:
DESCRIBE TYPE cycling.fullname;Results
The middlename column appears in the type definition.
CREATE TYPE cycling.fullname (
firstname text,
lastname text
);Rename an existing UDT field
To change the name of an existing field, use RENAME.
ALTER TYPE cycling.fullname
RENAME middlename TO middle
AND lastname TO last
AND firstname TO first;Check the results
Verify the changes.
DESCRIBE TYPE cycling.fullname;Results
The renamed fields appear in the type definition.
CREATE TYPE cycling.fullname (
firstname text,
lastname text,
middlename text
);