From 38bb77a5d15aa022248488bc8c0147139ce120a9 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 2 Aug 2002 18:15:10 +0000 Subject: 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. --- doc/src/sgml/catalogs.sgml | 13 +++++- doc/src/sgml/ref/alter_table.sgml | 92 +++++++++++++++++++++++++-------------- doc/src/sgml/ref/copy.sgml | 29 +++++------- doc/src/sgml/release.sgml | 4 +- 4 files changed, 86 insertions(+), 52 deletions(-) (limited to 'doc/src') 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 @@ @@ -810,6 +810,17 @@ + + attisdropped + bool + + + 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. + + + 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 @@ @@ -23,6 +23,8 @@ PostgreSQL documentation ALTER TABLE [ ONLY ] table [ * ] ADD [ COLUMN ] column type [ column_constraint [ ... ] ] +ALTER TABLE [ ONLY ] table [ * ] + DROP [ COLUMN ] column [ RESTRICT | CASCADE ] ALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } ALTER TABLE [ ONLY ] table [ * ] @@ -126,6 +128,26 @@ ALTER TABLE table + + CASCADE + + + Automatically drop objects that depend on the dropped column + or constraint (for example, views referencing the column). + + + + + + RESTRICT + + + Refuse to drop the column or constraint if there are any dependent + objects. This is the default behavior. + + + + @@ -186,6 +208,19 @@ ALTER TABLE table + + DROP COLUMN + + + 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 CASCADE if + anything outside the table depends on the column --- for example, + foreign key references, views, etc. + + + + SET/DROP DEFAULT @@ -317,6 +352,22 @@ ALTER TABLE table form after you've entered non-null values for the column in all rows. + + The DROP COLUMN 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 UPDATE of all rows + and then vacuum, as in: + +UPDATE table SET col = col; +VACUUM FULL table; + + + Changing any part of the schema of a system catalog is not permitted. @@ -342,6 +393,13 @@ ALTER TABLE distributors ADD COLUMN address VARCHAR(30); + + To drop a column from a table: + +ALTER TABLE distributors DROP COLUMN address RESTRICT; + + + To rename an existing column: @@ -420,38 +478,6 @@ ALTER TABLE distributors ADD PRIMARY KEY (dist_id); The ALTER COLUMN form is in full compliance. - - SQL92 specifies some additional capabilities for ALTER TABLE - statement which are not yet directly supported by PostgreSQL: - - - - - -ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE } - - - - - Removes a column from a table. - Currently, to remove an existing column the table must be - recreated and reloaded: - -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; - - - - - - - The clauses to rename tables, columns, indexes, and sequences are PostgreSQL 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 @@ @@ -21,16 +21,14 @@ PostgreSQL documentation 1999-12-11 -COPY table - [ ( column [, ...] ) ] +COPY table [ ( column [, ...] ) ] FROM { 'filename' | stdin } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] ] -COPY table - [ ( column [, ...] ) ] +COPY table [ ( column [, ...] ) ] TO { 'filename' | stdout } [ [ WITH ] [ BINARY ] @@ -201,10 +199,10 @@ ERROR: reason If a list of columns is specified, COPY 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, - COPY FROM 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, + COPY FROM will insert the default values for + those columns. @@ -266,8 +264,8 @@ ERROR: reason - COPY FROM will invoke any triggers or check - constraints. However, it will not invoke rules. + COPY FROM will invoke any triggers and check + constraints on the destination table. However, it will not invoke rules. @@ -330,12 +328,9 @@ ERROR: reason 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 COPY - FROM 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, COPY FROM 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. + COPY FROM will raise an error if any line of the + input file contains more or fewer columns than are expected. 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 @@ @@ -24,6 +24,8 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. -->