alter_domain(7)

1ALTER DOMAIN(7)                  SQL Commands                  ALTER DOMAIN(7)
2
3
4

NAME

6       ALTER DOMAIN - change the definition of a domain
7
8

SYNOPSIS

10       ALTER DOMAIN name
11           { SET DEFAULT expression | DROP DEFAULT }
12       ALTER DOMAIN name
13           { SET | DROP } NOT NULL
14       ALTER DOMAIN name
15           ADD domain_constraint
16       ALTER DOMAIN name
17           DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
18       ALTER DOMAIN name
19           OWNER TO new_owner
20       ALTER DOMAIN name
21           SET SCHEMA new_schema
22
23
24

DESCRIPTION

26       ALTER  DOMAIN  changes the definition of an existing domain.  There are
27       several sub-forms:
28
29       SET/DROP DEFAULT
30              These forms set or remove the default value for a  domain.  Note
31              that  defaults only apply to subsequent INSERT commands; they do
32              not affect rows already in a table using the domain.
33
34       SET/DROP NOT NULL
35              These forms change whether a domain is marked to allow NULL val‐
36              ues or to reject NULL values. You can only SET NOT NULL when the
37              columns using the domain contain no null values.
38
39       ADD domain_constraint
40              This form adds a new constraint to a domain using the same  syn‐
41              tax as CREATE DOMAIN [create_domain(7)].  This will only succeed
42              if all columns using the domain satisfy the new constraint.
43
44       DROP CONSTRAINT
45              This form drops constraints on a domain.
46
47       OWNER  This form changes the owner of the domain to the specified user.
48
49       SET SCHEMA
50              This form changes the schema  of  the  domain.  Any  constraints
51              associated  with  the  domain  are  moved into the new schema as
52              well.
53
54       You must own the domain to use ALTER DOMAIN.  To change the schema of a
55       domain,  you  must  also  have  CREATE privilege on the new schema.  To
56       alter the owner, you must also be a direct or indirect  member  of  the
57       new  owning  role,  and  that  role  must  have CREATE privilege on the
58       domain's schema. (These restrictions enforce that  altering  the  owner
59       doesn't  do  anything  you  couldn't  do by dropping and recreating the
60       domain.  However, a superuser can alter ownership of  any  domain  any‐
61       way.)
62

PARAMETERS

64       name   The  name  (possibly  schema-qualified) of an existing domain to
65              alter.
66
67       domain_constraint
68              New domain constraint for the domain.
69
70       constraint_name
71              Name of an existing constraint to drop.
72
73       CASCADE
74              Automatically drop objects that depend on the constraint.
75
76       RESTRICT
77              Refuse to  drop  the  constraint  if  there  are  any  dependent
78              objects. This is the default behavior.
79
80       new_owner
81              The user name of the new owner of the domain.
82
83       new_schema
84              The new schema for the domain.
85

NOTES

87       Currently,  ALTER  DOMAIN  ADD CONSTRAINT and ALTER DOMAIN SET NOT NULL
88       will fail if the named domain or any derived domain is  used  within  a
89       composite-type column of any table in the database. They should eventu‐
90       ally be improved to be able to  verify  the  new  constraint  for  such
91       nested columns.
92

EXAMPLES

94       To add a NOT NULL constraint to a domain:
95
96       ALTER DOMAIN zipcode SET NOT NULL;
97
98
99       To remove a NOT NULL constraint from a domain:
100
101       ALTER DOMAIN zipcode DROP NOT NULL;
102
103
104
105       To add a check constraint to a domain:
106
107       ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
108
109
110
111       To remove a check constraint from a domain:
112
113       ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
114
115
116
117       To move the domain into a different schema:
118
119       ALTER DOMAIN zipcode SET SCHEMA customers;
120
121
122

COMPATIBILITY

124       ALTER DOMAIN conforms to the SQL standard, except for the OWNER and SET
125       SCHEMA variants, which are PostgreSQL extensions.
126