2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.8 1999/07/22 15:09:06 thomas Exp $
6 <refentry id="SQL-ALTERTABLE">
8 <refentrytitle id="sql-altertable-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Modifies table properties
23 <date>1999-07-20</date>
26 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
27 [ * ] ADD [ COLUMN ] <replaceable class="PARAMETER">ER">co</replaceable>BLE> <replaceable
28 class="PARAMETER">type</replaceable>
29 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
30 [ * ] RENAME [ COLUMN ] <replaceable class="PARAMETER">ER">co</replaceable>BLE> TO <replaceable
31 class="PARAMETER">newcolumn</replaceable>
32 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
33 RENAME TO <replaceable class="PARAMETER">newtable</replaceable>
36 <refsect2 id="R2-SQL-ALTERTABLE-1">
38 <date>1998-04-15</date>
46 <term><replaceable class="PARAMETER"> table </replaceable></term>
49 The name of an existing table to alter.
55 <term><replaceable class="PARAMETER"> column </replaceable></term>
58 Name of a new or existing column.
64 <term><replaceable class="PARAMETER"> type </replaceable></term>
67 Type of the new column.
73 <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
76 New name for an existing column.
82 <term><replaceable class="PARAMETER"> newtable </replaceable></term>
85 New name for an existing column.
93 <refsect2 id="R2-SQL-ALTERTABLE-2">
95 <date>1998-04-15</date>
104 <term><computeroutput>
106 </computeroutput></term>
109 Message returned from column or table renaming.
115 <term><computeroutput>
117 </computeroutput></term>
120 Message returned from column addition.
126 <term><computeroutput>
128 </computeroutput></term>
131 Message returned if table or column is not available.
140 <refsect1 id="R1-SQL-ALTERTABLE-1">
142 <date>1998-04-15</date>
148 <command>ALTER TABLE</command> changes the definition of an existing table.
149 The new columns and their types are specified in the same style
150 and with the the same restrictions as in <command>CREATE TABLE</command>.
151 The RENAME clause causes the name of a table or column
152 to change without changing any of the data contained in
153 the affected table. Thus, the table or column will
154 remain of the same type and size after this command is
159 You must own the table in order to change its schema.
162 <refsect2 id="R2-SQL-ALTERTABLE-3">
164 <date>1998-04-15</date>
170 The keyword <literal>COLUMN</literal> is noise and can be omitted.
174 <quote>*</quote> following a name of a table indicates that the statement
175 should be run over that table and all tables below it in the
176 inheritance hierarchy;
177 by default, the attribute will not be added to or renamed in any of the subclasses.
179 This should always be done when adding or modifying an attribute in a
180 superclass. If it is not, queries on the inheritance hierarchy
184 SELECT <replaceable>NewColumn</replaceable> FROM <replaceable>SuperClass</replaceable>*
187 will not work because the subclasses will be missing an attribute
188 found in the superclass.
192 For efficiency reasons, default values for added attributes are
193 not placed in existing instances of a class.
194 That is, existing instances will have NULL values in the new
195 attributes. If non-NULL values are desired, a subsequent
196 <command>UPDATE</command> query
197 (<xref linkend="sql-update-title" endterm="sql-update-title">)
202 You must own the class in order to change its schema.
203 Renaming any part of the schema of a system
204 catalog is not permitted.
205 The <citetitle>PostgreSQL User's Guide</citetitle> has further
206 information on inheritance.
210 Refer to <command>CREATE TABLE</command> for a further description
216 <refsect1 id="R1-SQL-ALTERTABLE-2">
221 To add a column of type VARCHAR to a table:
223 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
228 To rename an existing column:
230 ALTER TABLE distributors RENAME COLUMN address TO city;
235 To rename an existing table:
237 ALTER TABLE distributors RENAME TO suppliers;
242 <refsect1 id="R1-SQL-ALTERTABLE-3">
247 <refsect2 id="R2-SQL-ALTERTABLE-4">
249 <date>1998-04-15</date>
255 <command>ALTER TABLE/RENAME</command>
256 is a <productname>Postgres</productname> language extension.
260 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
261 statement which are not yet directly supported by
262 <productname>Postgres</productname>:
269 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> ALTER [
270 COLUMN ] <replaceable class="PARAMETER">column</replaceable>
271 SET DEFAULT <replaceable class="PARAMETER">default</replaceable>
272 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> ALTER [
273 COLUMN ] <replaceable class="PARAMETER">column</replaceable>
274 ADD [ CONSTRAINT <replaceable class="PARAMETER">>constrain</replaceable>> ] <replaceable
275 class="PARAMETER">table-constraint</replaceable>
280 Puts the default value or constraint specified into the
281 definition of column in the table.
282 See <command>CREATE TABLE</command> for the
283 syntax of the default and table-constraint clauses.
284 If a default clause already exists, it will be replaced by
285 the new definition. If any constraints on this column already
286 exist, they will be retained using a boolean AND with the new
291 Currently, to set new default constraints on an existing column
292 the table must be recreated and reloaded:
295 CREATE TABLE temp AS SELECT * FROM distributors;
296 DROP TABLE distributors;
297 CREATE TABLE distributors (
298 did DECIMAL(3) DEFAULT 1,
299 name VARCHAR(40) NOT NULL,
302 INSERT INTO distributors SELECT * FROM temp;
312 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
313 DROP DEFAULT <replaceable class="PARAMETER">default</replaceable>
314 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
315 DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
320 Removes the default value specified by default or the rule
321 specified by constraint from the definition of a table.
322 If RESTRICT is specified only a constraint with no dependent
323 constraints can be destroyed.
324 If CASCADE is specified, Any constraints that are dependent on
325 this constraint are also dropped.
329 Currently, to remove a default value or constraints on an
330 existing column the table must be recreated and reloaded:
333 CREATE TABLE temp AS SELECT * FROM distributors;
334 DROP TABLE distributors;
335 CREATE TABLE distributors AS SELECT * FROM temp;
345 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
346 DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
351 Removes a column from a table.
352 If RESTRICT is specified only a column with no dependent
353 objects can be destroyed.
354 If CASCADE is specified, all objects that are dependent on
355 this column are also dropped.
359 Currently, to remove an existing column the table must be
360 recreated and reloaded:
363 CREATE TABLE temp AS SELECT did, city FROM distributors;
364 DROP TABLE distributors;
365 CREATE TABLE distributors (
366 did DECIMAL(3) DEFAULT 1,
367 name VARCHAR(40) NOT NULL,
369 INSERT INTO distributors SELECT * FROM temp;
380 <!-- Keep this comment at the end of the file
385 sgml-minimize-attributes:nil
386 sgml-always-quote-attributes:t
389 sgml-parent-document:nil
390 sgml-default-dtd-file:"../reference.ced"
391 sgml-exposed-tags:nil
392 sgml-local-catalogs:"/usr/lib/sgml/catalog"
393 sgml-local-ecat-files:nil