diff options
Diffstat (limited to 'doc/src/sgml/ref')
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 92 | ||||
| -rw-r--r-- | doc/src/sgml/ref/copy.sgml | 29 |
2 files changed, 71 insertions, 50 deletions
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, |