alter_domain(7) - BetterMan

—

NAME

ALTER_DOMAIN - change the definition of a domain

SYNOPSIS

bash

ALTER DOMAIN \nname\n
\n
    { SET DEFAULT \nexpression\n | DROP DEFAULT }
ALTER DOMAIN \nname\n
\n
    { SET | DROP } NOT NULL
ALTER DOMAIN \nname\n
\n
    ADD \ndomain_constraint\n [ NOT VALID ]
ALTER DOMAIN \nname\n
\n
    DROP CONSTRAINT [ IF EXISTS ] \nconstraint_name\n [ RESTRICT | CASCADE ]
ALTER DOMAIN \nname\n
\n
     RENAME CONSTRAINT \nconstraint_name\n TO \nnew_constraint_name\n
ALTER DOMAIN \nname\n
\n
    VALIDATE CONSTRAINT \nconstraint_name\n
ALTER DOMAIN \nname\n
\n
    OWNER TO { \nnew_owner\n | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DOMAIN \nname\n
\n
    RENAME TO \nnew_name\n
ALTER DOMAIN \nname\n
\n
    SET SCHEMA \nnew_schema\n
where \ndomain_constraint\n is:
[ CONSTRAINT \nconstraint_name\n ]
{ NOT NULL | CHECK (\nexpression\n) }

DESCRIPTION

ALTER DOMAIN changes the definition of an existing domain. There are several sub-forms:

SET/DROP DEFAULT

SET/DROP NOT NULL

ADD domain_constraint [ NOT VALID ]

DROP CONSTRAINT [ IF EXISTS ]

RENAME CONSTRAINT

VALIDATE CONSTRAINT

OWNER

RENAME

SET SCHEMA

You must own the domain to use ALTER DOMAIN. To change the schema of a domain, 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 domain's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the domain. However, a superuser can alter ownership of any domain anyway.)

PARAMETERS

name

domain_constraint

constraint_name

NOT VALID

CASCADE

RESTRICT

new_name

new_constraint_name

new_owner

new_schema

NOTES

Although ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing stored data satisfies the new constraint, this check is not bulletproof, because the command cannot “see” table rows that are newly inserted or updated and not yet committed. If there is a hazard that concurrent operations might insert bad data, the way to proceed is to add the constraint using the NOT VALID option, commit that command, wait until all transactions started before that commit have finished, and then issue ALTER DOMAIN VALIDATE CONSTRAINT to search for data violating the constraint. This method is reliable because once the constraint is committed, all new transactions are guaranteed to enforce it against new values of the domain type.

Currently, ALTER DOMAIN ADD CONSTRAINT, ALTER DOMAIN VALIDATE CONSTRAINT, and ALTER DOMAIN SET NOT NULL will fail if the named domain or any derived domain is used within a container-type column (a composite, array, or range column) in any table in the database. They should eventually be improved to be able to verify the new constraint for such nested values.

EXAMPLES

To add a NOT NULL constraint to a domain:

bash

ALTER DOMAIN zipcode SET NOT NULL;

To remove a NOT NULL constraint from a domain:

bash

ALTER DOMAIN zipcode DROP NOT NULL;

To add a check constraint to a domain:

bash

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

To remove a check constraint from a domain:

bash

ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;

To rename a check constraint on a domain:

bash

ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;

To move the domain into a different schema:

bash

ALTER DOMAIN zipcode SET SCHEMA customers;

COMPATIBILITY

ALTER DOMAIN conforms to the SQL standard, except for the OWNER, RENAME, SET SCHEMA, and VALIDATE CONSTRAINT variants, which are PostgreSQL extensions. The NOT VALID clause of the ADD CONSTRAINT variant is also a PostgreSQL extension.