doc/src/sgml/ref/alter_domain.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERDOMAIN">
   7  <refmeta>
   8   <refentrytitle id="sql-alterdomain-title">ALTER DOMAIN</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ALTER DOMAIN</refname>
  15   <refpurpose>
  16    change the definition of a domain
  17   </refpurpose>
  18  </refnamediv>
  19
  20  <indexterm zone="sql-alterdomain">
  21   <primary>ALTER DOMAIN</primary>
  22  </indexterm>
  23
  24  <refsynopsisdiv>
  25   <synopsis>
  26 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  27     { SET DEFAULT <replaceable class="PARAMETER">expression</replaceable> | DROP DEFAULT }
  28 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  29     { SET | DROP } NOT NULL
  30 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  31     ADD <replaceable class="PARAMETER">domain_constraint</replaceable>
  32 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  33     DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
  34 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  35     OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
  36 ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
  37     SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
  38   </synopsis>
  39  </refsynopsisdiv>
  40
  41  <refsect1>
  42   <title>Description</title>
  43
  44   <para>
  45    <command>ALTER DOMAIN</command> changes the definition of an existing domain.
  46    There are several sub-forms:
  47   </para>
  48
  49   <variablelist>
  50    <varlistentry>
  51     <term>SET/DROP DEFAULT</term>
  52     <listitem>
  53      <para>
  54       These forms set or remove the default value for a domain. Note
  55       that defaults only apply to subsequent <command>INSERT</command>
  56       commands; they do not affect rows already in a table using the domain.
  57      </para>
  58     </listitem>
  59    </varlistentry>
  60
  61    <varlistentry>
  62     <term>SET/DROP NOT NULL</term>
  63     <listitem>
  64      <para>
  65       These forms change whether a domain is marked to allow NULL
  66       values or to reject NULL values.  You can only <literal>SET NOT NULL</>
  67       when the columns using the domain contain no null values.
  68      </para>
  69     </listitem>
  70    </varlistentry>
  71
  72    <varlistentry>
  73     <term>ADD <replaceable class="PARAMETER">domain_constraint</replaceable></term>
  74     <listitem>
  75      <para>
  76       This form adds a new constraint to a domain using the same syntax as
  77       <xref linkend="SQL-CREATEDOMAIN" endterm="SQL-CREATEDOMAIN-TITLE">.
  78       This will only succeed if all columns using the domain satisfy the
  79       new constraint.
  80      </para>
  81     </listitem>
  82    </varlistentry>
  83
  84    <varlistentry>
  85     <term>DROP CONSTRAINT</term>
  86     <listitem>
  87      <para>
  88       This form drops constraints on a domain.
  89      </para>
  90     </listitem>
  91    </varlistentry>
  92
  93    <varlistentry>
  94     <term>OWNER</term>
  95     <listitem>
  96      <para>
  97       This form changes the owner of the domain to the specified user.
  98      </para>
  99     </listitem>
 100    </varlistentry>
 101
 102    <varlistentry>
 103     <term>SET SCHEMA</term>
 104     <listitem>
 105      <para>
 106       This form changes the schema of the domain.  Any constraints
 107       associated with the domain are moved into the new schema as well.
 108      </para>
 109     </listitem>
 110    </varlistentry>
 111   </variablelist>
 112
 113   <para>
 114    You must own the domain to use <command>ALTER DOMAIN</>.
 115    To change the schema of a domain, you must also have
 116    <literal>CREATE</literal> privilege on the new schema.
 117    To alter the owner, you must also be a direct or indirect member of the new
 118    owning role, and that role must have <literal>CREATE</literal> privilege on
 119    the domain's schema.  (These restrictions enforce that altering the owner
 120    doesn't do anything you couldn't do by dropping and recreating the domain.
 121    However, a superuser can alter ownership of any domain anyway.)
 122   </para>
 123  </refsect1>
 124
 125  <refsect1>
 126   <title>Parameters</title>
 127
 128    <para>
 129     <variablelist>
 130      <varlistentry>
 131       <term><replaceable class="PARAMETER">name</replaceable></term>
 132       <listitem>
 133        <para>
 134         The name (possibly schema-qualified) of an existing domain to
 135         alter.
 136        </para>
 137       </listitem>
 138      </varlistentry>
 139
 140      <varlistentry>
 141       <term><replaceable class="PARAMETER">domain_constraint</replaceable></term>
 142       <listitem>
 143        <para>
 144         New domain constraint for the domain.
 145        </para>
 146       </listitem>
 147      </varlistentry>
 148
 149      <varlistentry>
 150       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
 151       <listitem>
 152        <para>
 153         Name of an existing constraint to drop.
 154        </para>
 155       </listitem>
 156      </varlistentry>
 157
 158      <varlistentry>
 159       <term><literal>CASCADE</literal></term>
 160       <listitem>
 161        <para>
 162         Automatically drop objects that depend on the constraint.
 163        </para>
 164       </listitem>
 165      </varlistentry>
 166
 167      <varlistentry>
 168       <term><literal>RESTRICT</literal></term>
 169       <listitem>
 170        <para>
 171         Refuse to drop the constraint if there are any dependent
 172         objects. This is the default behavior.
 173        </para>
 174       </listitem>
 175      </varlistentry>
 176
 177      <varlistentry>
 178       <term><replaceable class="PARAMETER">new_owner</replaceable></term>
 179       <listitem>
 180        <para>
 181         The user name of the new owner of the domain.
 182        </para>
 183       </listitem>
 184      </varlistentry>
 185
 186      <varlistentry>
 187       <term><replaceable class="PARAMETER">new_schema</replaceable></term>
 188       <listitem>
 189        <para>
 190         The new schema for the domain.
 191        </para>
 192       </listitem>
 193      </varlistentry>
 194
 195     </variablelist>
 196    </para>
 197   </refsect1>
 198
 199  <refsect1>
 200   <title>Notes</title>
 201
 202   <para>
 203    Currently, <command>ALTER DOMAIN ADD CONSTRAINT</> and
 204    <command>ALTER DOMAIN SET NOT NULL</> will fail if the named domain or
 205    any derived domain is used within a composite-type column of any
 206    table in the database.  They should eventually be improved to be
 207    able to verify the new constraint for such nested columns.
 208   </para>
 209
 210  </refsect1>
 211
 212  <refsect1>
 213   <title>Examples</title>
 214
 215   <para>
 216    To add a <literal>NOT NULL</literal> constraint to a domain:
 217    <programlisting>
 218 ALTER DOMAIN zipcode SET NOT NULL;
 219    </programlisting>
 220    To remove a <literal>NOT NULL</literal> constraint from a domain:
 221    <programlisting>
 222 ALTER DOMAIN zipcode DROP NOT NULL;
 223    </programlisting>
 224   </para>
 225
 226   <para>
 227    To add a check constraint to a domain:
 228    <programlisting>
 229 ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
 230    </programlisting>
 231   </para>
 232
 233   <para>
 234    To remove a check constraint from a domain:
 235    <programlisting>
 236 ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
 237    </programlisting>
 238   </para>
 239
 240   <para>
 241    To move the domain into a different schema:
 242    <programlisting>
 243 ALTER DOMAIN zipcode SET SCHEMA customers;
 244    </programlisting>
 245   </para>
 246  </refsect1>
 247
 248  <refsect1 id="SQL-ALTERDOMAIN-compatibility">
 249   <title>Compatibility</title>
 250
 251   <para>
 252    <command>ALTER DOMAIN</command> conforms to the <acronym>SQL</acronym>
 253    standard,
 254    except for the <literal>OWNER</> and <literal>SET SCHEMA</> variants,
 255    which are <productname>PostgreSQL</productname> extensions.
 256   </para>
 257  </refsect1>
 258
 259  <refsect1 id="SQL-ALTERDOMAIN-see-also">
 260   <title>See Also</title>
 261
 262   <simplelist type="inline">
 263    <member><xref linkend="sql-createdomain" endterm="sql-createdomain-title"></member>
 264    <member><xref linkend="sql-dropdomain" endterm="sql-dropdomain-title"></member>
 265   </simplelist>
 266  </refsect1>
 267
 268 </refentry>