ALTER TYPE

Change the definition of a data type.

Summary

ALTER TYPE name action [, ... ]
ALTER TYPE name OWNER TO new_owner
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
ALTER TYPE name SET DEFAULT ENCODING ( storage_directive )

where action is one of the following options:

  ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
  DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
  ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]

where storage_directive is:

   COMPRESSTYPE={ZLIB | ZSTD | QUICKLZ | RLE_TYPE | NONE}
   COMPRESSLEVEL={0-19}
   BLOCKSIZE={8192-2097152}

describe

ALTER TYPE Changes the definition of an existing type. There are several subforms:

ADD ATTRIBUTE — Add new properties to the compound type using the same syntax as CREATE TYPE.
DROP ATTRIBUTE [ IF EXISTS ] — Remove attributes from composite types. If IF EXISTS is specified and the property does not exist, no errors are raised. In this case, a notification will be issued.
SET DATA TYPE — Changes the type of the attribute of the composite type.
OWNER — Change the owner of the type.
RENAME — Changes the name of a type or the name of a single property of a compound type.
SET SCHEMA — Moves the type to another schema.
ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ] — Add new value to the enumeration type. You can specify the position of the new value in the enumeration order as one of the existing values BEFORE or AFTER. Otherwise, the new item will be added to the end of the value list.
If IF NOT EXISTS is specified, it is not an error that the type already contains a new value; a notification is issued, but no other action is taken. Otherwise, if the new value already exists, an error will occur.
CASCADE — Automatically propagates operations to the type table and its descendants of the type to be changed.
RESTRICT — This operation is rejected if the type to be changed is the type of the type table. This is the default value.

The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE operations can be combined into multiple change lists to apply in parallel. For example, you can add multiple attributes and/or change the type of multiple attributes in a single command.

You can change the schema of names, owners, and types. You can also add or update storage options for scalar types.

Note: The YMatrix database does not support adding storage options for rows or composite types.

You must have this type to use ALTER TYPE. To change the type of schema, you must also have CREATE privileges for the new schema. To change the owner, you must also be a direct or indirect member of the newly owned role, and that role must have CREATE privileges for the schema of the type. (These restrictions force the change owner to not perform any actions of deleting and recreating the type. However, superusers can change ownership of any type.) To add or change the property type, you must also have privileges for the USAGE data type.

ALTER TYPE ... ADD VALUE (the form of adding a new value to the enum type) cannot be executed within a transaction block.

Comparisons involving increased enum values are sometimes slower than those involving only original members of the enum type. This usually only happens when you set the sort position of the new value (rather than the end of the list) using BEFORE or AFTER. However, sometimes even adding a new value to the end will happen (this happens if the OID counter is "wrapped" since the enum type was originally created). The growth rate is usually small. However, if it is important, you can get the best performance by deleting and recreating the enum type, or by dumping and reloading the database.

Parameters

name

The name of the existing type to be changed (optional schema-qualified).

new_name

New name of type.

new_owner

Username of the new owner of this type.

new_schema

New architecture of types.

attribute_name

The name of the attribute to add, change, or delete.

new_attribute_name

The new name of the property to be renamed.

data_type

The data type of the attribute to be added, or the new type of the attribute to be changed.

new_enum_value

The data type of the attribute to be added, or the new type of the attribute to be changed.

existing_enum_value

An existing enum value that should be added before or immediately after the sort order of the enum type. Like all enumeration texts, they also need to be enclosed in quotes.

storage_directive

Identifies the default storage option for that type when specified in the table column definition. Options include COMPRESSTYPE, COMPRESSLEVEL and BLOCKSIZE.
COMPRESSTYPE — Set to ZLIB (default), ZSTD, RLE_TYPE, or QUICKLZ1 to specify the compression type used.
COMPRESSLEVEL — For ZSTD compression, set it to an integer value between 1 (fastest compression) and 19 (maximum compression rate). For ZLIB compression, the effective range is 1 to 9. For RLE_TYPE, the compression level can be set to an integer value from 1 (fastest compression) to 4 (maximum compression ratio). The default compression level is 1.
BLOCKSIZE — Set to the size of each block in the column in the size (in bytes). BLOCKSIZE must be between 8192 and 2097152 bytes and is a multiple of 8192. The default block size is 32768.

Example

To rename the data type named electronic_mail:

ALTER TYPE electronic_mail RENAME TO email;

Change the owner of the user-defined type email to joe:

ALTER TYPE email OWNER TO joe;

Change the mode of the user-defined type email to customers:

ALTER TYPE email SET SCHEMA customers;

Set or change the compression type and compression level of a user-defined type named int33:

ALTER TYPE int33 SET DEFAULT ENCODING (compresstype=zlib, compresslevel=7);

To add a new property to the type:

ALTER TYPE compfoo ADD ATTRIBUTE f3 int;

To add a new value to an enum type for a specific sorting location:

ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';

compatibility