alter_type(7)

NAME#

ALTER_TYPE - change the definition of a type

SYNOPSIS#

ALTER TYPE \nname\n OWNER TO { \nnew_owner\n | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER TYPE \nname\n RENAME TO \nnew_name\n
ALTER TYPE \nname\n SET SCHEMA \nnew_schema\n
ALTER TYPE \nname\n RENAME ATTRIBUTE \nattribute_name\n TO \nnew_attribute_name\n [ CASCADE | RESTRICT ]
ALTER TYPE \nname\n \naction\n [, ... ]
ALTER TYPE \nname\n ADD VALUE [ IF NOT EXISTS ] \nnew_enum_value\n [ { BEFORE | AFTER } \nneighbor_enum_value\n ]
ALTER TYPE \nname\n RENAME VALUE \nexisting_enum_value\n TO \nnew_enum_value\n
ALTER TYPE \nname\n SET ( \nproperty\n = \nvalue\n [, ... ] )
where \naction\n is one of:
\n
    ADD ATTRIBUTE \nattribute_name\n \ndata_type\n [ COLLATE \ncollation\n ] [ CASCADE | RESTRICT ]
\n
    DROP ATTRIBUTE [ IF EXISTS ] \nattribute_name\n [ CASCADE | RESTRICT ]
\n
    ALTER ATTRIBUTE \nattribute_name\n [ SET DATA ] TYPE \ndata_type\n [ COLLATE \ncollation\n ] [ CASCADE | RESTRICT ]

DESCRIPTION#

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

OWNER

RENAME

SET SCHEMA

RENAME ATTRIBUTE

ADD ATTRIBUTE

DROP ATTRIBUTE [ IF EXISTS ]

ALTER ATTRIBUTE ... SET DATA TYPE

ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]

If IF NOT EXISTS is specified, it is not an error if the type already contains the new value: a notice is issued but no other action is taken. Otherwise, an error will occur if the new value is already present.

RENAME VALUE

SET ( property = value [, ... ] )

See CREATE TYPE (CREATE_TYPE(7)) for more details about these type properties. Note that where appropriate, a change in these properties for a base type will be propagated automatically to domains based on that type.

The ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several attributes and/or alter the type of several attributes in a single command.

You must own the type to use ALTER TYPE. To change the schema of a type, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the type's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the type. However, a superuser can alter ownership of any type anyway.) To add an attribute or alter an attribute type, you must also have USAGE privilege on the attribute's data type.

PARAMETERS#

name

new_name

new_owner

new_schema

attribute_name

new_attribute_name

data_type

new_enum_value

neighbor_enum_value

existing_enum_value

property

CASCADE

RESTRICT

NOTES#

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

Comparisons involving an added enum value will sometimes be slower than comparisons involving only original members of the enum type. This will usually only occur if BEFORE or AFTER is used to set the new value's sort position somewhere other than at the end of the list. However, sometimes it will happen even though the new value is added at the end (this occurs if the OID counter “wrapped around” since the original creation of the enum type). The slowdown is usually insignificant; but if it matters, optimal performance can be regained by dropping and recreating the enum type, or by dumping and restoring the database.

EXAMPLES#

To rename a data type:

ALTER TYPE electronic_mail RENAME TO email;

To change the owner of the type email to joe:

ALTER TYPE email OWNER TO joe;

To change the schema of the type email to customers:

ALTER TYPE email SET SCHEMA customers;

To add a new attribute to a composite type:

ALTER TYPE compfoo ADD ATTRIBUTE f3 int;

To add a new value to an enum type in a particular sort position:

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

To rename an enum value:

ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';

To create binary I/O functions for an existing base type:

CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
ALTER TYPE mytype SET (
\n
    SEND = mytypesend,
\n
    RECEIVE = mytyperecv
);

COMPATIBILITY#

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

SEE ALSO#

CREATE TYPE (CREATE_TYPE(7)), DROP TYPE (DROP_TYPE(7))