'\" t .\" Title: ALTER DOMAIN .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.79.1 .\" Date: 2019 .\" Manual: PostgreSQL 11.6 Documentation .\" Source: PostgreSQL 11.6 .\" Language: English .\" .TH "ALTER DOMAIN" "7" "2019" "PostgreSQL 11.6" "PostgreSQL 11.6 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_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 CREATE DOMAIN (\fBCREATE_DOMAIN\fR(7))\&. 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, verify that all data in columns using the domain 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 column 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.13)\&. .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 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))