Changes the definition of a domain.
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>
ALTER DOMAIN <name> RENAME TO <new_name>
ALTER DOMAIN <name> SET SCHEMA <new_schema>
ALTER DOMAIN
changes the definition of an existing domain. There are several sub-forms:
INSERT
commands. They do not affect rows already in a table using the domain.NULL
values or to reject NULL
values. You may only SET NOT NULL
when the columns using the domain contain no null values.CREATE DOMAIN
. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. These checks can be suppressed by adding the new constraint using the NOT VALID
option; the constraint can later be made valid using ALTER DOMAIN ... VALIDATE CONSTRAINT
. Newly inserted or updated rows are always checked against all constraints, even those marked NOT VALID
. NOT VALID
is only accepted for CHECK
constraints.IF EXISTS
is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.NOT VALID
, that is, verify that all data in columns using the domain satisfy the specified constraint.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 also 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 enforce that altering the owner does not do anything you could not do by dropping and recreating the domain. However, a superuser can alter ownership of any domain anyway.)
To add a NOT NULL
constraint to a domain:
ALTER DOMAIN zipcode SET NOT NULL;
To remove a NOT NULL
constraint from a domain:
ALTER DOMAIN zipcode DROP NOT NULL;
To add a check constraint to a domain:
ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
To remove a check constraint from a domain:
ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
To rename a check constraint on a domain:
ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;
To move the domain into a different schema:
ALTER DOMAIN zipcode SET SCHEMA customers;
ALTER DOMAIN
conforms to the SQL standard, except for the OWNER
, RENAME
, SET SCHEMA
, and VALIDATE CONSTRAINT
variants, which are Greenplum Database extensions. The NOT VALID
clause of the ADD CONSTRAINT
variant is also a Greenplum Database extension.
Parent topic: SQL Commands