Modifying a user-defined type

Adding columns to a user-defined type with the ALTER TYPE command.

The ALTER TYPE command can be used to add new columns to a user-defined type and to alter the data type of an existing column in a user-defined type.

Procedure

  • Add a middlename column of type text to the user-defined type cycling.fullname.
    ALTER TYPE cycling.fullname ADD middlename text;

    This creates the column metadata and adds the column to the type schema.

    To verify the changes, use DESC TYPE.
    DESC TYPE cycling.fullname ;
    The middle name columns shows in the type definition.
    CREATE TYPE cycling.fullname (
        firstname text,
        lastname text,
        middlename text
    );
  • A column can be renamed in either ALTER TABLE or ALTER TYPE. In ALTER TABLE, only primary key columns may be renamed.
    ALTER TYPE cycling.fullname 
    RENAME middlename TO middleinitial;
    DESC TYPE cycling.fullname ;
    Shows the new name in the type definition.
    CREATE TYPE cycling.fullname (
        firstname text,
        lastname text,
        middleinitial text
    );