Alter a user-defined type (UDT)

Use the ALTER TYPE command to add fields to a user-defined type (UDT) or to rename an existing field in a UDT.

Restriction: Modifying UDTs used in primary keys or index columns are not supported. Changing the field type is not supported.

Prerequisites

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.

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
);

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;

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
);