1<!--
2doc/src/sgml/ref/alter_table.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-ALTERTABLE">
7 <indexterm zone="sql-altertable">
8  <primary>ALTER TABLE</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>ALTER TABLE</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>ALTER TABLE</refname>
19  <refpurpose>change the definition of a table</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
25    <replaceable class="PARAMETER">action</replaceable> [, ... ]
26ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
27    RENAME [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> TO <replaceable class="PARAMETER">new_column_name</replaceable>
28ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
29    RENAME CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> TO <replaceable class="PARAMETER">new_constraint_name</replaceable>
30ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
31    RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
32ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
33    SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
34ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
35    SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
36ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
37    ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable>
38ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
39    DETACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable>
40
41<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
42
43    ADD [ COLUMN ] [ IF NOT EXISTS ] <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
44    DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column_name</replaceable> [ RESTRICT | CASCADE ]
45    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ USING <replaceable class="PARAMETER">expression</replaceable> ]
46    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
47    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP DEFAULT
48    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET | DROP } NOT NULL
49    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
50    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
51    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP IDENTITY [ IF EXISTS ]
52    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
53    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
54    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
55    ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
56    ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
57    ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
58    ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
59    VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
60    DROP CONSTRAINT [ IF EXISTS ]  <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
61    DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
62    ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
63    ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
64    ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
65    DISABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
66    ENABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
67    ENABLE REPLICA RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
68    ENABLE ALWAYS RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
69    DISABLE ROW LEVEL SECURITY
70    ENABLE ROW LEVEL SECURITY
71    FORCE ROW LEVEL SECURITY
72    NO FORCE ROW LEVEL SECURITY
73    CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
74    SET WITHOUT CLUSTER
75    SET WITH OIDS
76    SET WITHOUT OIDS
77    SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
78    SET { LOGGED | UNLOGGED }
79    SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )
80    RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
81    INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
82    NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
83    OF <replaceable class="PARAMETER">type_name</replaceable>
84    NOT OF
85    OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
86    REPLICA IDENTITY { DEFAULT | USING INDEX <replaceable class="PARAMETER">index_name</replaceable> | FULL | NOTHING }
87
88<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
89
90    [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
91    { UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
92    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
93</synopsis>
94 </refsynopsisdiv>
95
96 <refsect1>
97  <title>Description</title>
98
99  <para>
100   <command>ALTER TABLE</command> changes the definition of an existing table.
101   There are several subforms described below. Note that the lock level required
102   may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is
103   acquired unless explicitly noted. When multiple subcommands are given, the
104   lock acquired will be the strictest one required by any subcommand.
105
106  <variablelist>
107   <varlistentry>
108    <term><literal>ADD COLUMN [ IF NOT EXISTS ]</literal></term>
109    <listitem>
110     <para>
111      This form adds a new column to the table, using the same syntax as
112      <xref linkend="SQL-CREATETABLE">. If <literal>IF NOT EXISTS</literal>
113      is specified and a column already exists with this name,
114      no error is thrown.
115     </para>
116    </listitem>
117   </varlistentry>
118
119   <varlistentry>
120    <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
121    <listitem>
122     <para>
123      This form drops a column from a table.  Indexes and
124      table constraints involving the column will be automatically
125      dropped as well.
126      Multivariate statistics referencing the dropped column will also be
127      removed if the removal of the column would cause the statistics to
128      contain data for only a single column.
129      You will need to say <literal>CASCADE</> if anything outside the table
130      depends on the column, for example, foreign key references or views.
131      If <literal>IF EXISTS</literal> is specified and the column
132      does not exist, no error is thrown. In this case a notice
133      is issued instead.
134     </para>
135    </listitem>
136   </varlistentry>
137
138   <varlistentry>
139    <term><literal>SET DATA TYPE</literal></term>
140    <listitem>
141     <para>
142      This form changes the type of a column of a table. Indexes and
143      simple table constraints involving the column will be automatically
144      converted to use the new column type by reparsing the originally
145      supplied expression.
146      The optional <literal>COLLATE</literal> clause specifies a collation
147      for the new column; if omitted, the collation is the default for the
148      new column type.
149      The optional <literal>USING</literal>
150      clause specifies how to compute the new column value from the old;
151      if omitted, the default conversion is the same as an assignment
152      cast from old data type to new.  A  <literal>USING</literal>
153      clause must be provided if there is no implicit or assignment
154      cast from old to new type.
155     </para>
156    </listitem>
157   </varlistentry>
158
159   <varlistentry>
160    <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
161    <listitem>
162     <para>
163      These forms set or remove the default value for a column.
164      Default values only apply in subsequent <command>INSERT</command>
165      or <command>UPDATE</> commands; they do not cause rows already in the
166      table to change.
167     </para>
168    </listitem>
169   </varlistentry>
170
171   <varlistentry>
172    <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
173    <listitem>
174     <para>
175      These forms change whether a column is marked to allow null
176      values or to reject null values.  You can only use <literal>SET
177      NOT NULL</> when the column contains no null values.
178     </para>
179
180     <para>
181      If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
182      on a column if it is marked <literal>NOT NULL</literal> in the parent
183      table.  To drop the <literal>NOT NULL</literal> constraint from all the
184      partitions, perform <literal>DROP NOT NULL</literal> on the parent
185      table.  Even if there is no <literal>NOT NULL</> constraint on the
186      parent, such a constraint can still be added to individual partitions,
187      if desired; that is, the children can disallow nulls even if the parent
188      allows them, but not the other way around.
189     </para>
190    </listitem>
191   </varlistentry>
192
193   <varlistentry>
194    <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>
195    <term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>
196    <term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term>
197    <listitem>
198     <para>
199      These forms change whether a column is an identity column or change the
200      generation attribute of an existing identity column.
201      See <xref linkend="sql-createtable"> for details.
202     </para>
203
204     <para>
205      If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
206      column is not an identity column, no error is thrown.  In this case a
207      notice is issued instead.
208     </para>
209    </listitem>
210   </varlistentry>
211
212   <varlistentry>
213    <term><literal>SET <replaceable>sequence_option</replaceable></literal></term>
214    <term><literal>RESTART</literal></term>
215    <listitem>
216     <para>
217      These forms alter the sequence that underlies an existing identity
218      column.  <replaceable>sequence_option</replaceable> is an option
219      supported by <xref linkend="sql-altersequence"> such
220      as <literal>INCREMENT BY</literal>.
221     </para>
222    </listitem>
223   </varlistentry>
224
225   <varlistentry>
226    <term><literal>SET STATISTICS</literal></term>
227    <listitem>
228     <para>
229      This form
230      sets the per-column statistics-gathering target for subsequent
231      <xref linkend="sql-analyze"> operations.
232      The target can be set in the range 0 to 10000; alternatively, set it
233      to -1 to revert to using the system default statistics
234      target (<xref linkend="guc-default-statistics-target">).
235      For more information on the use of statistics by the
236      <productname>PostgreSQL</productname> query planner, refer to
237      <xref linkend="planner-stats">.
238     </para>
239     <para>
240      <literal>SET STATISTICS</literal> acquires a
241      <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
242     </para>
243    </listitem>
244   </varlistentry>
245
246   <varlistentry>
247    <term><literal>SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
248    <term><literal>RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )</literal></term>
249    <listitem>
250     <para>
251      This form sets or resets per-attribute options.  Currently, the only
252      defined per-attribute options are <literal>n_distinct</> and
253      <literal>n_distinct_inherited</>, which override the
254      number-of-distinct-values estimates made by subsequent
255      <xref linkend="sql-analyze">
256      operations.  <literal>n_distinct</> affects the statistics for the table
257      itself, while <literal>n_distinct_inherited</> affects the statistics
258      gathered for the table plus its inheritance children.  When set to a
259      positive value, <command>ANALYZE</> will assume that the column contains
260      exactly the specified number of distinct nonnull values.  When set to a
261      negative value, which must be greater
262      than or equal to -1, <command>ANALYZE</> will assume that the number of
263      distinct nonnull values in the column is linear in the size of the
264      table; the exact count is to be computed by multiplying the estimated
265      table size by the absolute value of the given number.  For example,
266      a value of -1 implies that all values in the column are distinct, while
267      a value of -0.5 implies that each value appears twice on the average.
268      This can be useful when the size of the table changes over time, since
269      the multiplication by the number of rows in the table is not performed
270      until query planning time.  Specify a value of 0 to revert to estimating
271      the number of distinct values normally.  For more information on the use
272      of statistics by the <productname>PostgreSQL</productname> query
273      planner, refer to <xref linkend="planner-stats">.
274     </para>
275     <para>
276      Changing per-attribute options acquires a
277      <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
278     </para>
279    </listitem>
280   </varlistentry>
281
282   <varlistentry>
283    <term>
284     <literal>SET STORAGE</literal>
285     <indexterm>
286      <primary>TOAST</primary>
287      <secondary>per-column storage settings</secondary>
288     </indexterm>
289    </term>
290    <listitem>
291     <para>
292      This form sets the storage mode for a column. This controls whether this
293      column is held inline or in a secondary <acronym>TOAST</> table, and
294      whether the data
295      should be compressed or not. <literal>PLAIN</literal> must be used
296      for fixed-length values such as <type>integer</type> and is
297      inline, uncompressed. <literal>MAIN</literal> is for inline,
298      compressible data. <literal>EXTERNAL</literal> is for external,
299      uncompressed data, and <literal>EXTENDED</literal> is for external,
300      compressed data.  <literal>EXTENDED</literal> is the default for most
301      data types that support non-<literal>PLAIN</literal> storage.
302      Use of <literal>EXTERNAL</literal> will make substring operations on
303      very large <type>text</type> and <type>bytea</type> values run faster,
304      at the penalty of increased storage space.  Note that
305      <literal>SET STORAGE</> doesn't itself change anything in the table,
306      it just sets the strategy to be pursued during future table updates.
307      See <xref linkend="storage-toast"> for more information.
308     </para>
309    </listitem>
310   </varlistentry>
311
312   <varlistentry>
313    <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]</literal></term>
314    <listitem>
315     <para>
316      This form adds a new constraint to a table using the same constraint
317      syntax as <xref linkend="sql-createtable">, plus the option <literal>NOT
318      VALID</literal>, which is currently only allowed for foreign key
319      and CHECK constraints.
320     </para>
321
322     <para>
323      Normally, this form will cause a scan of the table to verify that all
324      existing rows in the table satisfy the new constraint.  But if
325      the <literal>NOT VALID</literal> option is used, this
326      potentially-lengthy scan is skipped.  The constraint will still be
327      enforced against subsequent inserts or updates (that is, they'll fail
328      unless there is a matching row in the referenced table, in the case
329      of foreign keys, or they'll fail unless the new row matches the
330      specified check condition).  But the
331      database will not assume that the constraint holds for all rows in
332      the table, until it is validated by using the <literal>VALIDATE
333      CONSTRAINT</literal> option.
334      See <xref linkend="sql-altertable-notes"
335      endterm="sql-altertable-notes-title"> below for more information
336      about using the <literal>NOT VALID</literal> option.
337     </para>
338
339     <para>
340      Although most forms of <literal>ADD
341      <replaceable class="parameter">table_constraint</replaceable></literal>
342      require an <literal>ACCESS EXCLUSIVE</literal> lock, <literal>ADD
343      FOREIGN KEY</literal> requires only a <literal>SHARE ROW
344      EXCLUSIVE</literal> lock.  Note that <literal>ADD FOREIGN KEY</literal>
345      also acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the
346      referenced table, in addition to the lock on the table on which the
347      constraint is declared.
348     </para>
349    </listitem>
350   </varlistentry>
351
352   <varlistentry>
353    <term><literal>ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable></literal></term>
354    <listitem>
355     <para>
356      This form adds a new <literal>PRIMARY KEY</> or <literal>UNIQUE</>
357      constraint to a table based on an existing unique index.  All the
358      columns of the index will be included in the constraint.
359     </para>
360
361     <para>
362      The index cannot have expression columns nor be a partial index.
363      Also, it must be a b-tree index with default sort ordering.  These
364      restrictions ensure that the index is equivalent to one that would be
365      built by a regular <literal>ADD PRIMARY KEY</> or <literal>ADD UNIQUE</>
366      command.
367     </para>
368
369     <para>
370      If <literal>PRIMARY KEY</> is specified, and the index's columns are not
371      already marked <literal>NOT NULL</>, then this command will attempt to
372      do <literal>ALTER COLUMN SET NOT NULL</> against each such column.
373      That requires a full table scan to verify the column(s) contain no
374      nulls.  In all other cases, this is a fast operation.
375     </para>
376
377     <para>
378      If a constraint name is provided then the index will be renamed to match
379      the constraint name.  Otherwise the constraint will be named the same as
380      the index.
381     </para>
382
383     <para>
384      After this command is executed, the index is <quote>owned</> by the
385      constraint, in the same way as if the index had been built by
386      a regular <literal>ADD PRIMARY KEY</> or <literal>ADD UNIQUE</>
387      command.  In particular, dropping the constraint will make the index
388      disappear too.
389     </para>
390
391     <note>
392      <para>
393       Adding a constraint using an existing index can be helpful in
394       situations where a new constraint needs to be added without blocking
395       table updates for a long time.  To do that, create the index using
396       <command>CREATE INDEX CONCURRENTLY</>, and then install it as an
397       official constraint using this syntax.  See the example below.
398      </para>
399     </note>
400    </listitem>
401   </varlistentry>
402
403   <varlistentry>
404    <term><literal>ALTER CONSTRAINT</literal></term>
405    <listitem>
406     <para>
407      This form alters the attributes of a constraint that was previously
408      created. Currently only foreign key constraints may be altered.
409     </para>
410    </listitem>
411   </varlistentry>
412
413   <varlistentry>
414    <term><literal>VALIDATE CONSTRAINT</literal></term>
415    <listitem>
416     <para>
417      This form validates a foreign key or check constraint that was
418      previously created as <literal>NOT VALID</literal>, by scanning the
419      table to ensure there are no rows for which the constraint is not
420      satisfied.  Nothing happens if the constraint is already marked valid.
421      (See <xref linkend="sql-altertable-notes"
422      endterm="sql-altertable-notes-title"> below for an explanation of the
423      usefulness of this command.)
424     </para>
425     <para>
426      This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
427     </para>
428    </listitem>
429   </varlistentry>
430
431   <varlistentry>
432    <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
433    <listitem>
434     <para>
435      This form drops the specified constraint on a table.
436      If <literal>IF EXISTS</literal> is specified and the constraint
437      does not exist, no error is thrown. In this case a notice is issued instead.
438     </para>
439    </listitem>
440   </varlistentry>
441
442   <varlistentry>
443    <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
444    <listitem>
445     <para>
446      These forms configure the firing of trigger(s) belonging to the table.
447      A disabled trigger is still known to the system, but is not executed
448      when its triggering event occurs.  For a deferred trigger, the enable
449      status is checked when the event occurs, not when the trigger function
450      is actually executed.  One can disable or enable a single
451      trigger specified by name, or all triggers on the table, or only
452      user triggers (this option excludes internally generated constraint
453      triggers such as those that are used to implement foreign key
454      constraints or deferrable uniqueness and exclusion constraints).
455      Disabling or enabling internally generated constraint triggers
456      requires superuser privileges; it should be done with caution since
457      of course the integrity of the constraint cannot be guaranteed if the
458      triggers are not executed.
459      The trigger firing mechanism is also affected by the configuration
460      variable <xref linkend="guc-session-replication-role">. Simply enabled
461      triggers will fire when the replication role is <quote>origin</>
462      (the default) or <quote>local</>. Triggers configured as <literal>ENABLE
463      REPLICA</literal> will only fire if the session is in <quote>replica</>
464      mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
465      fire regardless of the current replication mode.
466     </para>
467     <para>
468      This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
469     </para>
470    </listitem>
471   </varlistentry>
472
473   <varlistentry>
474    <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term>
475    <listitem>
476     <para>
477      These forms configure the firing of rewrite rules belonging to the table.
478      A disabled rule is still known to the system, but is not applied
479      during query rewriting. The semantics are as for disabled/enabled
480      triggers. This configuration is ignored for <literal>ON SELECT</literal> rules, which
481      are always applied in order to keep views working even if the current
482      session is in a non-default replication role.
483     </para>
484    </listitem>
485   </varlistentry>
486
487   <varlistentry>
488    <term><literal>DISABLE</literal>/<literal>ENABLE ROW LEVEL SECURITY</literal></term>
489    <listitem>
490     <para>
491      These forms control the application of row security policies belonging
492      to the table.  If enabled and no policies exist for the table, then a
493      default-deny policy is applied.  Note that policies can exist for a table
494      even if row level security is disabled - in this case, the policies will
495      NOT be applied and the policies will be ignored.
496      See also
497      <xref linkend="SQL-CREATEPOLICY">.
498     </para>
499    </listitem>
500   </varlistentry>
501
502   <varlistentry>
503    <term><literal>NO FORCE</literal>/<literal>FORCE ROW LEVEL SECURITY</literal></term>
504    <listitem>
505     <para>
506      These forms control the application of row security policies belonging
507      to the table when the user is the table owner.  If enabled, row level
508      security policies will be applied when the user is the table owner.  If
509      disabled (the default) then row level security will not be applied when
510      the user is the table owner.
511      See also
512      <xref linkend="SQL-CREATEPOLICY">.
513     </para>
514    </listitem>
515   </varlistentry>
516
517   <varlistentry>
518    <term><literal>CLUSTER ON</literal></term>
519    <listitem>
520     <para>
521      This form selects the default index for future
522      <xref linkend="SQL-CLUSTER">
523      operations.  It does not actually re-cluster the table.
524     </para>
525     <para>
526      Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
527     </para>
528    </listitem>
529   </varlistentry>
530
531   <varlistentry>
532    <term><literal>SET WITHOUT CLUSTER</literal></term>
533    <listitem>
534     <para>
535      This form removes the most recently used
536      <xref linkend="SQL-CLUSTER">
537      index specification from the table.  This affects
538      future cluster operations that don't specify an index.
539     </para>
540     <para>
541      Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
542     </para>
543    </listitem>
544   </varlistentry>
545
546   <varlistentry>
547    <term><literal>SET WITH OIDS</literal></term>
548    <listitem>
549     <para>
550      This form adds an <literal>oid</literal> system column to the
551      table (see <xref linkend="ddl-system-columns">).
552      It does nothing if the table already has OIDs.
553     </para>
554
555     <para>
556      Note that this is not equivalent to <literal>ADD COLUMN oid oid</>;
557      that would add a normal column that happened to be named
558      <literal>oid</>, not a system column.
559     </para>
560    </listitem>
561   </varlistentry>
562
563   <varlistentry>
564    <term><literal>SET WITHOUT OIDS</literal></term>
565    <listitem>
566     <para>
567      This form removes the <literal>oid</literal> system column from the
568      table.  This is exactly equivalent to
569      <literal>DROP COLUMN oid RESTRICT</literal>,
570      except that it will not complain if there is already no
571      <literal>oid</literal> column.
572     </para>
573    </listitem>
574   </varlistentry>
575
576   <varlistentry>
577    <term><literal>SET TABLESPACE</literal></term>
578    <listitem>
579     <para>
580      This form changes the table's tablespace to the specified tablespace and
581      moves the data file(s) associated with the table to the new tablespace.
582      Indexes on the table, if any, are not moved; but they can be moved
583      separately with additional <literal>SET TABLESPACE</literal> commands.
584      All tables in the current database in a tablespace can be moved by using
585      the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
586      to be moved first and then move each one.  This form also supports
587      <literal>OWNED BY</literal>, which will only move tables owned by the
588      roles specified.  If the <literal>NOWAIT</literal> option is specified
589      then the command will fail if it is unable to acquire all of the locks
590      required immediately.  Note that system catalogs are not moved by this
591      command, use <command>ALTER DATABASE</command> or explicit
592      <command>ALTER TABLE</command> invocations instead if desired.  The
593      <literal>information_schema</literal> relations are not considered part
594      of the system catalogs and will be moved.
595      See also
596      <xref linkend="SQL-CREATETABLESPACE">.
597     </para>
598    </listitem>
599   </varlistentry>
600
601   <varlistentry>
602    <term><literal>SET { LOGGED | UNLOGGED }</literal></term>
603    <listitem>
604     <para>
605      This form changes the table from unlogged to logged or vice-versa
606      (see <xref linkend="SQL-CREATETABLE-UNLOGGED">).  It cannot be applied
607      to a temporary table.
608     </para>
609    </listitem>
610   </varlistentry>
611
612   <varlistentry>
613    <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term>
614    <listitem>
615     <para>
616      This form changes one or more storage parameters for the table.  See
617      <xref linkend="SQL-CREATETABLE-storage-parameters"
618      endterm="SQL-CREATETABLE-storage-parameters-title">
619      for details on the available parameters.  Note that the table contents
620      will not be modified immediately by this command; depending on the
621      parameter you might need to rewrite the table to get the desired effects.
622      That can be done with <link linkend="SQL-VACUUM">VACUUM
623      FULL</>, <xref linkend="SQL-CLUSTER"> or one of the forms
624      of <command>ALTER TABLE</> that forces a table rewrite.
625      For planner related parameters, changes will take effect from the next
626      time the table is locked so currently executing queries will not be
627      affected.
628     </para>
629
630     <para>
631      <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
632      fillfactor and autovacuum storage parameters, as well as the
633      planner parameter <varname>parallel_workers</>.
634     </para>
635
636     <note>
637      <para>
638       While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified
639       in the <literal>WITH (<replaceable
640       class="PARAMETER">storage_parameter</>)</literal> syntax,
641       <command>ALTER TABLE</> does not treat <literal>OIDS</> as a
642       storage parameter.  Instead use the <literal>SET WITH OIDS</>
643       and <literal>SET WITHOUT OIDS</> forms to change OID status.
644      </para>
645     </note>
646    </listitem>
647   </varlistentry>
648
649   <varlistentry>
650    <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
651    <listitem>
652     <para>
653      This form resets one or more storage parameters to their
654      defaults.  As with <literal>SET</>, a table rewrite might be
655      needed to update the table entirely.
656     </para>
657    </listitem>
658   </varlistentry>
659
660   <varlistentry>
661    <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
662    <listitem>
663     <para>
664      This form adds the target table as a new child of the specified parent
665      table.  Subsequently, queries against the parent will include records
666      of the target table.  To be added as a child, the target table must
667      already contain all the same columns as the parent (it could have
668      additional columns, too).  The columns must have matching data types,
669      and if they have <literal>NOT NULL</literal> constraints in the parent
670      then they must also have <literal>NOT NULL</literal> constraints in the
671      child.
672     </para>
673
674     <para>
675      There must also be matching child-table constraints for all
676      <literal>CHECK</literal> constraints of the parent, except those
677      marked non-inheritable (that is, created with <literal>ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT</literal>)
678      in the parent, which are ignored; all child-table constraints matched
679      must not be marked non-inheritable.
680      Currently
681      <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
682      <literal>FOREIGN KEY</literal> constraints are not considered, but
683      this might change in the future.
684     </para>
685    </listitem>
686   </varlistentry>
687
688   <varlistentry>
689    <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
690    <listitem>
691     <para>
692      This form removes the target table from the list of children of the
693      specified parent table.
694      Queries against the parent table will no longer include records drawn
695      from the target table.
696     </para>
697    </listitem>
698   </varlistentry>
699
700   <varlistentry>
701    <term><literal>OF <replaceable class="PARAMETER">type_name</replaceable></literal></term>
702    <listitem>
703     <para>
704      This form links the table to a composite type as though <command>CREATE
705      TABLE OF</> had formed it.  The table's list of column names and types
706      must precisely match that of the composite type; the presence of
707      an <literal>oid</> system column is permitted to differ.  The table must
708      not inherit from any other table.  These restrictions ensure
709      that <command>CREATE TABLE OF</> would permit an equivalent table
710      definition.
711     </para>
712    </listitem>
713   </varlistentry>
714
715   <varlistentry>
716    <term><literal>NOT OF</literal></term>
717    <listitem>
718     <para>
719      This form dissociates a typed table from its type.
720     </para>
721    </listitem>
722   </varlistentry>
723
724   <varlistentry>
725    <term><literal>OWNER</literal></term>
726    <listitem>
727     <para>
728      This form changes the owner of the table, sequence, view, materialized view,
729      or foreign table to the specified user.
730     </para>
731    </listitem>
732   </varlistentry>
733
734   <varlistentry id="SQL-CREATETABLE-REPLICA-IDENTITY">
735    <term><literal>REPLICA IDENTITY</literal></term>
736    <listitem>
737     <para>
738      This form changes the information which is written to the write-ahead log
739      to identify rows which are updated or deleted.  This option has no effect
740      except when logical replication is in use.  <literal>DEFAULT</>
741      (the default for non-system tables) records the
742      old values of the columns of the primary key, if any.  <literal>USING INDEX</>
743      records the old values of the columns covered by the named index, which
744      must be unique, not partial, not deferrable, and include only columns marked
745      <literal>NOT NULL</>.  <literal>FULL</> records the old values of all columns
746      in the row.  <literal>NOTHING</> records no information about the old row.
747      (This is the default for system tables.)
748      In all cases, no old values are logged unless at least one of the columns
749      that would be logged differs between the old and new versions of the row.
750     </para>
751    </listitem>
752   </varlistentry>
753
754   <varlistentry>
755    <term><literal>RENAME</literal></term>
756    <listitem>
757     <para>
758      The <literal>RENAME</literal> forms change the name of a table
759      (or an index, sequence, view, materialized view, or foreign table), the name
760      of an individual column in a table, or the name of a constraint of the table.
761      There is no effect on the stored data.
762     </para>
763    </listitem>
764   </varlistentry>
765
766   <varlistentry>
767    <term><literal>SET SCHEMA</literal></term>
768    <listitem>
769     <para>
770      This form moves the table into another schema.  Associated indexes,
771      constraints, and sequences owned by table columns are moved as well.
772     </para>
773    </listitem>
774   </varlistentry>
775
776   <varlistentry>
777    <term><literal>ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable></literal></term>
778    <listitem>
779     <para>
780      This form attaches an existing table (which might itself be partitioned)
781      as a partition of the target table using the same syntax for
782      <replaceable class="PARAMETER">partition_bound_spec</replaceable> as
783      <xref linkend="sql-createtable">.  The partition bound specification
784      must correspond to the partitioning strategy and partition key of the
785      target table.  The table to be attached must have all the same columns
786      as the target table and no more; moreover, the column types must also
787      match.  Also, it must have all the <literal>NOT NULL</literal> and
788      <literal>CHECK</literal> constraints of the target table.  Currently
789      <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
790      <literal>FOREIGN KEY</literal> constraints are not considered.
791      If any of the <literal>CHECK</literal> constraints of the table being
792      attached are marked <literal>NO INHERIT</literal>, the command will fail;
793      such constraints must be recreated without the
794      <literal>NO INHERIT</literal> clause.
795     </para>
796
797     <para>
798      If the new partition is a regular table, a full table scan is performed
799      to check that existing rows in the table do not violate the partition
800      constraint. It is possible to avoid this scan by adding a valid
801      <literal>CHECK</literal> constraint to the table that allows only
802      rows satisfying the desired partition constraint before running this
803      command. The <literal>CHECK</literal> constraint will be used to
804      determine that the table need not be scanned to validate the partition
805      constraint. This does not work, however, if any of the partition keys
806      is an expression and the partition does not accept
807      <literal>NULL</literal> values. If attaching a list partition that will
808      not accept <literal>NULL</literal> values, also add
809      <literal>NOT NULL</literal> constraint to the partition key column,
810      unless it's an expression.
811     </para>
812
813     <para>
814      If the new partition is a foreign table, nothing is done to verify
815      that all the rows in the foreign table obey the partition constraint.
816      (See the discussion in <xref linkend="SQL-CREATEFOREIGNTABLE"> about
817      constraints on the foreign table.)
818     </para>
819    </listitem>
820   </varlistentry>
821
822   <varlistentry>
823    <term><literal>DETACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable></term>
824    <listitem>
825     <para>
826      This form detaches specified partition of the target table.  The detached
827      partition continues to exist as a standalone table, but no longer has any
828      ties to the table from which it was detached.
829     </para>
830    </listitem>
831   </varlistentry>
832
833  </variablelist>
834  </para>
835
836  <para>
837   All the forms of ALTER TABLE that act on a single table, except
838   <literal>RENAME</literal>, <literal>SET SCHEMA</literal>,
839   <literal>ATTACH PARTITION</literal>, and
840   <literal>DETACH PARTITION</literal> can be combined into
841   a list of multiple alterations to be applied together.  For example, it
842   is possible to add several columns and/or alter the type of several
843   columns in a single command.  This is particularly useful with large
844   tables, since only one pass over the table need be made.
845  </para>
846
847  <para>
848   You must own the table to use <command>ALTER TABLE</>.
849   To change the schema or tablespace of a table, you must also have
850   <literal>CREATE</literal> privilege on the new schema or tablespace.
851   To add the table as a new child of a parent table, you must own the parent
852   table as well.  Also, to attach a table as a new partition of the table,
853   you must own the table being attached.
854   To alter the owner, you must also be a direct or indirect member of the new
855   owning role, and that role must have <literal>CREATE</literal> privilege on
856   the table's schema.  (These restrictions enforce that altering the owner
857   doesn't do anything you couldn't do by dropping and recreating the table.
858   However, a superuser can alter ownership of any table anyway.)
859   To add a column or alter a column type or use the <literal>OF</literal>
860   clause, you must also have <literal>USAGE</literal> privilege on the data
861   type.
862  </para>
863 </refsect1>
864
865 <refsect1>
866  <title>Parameters</title>
867
868    <variablelist>
869
870     <varlistentry>
871      <term><literal>IF EXISTS</literal></term>
872      <listitem>
873       <para>
874        Do not throw an error if the table does not exist. A notice is issued
875        in this case.
876       </para>
877      </listitem>
878     </varlistentry>
879
880     <varlistentry>
881      <term><replaceable class="PARAMETER">name</replaceable></term>
882      <listitem>
883       <para>
884        The name (optionally schema-qualified) of an existing table to
885        alter. If <literal>ONLY</> is specified before the table name, only
886        that table is altered. If <literal>ONLY</> is not specified, the table
887        and all its descendant tables (if any) are altered.  Optionally,
888        <literal>*</> can be specified after the table name to explicitly
889        indicate that descendant tables are included.
890       </para>
891      </listitem>
892     </varlistentry>
893
894     <varlistentry>
895      <term><replaceable class="PARAMETER">column_name</replaceable></term>
896      <listitem>
897       <para>
898        Name of a new or existing column.
899       </para>
900      </listitem>
901     </varlistentry>
902
903     <varlistentry>
904      <term><replaceable class="PARAMETER">new_column_name</replaceable></term>
905      <listitem>
906       <para>
907        New name for an existing column.
908       </para>
909      </listitem>
910     </varlistentry>
911
912     <varlistentry>
913      <term><replaceable class="PARAMETER">new_name</replaceable></term>
914      <listitem>
915       <para>
916        New name for the table.
917       </para>
918      </listitem>
919     </varlistentry>
920
921     <varlistentry>
922      <term><replaceable class="PARAMETER">data_type</replaceable></term>
923      <listitem>
924       <para>
925        Data type of the new column, or new data type for an existing
926        column.
927       </para>
928      </listitem>
929     </varlistentry>
930
931     <varlistentry>
932      <term><replaceable class="PARAMETER">table_constraint</replaceable></term>
933      <listitem>
934       <para>
935        New table constraint for the table.
936       </para>
937      </listitem>
938     </varlistentry>
939
940     <varlistentry>
941      <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
942      <listitem>
943       <para>
944        Name of a new or existing constraint.
945       </para>
946      </listitem>
947     </varlistentry>
948
949     <varlistentry>
950      <term><literal>CASCADE</literal></term>
951      <listitem>
952       <para>
953        Automatically drop objects that depend on the dropped column
954        or constraint (for example, views referencing the column),
955        and in turn all objects that depend on those objects
956        (see <xref linkend="ddl-depend">).
957       </para>
958      </listitem>
959     </varlistentry>
960
961     <varlistentry>
962      <term><literal>RESTRICT</literal></term>
963      <listitem>
964       <para>
965        Refuse to drop the column or constraint if there are any dependent
966        objects. This is the default behavior.
967       </para>
968      </listitem>
969     </varlistentry>
970
971     <varlistentry>
972      <term><replaceable class="PARAMETER">trigger_name</replaceable></term>
973      <listitem>
974       <para>
975        Name of a single trigger to disable or enable.
976       </para>
977      </listitem>
978     </varlistentry>
979
980     <varlistentry>
981      <term><literal>ALL</literal></term>
982      <listitem>
983       <para>
984        Disable or enable all triggers belonging to the table.
985        (This requires superuser privilege if any of the triggers are
986        internally generated constraint triggers such as those that are used
987        to implement foreign key constraints or deferrable uniqueness and
988        exclusion constraints.)
989       </para>
990      </listitem>
991     </varlistentry>
992
993     <varlistentry>
994      <term><literal>USER</literal></term>
995      <listitem>
996       <para>
997        Disable or enable all triggers belonging to the table except for
998        internally generated constraint triggers such as those that are used
999        to implement foreign key constraints or deferrable uniqueness and
1000        exclusion constraints.
1001       </para>
1002      </listitem>
1003     </varlistentry>
1004
1005     <varlistentry>
1006      <term><replaceable class="PARAMETER">index_name</replaceable></term>
1007      <listitem>
1008       <para>
1009        The name of an existing index.
1010       </para>
1011      </listitem>
1012     </varlistentry>
1013
1014     <varlistentry>
1015      <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
1016      <listitem>
1017       <para>
1018        The name of a table storage parameter.
1019       </para>
1020      </listitem>
1021     </varlistentry>
1022
1023     <varlistentry>
1024      <term><replaceable class="PARAMETER">value</replaceable></term>
1025      <listitem>
1026       <para>
1027        The new value for a table storage parameter.
1028        This might be a number or a word depending on the parameter.
1029       </para>
1030      </listitem>
1031     </varlistentry>
1032
1033     <varlistentry>
1034      <term><replaceable class="PARAMETER">parent_table</replaceable></term>
1035      <listitem>
1036       <para>
1037        A parent table to associate or de-associate with this table.
1038       </para>
1039      </listitem>
1040     </varlistentry>
1041
1042     <varlistentry>
1043      <term><replaceable class="PARAMETER">new_owner</replaceable></term>
1044      <listitem>
1045       <para>
1046        The user name of the new owner of the table.
1047       </para>
1048      </listitem>
1049     </varlistentry>
1050
1051     <varlistentry>
1052      <term><replaceable class="PARAMETER">new_tablespace</replaceable></term>
1053      <listitem>
1054       <para>
1055        The name of the tablespace to which the table will be moved.
1056       </para>
1057      </listitem>
1058     </varlistentry>
1059
1060     <varlistentry>
1061      <term><replaceable class="PARAMETER">new_schema</replaceable></term>
1062      <listitem>
1063       <para>
1064        The name of the schema to which the table will be moved.
1065       </para>
1066      </listitem>
1067     </varlistentry>
1068
1069     <varlistentry>
1070      <term><replaceable class="PARAMETER">partition_name</replaceable></term>
1071      <listitem>
1072       <para>
1073        The name of the table to attach as a new partition or to detach from this table.
1074       </para>
1075      </listitem>
1076     </varlistentry>
1077
1078     <varlistentry>
1079      <term><replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
1080      <listitem>
1081       <para>
1082        The partition bound specification for a new partition.  Refer to
1083        <xref linkend="sql-createtable"> for more details on the syntax of the same.
1084       </para>
1085      </listitem>
1086     </varlistentry>
1087
1088    </variablelist>
1089 </refsect1>
1090
1091 <refsect1 id="sql-altertable-notes">
1092  <title id="sql-altertable-notes-title">Notes</title>
1093
1094   <para>
1095    The key word <literal>COLUMN</literal> is noise and can be omitted.
1096   </para>
1097
1098   <para>
1099    When a column is added with <literal>ADD COLUMN</literal>, all existing
1100    rows in the table are initialized with the column's default value
1101    (NULL if no <literal>DEFAULT</> clause is specified).
1102    If there is no <literal>DEFAULT</> clause, this is merely a metadata
1103    change and does not require any immediate update of the table's data;
1104    the added NULL values are supplied on readout, instead.
1105   </para>
1106
1107   <para>
1108    Adding a column with a <literal>DEFAULT</> clause or changing the type of
1109    an existing column will require the entire table and its indexes to be
1110    rewritten.  As an exception when changing the type of an existing column,
1111    if the <literal>USING</> clause does not change the column
1112    contents and the old type is either binary coercible to the new type or
1113    an unconstrained domain over the new type, a table rewrite is not needed;
1114    but any indexes on the affected columns must still be rebuilt.  Adding or
1115    removing a system <literal>oid</> column also requires rewriting the entire
1116    table.  Table and/or index rebuilds may take a significant amount of time
1117    for a large table; and will temporarily require as much as double the disk
1118    space.
1119   </para>
1120
1121   <para>
1122    Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
1123    scanning the table to verify that existing rows meet the constraint,
1124    but does not require a table rewrite.
1125   </para>
1126
1127   <para>
1128    Similarly, when attaching a new partition it may be scanned to verify that
1129    existing rows meet the partition constraint.
1130   </para>
1131
1132   <para>
1133    The main reason for providing the option to specify multiple changes
1134    in a single <command>ALTER TABLE</> is that multiple table scans or
1135    rewrites can thereby be combined into a single pass over the table.
1136   </para>
1137
1138   <para>
1139    Scanning a large table to verify a new foreign key or check constraint
1140    can take a long time, and other updates to the table are locked out
1141    until the <command>ALTER TABLE ADD CONSTRAINT</command> command is
1142    committed.  The main purpose of the <literal>NOT VALID</literal>
1143    constraint option is to reduce the impact of adding a constraint on
1144    concurrent updates.  With <literal>NOT VALID</literal>,
1145    the <command>ADD CONSTRAINT</command> command does not scan the table
1146    and can be committed immediately.  After that, a <literal>VALIDATE
1147    CONSTRAINT</literal> command can be issued to verify that existing rows
1148    satisfy the constraint.  The validation step does not need to lock out
1149    concurrent updates, since it knows that other transactions will be
1150    enforcing the constraint for rows that they insert or update; only
1151    pre-existing rows need to be checked.  Hence, validation acquires only
1152    a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the table being
1153    altered.  (If the constraint is a foreign key then a <literal>ROW
1154    SHARE</literal> lock is also required on the table referenced by the
1155    constraint.)  In addition to improving concurrency, it can be useful to
1156    use <literal>NOT VALID</literal> and <literal>VALIDATE
1157    CONSTRAINT</literal> in cases where the table is known to contain
1158    pre-existing violations.  Once the constraint is in place, no new
1159    violations can be inserted, and the existing problems can be corrected
1160    at leisure until <literal>VALIDATE CONSTRAINT</literal> finally
1161    succeeds.
1162   </para>
1163
1164   <para>
1165    The <literal>DROP COLUMN</literal> form does not physically remove
1166    the column, but simply makes it invisible to SQL operations.  Subsequent
1167    insert and update operations in the table will store a null value for the
1168    column. Thus, dropping a column is quick but it will not immediately
1169    reduce the on-disk size of your table, as the space occupied
1170    by the dropped column is not reclaimed.  The space will be
1171    reclaimed over time as existing rows are updated.  (These statements do
1172    not apply when dropping the system <literal>oid</> column; that is done
1173    with an immediate rewrite.)
1174   </para>
1175
1176   <para>
1177    To force immediate reclamation of space occupied by a dropped column,
1178    you can execute one of the forms of <command>ALTER TABLE</> that
1179    performs a rewrite of the whole table.  This results in reconstructing
1180    each row with the dropped column replaced by a null value.
1181   </para>
1182
1183   <para>
1184    The rewriting forms of <command>ALTER TABLE</> are not MVCC-safe.
1185    After a table rewrite, the table will appear empty to concurrent
1186    transactions, if they are using a snapshot taken before the rewrite
1187    occurred.  See <xref linkend="mvcc-caveats"> for more details.
1188   </para>
1189
1190   <para>
1191    The <literal>USING</literal> option of <literal>SET DATA TYPE</> can actually
1192    specify any expression involving the old values of the row; that is, it
1193    can refer to other columns as well as the one being converted.  This allows
1194    very general conversions to be done with the <literal>SET DATA TYPE</>
1195    syntax.  Because of this flexibility, the <literal>USING</literal>
1196    expression is not applied to the column's default value (if any); the
1197    result might not be a constant expression as required for a default.
1198    This means that when there is no implicit or assignment cast from old to
1199    new type, <literal>SET DATA TYPE</> might fail to convert the default even
1200    though a <literal>USING</literal> clause is supplied.  In such cases,
1201    drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
1202    TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
1203    default.  Similar considerations apply to indexes and constraints involving
1204    the column.
1205   </para>
1206
1207   <para>
1208    If a table has any descendant tables, it is not permitted to add,
1209    rename, or change the type of a column in the parent table without doing
1210    same to the descendants.  This ensures that the descendants always have
1211    columns matching the parent.  Similarly, a constraint cannot be renamed
1212    in the parent without also renaming it in all descendants, so that
1213    constraints also match between the parent and its descendants.
1214    Also, because selecting from the parent also selects from its descendants,
1215    a constraint on the parent cannot be marked valid unless it is also marked
1216    valid for those descendants.  In all of these cases, <command>ALTER TABLE
1217    ONLY</command> will be rejected.
1218   </para>
1219
1220   <para>
1221    A recursive <literal>DROP COLUMN</literal> operation will remove a
1222    descendant table's column only if the descendant does not inherit
1223    that column from any other parents and never had an independent
1224    definition of the column.  A nonrecursive <literal>DROP
1225    COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
1226    COLUMN</command>) never removes any descendant columns, but
1227    instead marks them as independently defined rather than inherited.
1228    A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
1229    partitioned table, because all partitions of a table must have the same
1230    columns as the partitioning root.
1231   </para>
1232
1233   <para>
1234    The actions for identity columns (<literal>ADD
1235    GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
1236    IDENTITY</literal>), as well as the actions
1237    <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
1238    and <literal>TABLESPACE</> never recurse to descendant tables;
1239    that is, they always act as though <literal>ONLY</> were specified.
1240    Adding a constraint recurses only for <literal>CHECK</> constraints
1241    that are not marked <literal>NO INHERIT</>.
1242   </para>
1243
1244   <para>
1245    Changing any part of a system catalog table is not permitted.
1246   </para>
1247
1248   <para>
1249    Refer to <xref linkend="sql-createtable"> for a further description of valid
1250    parameters. <xref linkend="ddl"> has further information on
1251    inheritance.
1252   </para>
1253 </refsect1>
1254
1255 <refsect1>
1256  <title>Examples</title>
1257
1258  <para>
1259   To add a column of type <type>varchar</type> to a table:
1260<programlisting>
1261ALTER TABLE distributors ADD COLUMN address varchar(30);
1262</programlisting>
1263  </para>
1264
1265  <para>
1266   To drop a column from a table:
1267<programlisting>
1268ALTER TABLE distributors DROP COLUMN address RESTRICT;
1269</programlisting>
1270  </para>
1271
1272  <para>
1273   To change the types of two existing columns in one operation:
1274<programlisting>
1275ALTER TABLE distributors
1276    ALTER COLUMN address TYPE varchar(80),
1277    ALTER COLUMN name TYPE varchar(100);
1278</programlisting>
1279  </para>
1280
1281  <para>
1282   To change an integer column containing Unix timestamps to <type>timestamp
1283   with time zone</type> via a <literal>USING</literal> clause:
1284<programlisting>
1285ALTER TABLE foo
1286    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
1287    USING
1288        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
1289</programlisting>
1290  </para>
1291
1292  <para>
1293   The same, when the column has a default expression that won't automatically
1294   cast to the new data type:
1295<programlisting>
1296ALTER TABLE foo
1297    ALTER COLUMN foo_timestamp DROP DEFAULT,
1298    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
1299    USING
1300        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
1301    ALTER COLUMN foo_timestamp SET DEFAULT now();
1302</programlisting>
1303  </para>
1304
1305  <para>
1306   To rename an existing column:
1307<programlisting>
1308ALTER TABLE distributors RENAME COLUMN address TO city;
1309</programlisting>
1310  </para>
1311
1312  <para>
1313   To rename an existing table:
1314<programlisting>
1315ALTER TABLE distributors RENAME TO suppliers;
1316</programlisting>
1317  </para>
1318
1319  <para>
1320   To rename an existing constraint:
1321<programlisting>
1322ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
1323</programlisting>
1324  </para>
1325
1326  <para>
1327   To add a not-null constraint to a column:
1328<programlisting>
1329ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
1330</programlisting>
1331   To remove a not-null constraint from a column:
1332<programlisting>
1333ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
1334</programlisting>
1335  </para>
1336
1337  <para>
1338   To add a check constraint to a table and all its children:
1339<programlisting>
1340ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
1341</programlisting>
1342  </para>
1343
1344  <para>
1345   To add a check constraint only to a table and not to its children:
1346<programlisting>
1347ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
1348</programlisting>
1349   (The check constraint will not be inherited by future children, either.)
1350  </para>
1351
1352  <para>
1353   To remove a check constraint from a table and all its children:
1354<programlisting>
1355ALTER TABLE distributors DROP CONSTRAINT zipchk;
1356</programlisting>
1357  </para>
1358
1359  <para>
1360   To remove a check constraint from one table only:
1361<programlisting>
1362ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
1363</programlisting>
1364   (The check constraint remains in place for any child tables.)
1365  </para>
1366
1367  <para>
1368   To add a foreign key constraint to a table:
1369<programlisting>
1370ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
1371</programlisting>
1372  </para>
1373
1374  <para>
1375   To add a foreign key constraint to a table with the least impact on other work:
1376<programlisting>
1377ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
1378ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
1379</programlisting>
1380  </para>
1381
1382  <para>
1383   To add a (multicolumn) unique constraint to a table:
1384<programlisting>
1385ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
1386</programlisting>
1387  </para>
1388
1389  <para>
1390   To add an automatically named primary key constraint to a table, noting
1391   that a table can only ever have one primary key:
1392<programlisting>
1393ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
1394</programlisting>
1395  </para>
1396
1397  <para>
1398   To move a table to a different tablespace:
1399<programlisting>
1400ALTER TABLE distributors SET TABLESPACE fasttablespace;
1401</programlisting>
1402  </para>
1403
1404  <para>
1405   To move a table to a different schema:
1406<programlisting>
1407ALTER TABLE myschema.distributors SET SCHEMA yourschema;
1408</programlisting>
1409  </para>
1410
1411  <para>
1412   To recreate a primary key constraint, without blocking updates while the
1413   index is rebuilt:
1414<programlisting>
1415CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
1416ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
1417    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
1418</programlisting></para>
1419
1420  <para>
1421   Attach a partition to range partitioned table:
1422<programlisting>
1423ALTER TABLE measurement
1424    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1425</programlisting></para>
1426
1427  <para>
1428   Attach a partition to list partitioned table:
1429<programlisting>
1430ALTER TABLE cities
1431    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
1432</programlisting></para>
1433
1434  <para>
1435   Detach a partition from partitioned table:
1436<programlisting>
1437ALTER TABLE measurement
1438    DETACH PARTITION measurement_y2015m12;
1439</programlisting></para>
1440
1441 </refsect1>
1442
1443 <refsect1>
1444  <title>Compatibility</title>
1445
1446  <para>
1447   The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
1448   <literal>DROP [COLUMN]</>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
1449   <literal>SET DEFAULT</>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
1450   <literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
1451   conform with the SQL standard.  The other forms are
1452   <productname>PostgreSQL</productname> extensions of the SQL standard.
1453   Also, the ability to specify more than one manipulation in a single
1454   <command>ALTER TABLE</> command is an extension.
1455  </para>
1456
1457  <para>
1458   <command>ALTER TABLE DROP COLUMN</> can be used to drop the only
1459   column of a table, leaving a zero-column table.  This is an
1460   extension of SQL, which disallows zero-column tables.
1461  </para>
1462 </refsect1>
1463
1464 <refsect1>
1465  <title>See Also</title>
1466
1467  <simplelist type="inline">
1468   <member><xref linkend="sql-createtable"></member>
1469  </simplelist>
1470 </refsect1>
1471</refentry>
1472