Use ALTER DOMAIN to change a domain definition - PolarDB

ALTER DOMAIN changes the definition of an existing domain.

Sub-forms

ALTER DOMAIN supports the following sub-forms:

SET DEFAULT / DROP DEFAULT Set or remove the default value for a domain. Defaults apply only to subsequent INSERT commands and do not affect rows already stored in a table that uses the domain.

SET NOT NULL / DROP NOT NULL Mark a domain to reject NULL values, or allow NULL values again. SET NOT NULL succeeds only when all columns using the domain contain no null values.

ADD domain_constraint [ NOT VALID ] Add a new constraint using the same syntax as CREATE DOMAIN. All columns using the domain are checked against the new constraint. To skip the immediate check, add NOT VALID; validate the constraint later with ALTER DOMAIN ... VALIDATE CONSTRAINT. Newly inserted or updated rows are always checked against all constraints, including those marked NOT VALID. NOT VALID is accepted only for CHECK constraints.

DROP CONSTRAINT [ IF EXISTS ] Remove a constraint from a domain. With IF EXISTS, dropping a non-existent constraint issues a notice instead of an error.

RENAME CONSTRAINT Rename a constraint on a domain.

VALIDATE CONSTRAINT Validate a constraint that was previously added with NOT VALID, verifying that all existing values in columns of the domain type satisfy the constraint.

OWNER TO Transfer ownership of the domain to the specified user.

RENAME TO Rename the domain.

SET SCHEMA Move the domain to a different schema. All constraints associated with the domain move to the new schema as well.

Prerequisites

To use ALTER DOMAIN, you must own the domain.
To change the schema, you must also have CREATE privilege on the new schema.
To change the owner, you must be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the domain's schema. These restrictions ensure that transferring ownership provides no more access than dropping and recreating the domain would. A superuser can transfer ownership of any domain regardless of these restrictions.

Synopsis

ALTER DOMAIN name
        { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name
        { SET | DROP } NOT NULL
ALTER DOMAIN name
        ADD domain_constraint [ NOT VALID ]
ALTER DOMAIN name
        DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name
         RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER DOMAIN name
        VALIDATE CONSTRAINT constraint_name
ALTER DOMAIN name
        OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DOMAIN name
        RENAME TO new_name
ALTER DOMAIN name
        SET SCHEMA new_schema

Parameters

Parameter	Description
`name`	The name (optionally schema-qualified) of an existing domain to alter.
`domain_constraint`	The new constraint definition for the domain.
`constraint_name`	The name of an existing constraint to drop or rename.
`NOT VALID`	Skip verification of existing stored data when adding a constraint.
`CASCADE`	Automatically drop objects that depend on the constraint, and in turn all objects that depend on those objects.
`RESTRICT`	Refuse to drop the constraint if any dependent objects exist. This is the default.
`new_name`	The new name for the domain.
`new_constraint_name`	The new name for the constraint.
`new_owner`	The user name of the new owner of the domain.
`new_schema`	The new schema for the domain.

Usage notes

Safe constraint addition in concurrent environments

ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing data satisfies the new constraint, but the check is not guaranteed to be complete: it cannot see rows that were inserted or updated after the command started but before it committed. If concurrent writes could introduce invalid data, use this approach instead:

Add the constraint with NOT VALID.
Commit the transaction.
Wait for all transactions that started before the commit to finish.
Run ALTER DOMAIN VALIDATE CONSTRAINT.

Once the constraint is committed, all new transactions enforce it against new values of the domain type, making this approach reliable.

Container-type column limitation

ALTER DOMAIN ADD CONSTRAINT, ALTER DOMAIN VALIDATE CONSTRAINT, and ALTER DOMAIN SET NOT NULL fail if the domain (or any domain derived from it) is used within a composite, array, or range column in any table in the database. Support for verifying constraints in nested columns is expected to be addressed in a future release.

Examples

Add a NOT NULL constraint:

ALTER DOMAIN zipcode SET NOT NULL;

Remove a NOT NULL constraint:

ALTER DOMAIN zipcode DROP NOT NULL;

Add a CHECK constraint:

ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);

Remove a CHECK constraint:

ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;

Rename a CHECK constraint:

ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;

Move a domain to a different schema:

ALTER DOMAIN zipcode SET SCHEMA customers;

PolarDB:ALTER DOMAIN