ALTER TABLE DROP COLUMN works. Patch by Christopher Kings-Lynne,

code review by Tom Lane.  Remaining issues: functions that take or
return tuple types are likely to break if one drops (or adds!)
a column in the table defining the type.  Need to think about what
to do here.

Along the way: some code review for recent COPY changes; mark system
columns attnotnull = true where appropriate, per discussion a month ago.

4 files changed, 86 insertions, 52 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 8bf4bf816f0..573815388fe 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.50 2002/07/31 17:19:49 tgl Exp $
+ $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.51 2002/08/02 18:15:04 tgl Exp $
  -->
 
 <chapter id="catalogs">
@@ -810,6 +810,17 @@
       </entry>
      </row>
 
+     <row>
+      <entry>attisdropped</entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>
+       This column has been dropped and is no longer valid.  A dropped
+       column is still physically present in the table, but is
+       ignored by the parser and so cannot be accessed via SQL.
+      </entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 11da9fdf60c..0bfe88cf54d 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.47 2002/07/31 17:19:50 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.48 2002/08/02 18:15:04 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -24,6 +24,8 @@ PostgreSQL documentation
 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
     ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
+    DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
+ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable class="PARAMETER">value</replaceable> | DROP DEFAULT }
 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
@@ -126,6 +128,26 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term>CASCADE</term>
+      <listitem>
+       <para>
+        Automatically drop objects that depend on the dropped column
+	or constraint (for example, views referencing the column).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>RESTRICT</term>
+      <listitem>
+       <para>
+        Refuse to drop the column or constraint if there are any dependent
+	objects. This is the default behavior.
+       </para>
+      </listitem>
+     </varlistentry>
+
     </variablelist>
    </para>
   </refsect2>
@@ -187,6 +209,19 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    </varlistentry>
 
    <varlistentry>
+    <term>DROP COLUMN</term>
+    <listitem>
+     <para>
+      This form drops a column from a table.  Note that indexes and
+      table constraints involving the column will be automatically
+      dropped as well.  You will need to say <literal>CASCADE</> if
+      anything outside the table depends on the column --- for example,
+      foreign key references, views, etc.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term>SET/DROP DEFAULT</term>
     <listitem>
      <para>
@@ -318,6 +353,22 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    </para>
 
    <para>
+    The <literal>DROP COLUMN</literal> command does not physically remove
+    the column, but simply makes it invisible to SQL operations.  Subsequent
+    inserts and updates of the table will store a NULL 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.
+    To reclaim the space at once, do a dummy <command>UPDATE</> of all rows
+    and then vacuum, as in:
+     <programlisting>
+UPDATE table SET col = col;
+VACUUM FULL table;
+    </programlisting>
+   </para>
+
+   <para>
     Changing any  part  of  the schema of a system
     catalog is not permitted.
    </para>
@@ -343,6 +394,13 @@ ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
   </para>
 
   <para>
+   To drop a column from a table:
+   <programlisting>
+ALTER TABLE distributors DROP COLUMN address RESTRICT;
+   </programlisting>
+  </para>
+
+  <para>
    To rename an existing column:
    <programlisting>
 ALTER TABLE distributors RENAME COLUMN address TO city;
@@ -420,38 +478,6 @@ ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
      The <literal>ALTER COLUMN</literal> form is in full compliance.
     </para>
 
-    <para>
-     SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
-     statement which are not yet directly supported by <productname>PostgreSQL</productname>:
-
-    <variablelist>
-    <varlistentry>
-     <term>
-      <synopsis>
-ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
-      </synopsis>
-     </term>
-     <listitem>
-      <para>
-       Removes a column from a table.
-       Currently, to remove an existing column the table must be
-       recreated and reloaded:
-       <programlisting>
-CREATE TABLE temp AS SELECT did, city FROM distributors;    
-DROP TABLE distributors;
-CREATE TABLE distributors (
-    did      DECIMAL(3)  DEFAULT 1,
-    name     VARCHAR(40) NOT NULL
-);
-INSERT INTO distributors SELECT * FROM temp;
-DROP TABLE temp;
-       </programlisting>
-      </para>
-     </listitem>
-    </varlistentry>
-   </variablelist>
-   </para>
-
    <para>
     The clauses to rename tables, columns, indexes, and sequences are
     <productname>PostgreSQL</productname> extensions from SQL92.
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 484b0a62087..d09b6706dd0 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.34 2002/07/30 16:55:05 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v 1.35 2002/08/02 18:15:04 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -21,16 +21,14 @@ PostgreSQL documentation
    <date>1999-12-11</date>
   </refsynopsisdivinfo>
   <synopsis>
-COPY <replaceable class="parameter">table</replaceable> 
-    [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
+COPY <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
     FROM { '<replaceable class="parameter">filename</replaceable>' | <filename>stdin</filename> }
     [ [ WITH ] 
           [ BINARY ] 
           [ OIDS ]
           [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ]
           [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ] ]
-COPY <replaceable class="parameter">table</replaceable>
-    [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
+COPY <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
     TO { '<replaceable class="parameter">filename</replaceable>' | <filename>stdout</filename> }
     [ [ WITH ] 
           [ BINARY ]
@@ -201,10 +199,10 @@ ERROR: <replaceable>reason</replaceable>
 
   <para>
    If a list of columns is specified, <command>COPY</command> will
-   only copy the data in the specified columns to or from the table.
-   If there are any columns in the table that are not in the table,
-   <command>COPY FROM</command> will insert the default value for
-   that column.
+   only copy the data in the specified columns to or from the file.
+   If there are any columns in the table that are not in the file,
+   <command>COPY FROM</command> will insert the default values for
+   those columns.
   </para>
 
   <para>
@@ -266,8 +264,8 @@ ERROR: <replaceable>reason</replaceable>
    </para>
 
    <para>
-    <command>COPY FROM</command> will invoke any triggers or check
-	constraints. However, it will not invoke rules.
+    <command>COPY FROM</command> will invoke any triggers and check
+    constraints on the destination table. However, it will not invoke rules.
    </para>
 
    <para>
@@ -330,12 +328,9 @@ ERROR: <replaceable>reason</replaceable>
     The attribute values themselves are strings generated by the
     output function, or acceptable to the input function, of each
     attribute's data type.  The specified null-value string is used in
-    place of attributes that are NULL.  When using <command>COPY
-	FROM</command> without a column list, each row of the input file
-	must contain data for each attribute in the table: no missing data
-	is allowed.  Similarly, <command>COPY FROM</command> will raise
-	an error if it encounters any data in the input file that would
-	not be inserted into the table: extra data is not allowed.
+    place of attributes that are NULL.
+    <command>COPY FROM</command> will raise an error if any line of the
+    input file contains more or fewer columns than are expected.
    </para>
    <para>
     If OIDS is specified, the OID is read or written as the first column,
diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml
index af82154d8c7..0da3d9ef298 100644
--- a/doc/src/sgml/release.sgml
+++ b/doc/src/sgml/release.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.144 2002/07/29 22:14:10 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.145 2002/08/02 18:15:04 tgl Exp $
 -->
 
 <appendix id="release">
@@ -24,6 +24,8 @@ CDATA means the content is "SGML-free", so you can write without
 worries about funny characters.
 -->
 <literallayout><![CDATA[
+COPY accepts a list of columns to copy
+ALTER TABLE DROP COLUMN
 CREATE OPERATOR CLASS/DROP OPERATOR CLASS
 CREATE CAST/DROP CAST
 Sequences created by SERIAL column definitions now auto-drop with the column