Home Explore Blog CI



postgresql

25th chunk of `doc/src/sgml/ref/alter_table.sgml`
7fb319349a20fd3e1b66c334deccb136dccaf18f3b4440c60000000100000fa3
 CONSTRAINT</literal> in cases where the table is known to contain
    pre-existing violations.  Once the constraint is in place, no new
    violations can be inserted, and the existing problems can be corrected
    at leisure until <literal>VALIDATE CONSTRAINT</literal> finally
    succeeds.
   </para>

   <para>
    The <literal>DROP COLUMN</literal> form does not physically remove
    the column, but simply makes it invisible to SQL operations.  Subsequent
    insert and update operations in the table will store a null value for the
    column. Thus, dropping a column is quick but it will not immediately
    reduce the on-disk size of your table, as the space occupied
    by the dropped column is not reclaimed.  The space will be
    reclaimed over time as existing rows are updated.
   </para>

   <para>
    To force immediate reclamation of space occupied by a dropped column,
    you can execute one of the forms of <command>ALTER TABLE</command> that
    performs a rewrite of the whole table.  This results in reconstructing
    each row with the dropped column replaced by a null value.
   </para>

   <para>
    The rewriting forms of <command>ALTER TABLE</command> are not MVCC-safe.
    After a table rewrite, the table will appear empty to concurrent
    transactions, if they are using a snapshot taken before the rewrite
    occurred.  See <xref linkend="mvcc-caveats"/> for more details.
   </para>

   <para>
    The <literal>USING</literal> option of <literal>SET DATA TYPE</literal> can actually
    specify any expression involving the old values of the row; that is, it
    can refer to other columns as well as the one being converted.  This allows
    very general conversions to be done with the <literal>SET DATA TYPE</literal>
    syntax.  Because of this flexibility, the <literal>USING</literal>
    expression is not applied to the column's default value (if any); the
    result might not be a constant expression as required for a default.
    This means that when there is no implicit or assignment cast from old to
    new type, <literal>SET DATA TYPE</literal> might fail to convert the default even
    though a <literal>USING</literal> clause is supplied.  In such cases,
    drop the default with <literal>DROP DEFAULT</literal>, perform the <literal>ALTER
    TYPE</literal>, and then use <literal>SET DEFAULT</literal> to add a suitable new
    default.  Similar considerations apply to indexes and constraints involving
    the column.
   </para>

   <para>
    If a table has any descendant tables, it is not permitted to add,
    rename, or change the type of a column in the parent table without doing
    the same to the descendants.  This ensures that the descendants always
    have columns matching the parent.  Similarly, a <literal>CHECK</literal>
    constraint cannot be renamed in the parent without also renaming it in
    all descendants, so that <literal>CHECK</literal> constraints also match
    between the parent and its descendants.  (That restriction does not apply
    to index-based constraints, however.)
    Also, because selecting from the parent also selects from its descendants,
    a constraint on the parent cannot be marked valid unless it is also marked
    valid for those descendants.  In all of these cases, <command>ALTER TABLE
    ONLY</command> will be rejected.
   </para>

   <para>
    A recursive <literal>DROP COLUMN</literal> operation will remove a
    descendant table's column only if the descendant does not inherit
    that column from any other parents and never had an independent
    definition of the column.  A nonrecursive <literal>DROP
    COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
    COLUMN</command>) never removes any descendant columns, but
    instead marks them as independently defined rather than inherited.
    A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
    partitioned table, because all partitions of a table must have the same
    columns

Title: ALTER TABLE: Constraints, Dropping Columns, and Inheritance
Summary
Continuing the discussion of ALTER TABLE, this section covers: using NOT VALID and VALIDATE CONSTRAINT to manage existing constraint violations; DROP COLUMN's behavior of marking columns invisible and the need for a table rewrite for immediate space reclamation; ALTER TABLE rewrites not being MVCC-safe; the flexibility of the USING option in SET DATA TYPE; the constraints on altering columns and CHECK constraints in parent tables with descendants; and the behavior of recursive and non-recursive DROP COLUMN operations in inherited tables.