Skip to main content
The ALTER DOMAIN statement modifies a domain data type in the current database.
The ALTER DOMAIN statement performs a schema change. For more information about how online schema changes work in CockroachDB, refer to Online Schema Changes.

Synopsis

alter_domain syntax diagram

Parameters

Required privileges

  • To alter a domain, the user must be the owner of the domain.
  • To set the schema of a domain, the user must also have the CREATE privilege on the new schema.
  • To alter the owner of a domain:
    • The user executing the command must be a member of the new owner role.
    • The new owner role must have the CREATE privilege on the schema the domain belongs to.

Examples

The examples use the following domain and table:
Insert a row that will violate a stricter constraint added in a later example:

Set a default value

The following statement sets a default value of 1 for columns that use the order_qty domain:

Add a constraint

Existing data is validated before the constraint is applied. If a value in the orders table violates the new constraint, the statement returns an error:
With NOT VALID, existing data is not validated and only newly inserted or updated values are checked:
The convalidated column of pg_catalog.pg_constraint shows whether each domain constraint is validated:

Validate a constraint

The following statement validates the qty_cap constraint that was added with NOT VALID in the preceding example. The orders table still contains a value that violates the constraint, so the statement returns an error:
Update the row that violates the constraint:
Validate the constraint again:
The convalidated column of pg_catalog.pg_constraint shows that the constraint is now validated:

Rename and drop a constraint

Rename the qty_cap constraint:
Drop the constraint by its new name:

Change the owner and schema

Create a role and make it the owner of the domain:
Create a schema and move the domain into it:

Rename a domain

The following statement renames the domain, referencing it by its new schema-qualified name:
The domain definition reflects the changes from the preceding examples:

Known limitations

See also