2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.14 2000/07/22 02:39:10 momjian 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 [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
27 ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable
28 class="PARAMETER">type</replaceable>
29 ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
30 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
31 class="PARAMETER">value</replaceable> | DROP DEFAULT }
32 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
33 RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
34 class="PARAMETER">newcolumn</replaceable>
35 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
36 RENAME TO <replaceable class="PARAMETER">newtable</replaceable>
37 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
38 ADD <replaceable class="PARAMETER">table constraint definition</replaceable>
41 <refsect2 id="R2-SQL-ALTERTABLE-1">
43 <date>1998-04-15</date>
51 <term><replaceable class="PARAMETER"> table </replaceable></term>
54 The name of an existing table to alter.
60 <term><replaceable class="PARAMETER"> column </replaceable></term>
63 Name of a new or existing column.
69 <term><replaceable class="PARAMETER"> type </replaceable></term>
72 Type of the new column.
78 <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
81 New name for an existing column.
87 <term><replaceable class="PARAMETER"> newtable </replaceable></term>
90 New name for the table.
96 <term><replaceable class="PARAMETER"> table constraint definition </replaceable></term>
99 New table constraint for the table
107 <refsect2 id="R2-SQL-ALTERTABLE-2">
109 <date>1998-04-15</date>
118 <term><computeroutput>ALTER</computeroutput></term>
121 Message returned from column or table renaming.
127 <term><computeroutput>ERROR</computeroutput></term>
130 Message returned if table or column is not available.
139 <refsect1 id="R1-SQL-ALTERTABLE-1">
141 <date>1998-04-15</date>
147 <command>ALTER TABLE</command> changes the definition of an existing table.
148 The <literal>ADD COLUMN</literal> form adds a new column to the table
149 using the same syntax as <xref linkend="SQL-CREATETABLE"
150 endterm="SQL-CREATETABLE-title">. The <literal>ALTER COLUMN</literal> form
151 allows you to set or remove the default for the column. Note that defaults
152 only apply to newly inserted rows.
153 The <literal>RENAME</literal> clause causes the name of a table or column
154 to change without changing any of the data contained in
155 the affected table. Thus, the table or column will
156 remain of the same type and size after this command is
158 The ADD <replaceable class="PARAMETER">table constraint definition</replaceable> clause
159 adds a new constraint to the table using the same syntax as <xref
160 linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">.
164 You must own the table in order to change its schema.
167 <refsect2 id="R2-SQL-ALTERTABLE-3">
169 <date>1998-04-15</date>
175 The keyword <literal>COLUMN</literal> is noise and can be omitted.
179 In the current implementation, default and constraint clauses for the
180 new column will be ignored. You can use the <literal>SET DEFAULT</literal>
181 form of <command>ALTER TABLE</command> to set the default later.
182 (You will also have to update the already existing rows to the
183 new default value, using <xref linkend="sql-update-title"
184 endterm="sql-update-title">.)
188 In the current implementation, only FOREIGN KEY constraints can
189 be added to a table. To create or remove a unique constraint, create
190 a unique index (see <xref linkend="SQL-CREATEINDEX"
191 endterm="SQL-CREATEINDEX-title">). To add check constraints
192 you need to recreate and reload the table, using other
193 parameters to the <xref linkend="SQL-CREATETABLE"
194 endterm="SQL-CREATETABLE-title"> command.
198 You must own the class in order to change its schema.
199 Renaming any part of the schema of a system
200 catalog is not permitted.
201 The <citetitle>PostgreSQL User's Guide</citetitle> has further
202 information on inheritance.
206 Refer to <command>CREATE TABLE</command> for a further description
212 <refsect1 id="R1-SQL-ALTERTABLE-2">
217 To add a column of type VARCHAR to a table:
219 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
224 To rename an existing column:
226 ALTER TABLE distributors RENAME COLUMN address TO city;
231 To rename an existing table:
233 ALTER TABLE distributors RENAME TO suppliers;
238 To add a foreign key constraint to a table:
240 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL
245 <refsect1 id="R1-SQL-ALTERTABLE-3">
250 <refsect2 id="R2-SQL-ALTERTABLE-4">
252 <date>1998-04-15</date>
256 The <literal>ADD COLUMN</literal> form is compliant with the exception that
257 it does not support defaults and constraints, as explained above.
258 The <literal>ALTER COLUMN</literal> form is in full compliance.
262 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
263 statement which are not yet directly supported by <productname>Postgres</productname>:
269 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
274 Removes a table constraint (such as a check constraint,
275 unique constraint, or foreign key constraint). To
276 remove a unique constraint, drop a unique index,
277 To remove other kinds of constraints you need to recreate
278 and reload the table, using other parameters to the
279 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">
283 For example, to drop any constraints on a table <literal>distributors</literal>:
285 CREATE TABLE temp AS SELECT * FROM distributors;
286 DROP TABLE distributors;
287 CREATE TABLE distributors AS SELECT * FROM temp;
297 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
302 Removes a column from a table.
303 Currently, to remove an existing column the table must be
304 recreated and reloaded:
306 CREATE TABLE temp AS SELECT did, city FROM distributors;
307 DROP TABLE distributors;
308 CREATE TABLE distributors (
309 did DECIMAL(3) DEFAULT 1,
310 name VARCHAR(40) NOT NULL
312 INSERT INTO distributors SELECT * FROM temp;
322 The clauses to rename columns and tables are <productname>Postgres</productname>
323 extensions from SQL92.
330 <!-- Keep this comment at the end of the file
335 sgml-minimize-attributes:nil
336 sgml-always-quote-attributes:t
339 sgml-parent-document:nil
340 sgml-default-dtd-file:"../reference.ced"
341 sgml-exposed-tags:nil
342 sgml-local-catalogs:"/usr/lib/sgml/catalog"
343 sgml-local-ecat-files:nil