diff options
Diffstat (limited to 'doc/src/sgml/man7/ALTER_DOMAIN.7')
| -rw-r--r-- | doc/src/sgml/man7/ALTER_DOMAIN.7 | 293 |
1 files changed, 293 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/ALTER_DOMAIN.7 b/doc/src/sgml/man7/ALTER_DOMAIN.7 new file mode 100644 index 0000000..9e62624 --- /dev/null +++ b/doc/src/sgml/man7/ALTER_DOMAIN.7 @@ -0,0 +1,293 @@ +'\" t +.\" Title: ALTER DOMAIN +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "ALTER DOMAIN" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +ALTER_DOMAIN \- change the definition of a domain +.SH "SYNOPSIS" +.sp +.nf +ALTER DOMAIN \fIname\fR + { SET DEFAULT \fIexpression\fR | DROP DEFAULT } +ALTER DOMAIN \fIname\fR + { SET | DROP } NOT NULL +ALTER DOMAIN \fIname\fR + ADD \fIdomain_constraint\fR [ NOT VALID ] +ALTER DOMAIN \fIname\fR + DROP CONSTRAINT [ IF EXISTS ] \fIconstraint_name\fR [ RESTRICT | CASCADE ] +ALTER DOMAIN \fIname\fR + RENAME CONSTRAINT \fIconstraint_name\fR TO \fInew_constraint_name\fR +ALTER DOMAIN \fIname\fR + VALIDATE CONSTRAINT \fIconstraint_name\fR +ALTER DOMAIN \fIname\fR + OWNER TO { \fInew_owner\fR | CURRENT_ROLE | CURRENT_USER | SESSION_USER } +ALTER DOMAIN \fIname\fR + RENAME TO \fInew_name\fR +ALTER DOMAIN \fIname\fR + SET SCHEMA \fInew_schema\fR +.fi +.SH "DESCRIPTION" +.PP +\fBALTER DOMAIN\fR +changes the definition of an existing domain\&. There are several sub\-forms: +.PP +SET/DROP DEFAULT +.RS 4 +These forms set or remove the default value for a domain\&. Note that defaults only apply to subsequent +\fBINSERT\fR +commands; they do not affect rows already in a table using the domain\&. +.RE +.PP +SET/DROP NOT NULL +.RS 4 +These forms change whether a domain is marked to allow NULL values or to reject NULL values\&. You can only +SET NOT NULL +when the columns using the domain contain no null values\&. +.RE +.PP +ADD \fIdomain_constraint\fR [ NOT VALID ] +.RS 4 +This form adds a new constraint to a domain using the same syntax as +\fBCREATE DOMAIN\fR\&. 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 +\fBALTER DOMAIN \&.\&.\&. VALIDATE CONSTRAINT\fR\&. Newly inserted or updated rows are always checked against all constraints, even those marked +NOT VALID\&. +NOT VALID +is only accepted for +CHECK +constraints\&. +.RE +.PP +DROP CONSTRAINT [ IF EXISTS ] +.RS 4 +This form drops constraints on a domain\&. If +IF EXISTS +is specified and the constraint does not exist, no error is thrown\&. In this case a notice is issued instead\&. +.RE +.PP +RENAME CONSTRAINT +.RS 4 +This form changes the name of a constraint on a domain\&. +.RE +.PP +VALIDATE CONSTRAINT +.RS 4 +This form validates a constraint previously added as +NOT VALID, that is, it verifies that all values in table columns of the domain type satisfy the specified constraint\&. +.RE +.PP +OWNER +.RS 4 +This form changes the owner of the domain to the specified user\&. +.RE +.PP +RENAME +.RS 4 +This form changes the name of the domain\&. +.RE +.PP +SET SCHEMA +.RS 4 +This form changes the schema of the domain\&. Any constraints associated with the domain are moved into the new schema as well\&. +.RE +.PP +You must own the domain to use +\fBALTER DOMAIN\fR\&. 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\*(Aqs schema\&. (These restrictions enforce that altering the owner doesn\*(Aqt do anything you couldn\*(Aqt do by dropping and recreating the domain\&. However, a superuser can alter ownership of any domain anyway\&.) +.SH "PARAMETERS" +.PP +.PP +\fIname\fR +.RS 4 +The name (possibly schema\-qualified) of an existing domain to alter\&. +.RE +.PP +\fIdomain_constraint\fR +.RS 4 +New domain constraint for the domain\&. +.RE +.PP +\fIconstraint_name\fR +.RS 4 +Name of an existing constraint to drop or rename\&. +.RE +.PP +NOT VALID +.RS 4 +Do not verify existing stored data for constraint validity\&. +.RE +.PP +CASCADE +.RS 4 +Automatically drop objects that depend on the constraint, and in turn all objects that depend on those objects (see +Section\ \&5.14)\&. +.RE +.PP +RESTRICT +.RS 4 +Refuse to drop the constraint if there are any dependent objects\&. This is the default behavior\&. +.RE +.PP +\fInew_name\fR +.RS 4 +The new name for the domain\&. +.RE +.PP +\fInew_constraint_name\fR +.RS 4 +The new name for the constraint\&. +.RE +.PP +\fInew_owner\fR +.RS 4 +The user name of the new owner of the domain\&. +.RE +.PP +\fInew_schema\fR +.RS 4 +The new schema for the domain\&. +.RE +.SH "NOTES" +.PP +Although +\fBALTER DOMAIN ADD CONSTRAINT\fR +attempts to verify that existing stored data satisfies the new constraint, this check is not bulletproof, because the command cannot +\(lqsee\(rq +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 +\fBALTER DOMAIN VALIDATE CONSTRAINT\fR +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\&. +.PP +Currently, +\fBALTER DOMAIN ADD CONSTRAINT\fR, +\fBALTER DOMAIN VALIDATE CONSTRAINT\fR, and +\fBALTER DOMAIN SET NOT NULL\fR +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\&. +.SH "EXAMPLES" +.PP +To add a +NOT NULL +constraint to a domain: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode SET NOT NULL; +.fi +.if n \{\ +.RE +.\} +.sp +To remove a +NOT NULL +constraint from a domain: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode DROP NOT NULL; +.fi +.if n \{\ +.RE +.\} +.PP +To add a check constraint to a domain: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5); +.fi +.if n \{\ +.RE +.\} +.PP +To remove a check constraint from a domain: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode DROP CONSTRAINT zipchk; +.fi +.if n \{\ +.RE +.\} +.PP +To rename a check constraint on a domain: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check; +.fi +.if n \{\ +.RE +.\} +.PP +To move the domain into a different schema: +.sp +.if n \{\ +.RS 4 +.\} +.nf +ALTER DOMAIN zipcode SET SCHEMA customers; +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +\fBALTER DOMAIN\fR +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\&. +.SH "SEE ALSO" +CREATE DOMAIN (\fBCREATE_DOMAIN\fR(7)), DROP DOMAIN (\fBDROP_DOMAIN\fR(7)) |