2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.16 2000/12/20 03:19:24 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>
39 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
40 OWNER TO <replaceable class="PARAMETER">new owner</replaceable>
43 <refsect2 id="R2-SQL-ALTERTABLE-1">
45 <date>1998-04-15</date>
53 <term><replaceable class="PARAMETER"> table </replaceable></term>
56 The name of an existing table to alter.
62 <term><replaceable class="PARAMETER"> column </replaceable></term>
65 Name of a new or existing column.
71 <term><replaceable class="PARAMETER"> type </replaceable></term>
74 Type of the new column.
80 <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
83 New name for an existing column.
89 <term><replaceable class="PARAMETER"> newtable </replaceable></term>
92 New name for the table.
98 <term><replaceable class="PARAMETER"> table constraint definition </replaceable></term>
101 New table constraint for the table
107 <term><replaceable class="PARAMETER">New user </replaceable></term>
110 The userid of the new owner of the table.
119 <refsect2 id="R2-SQL-ALTERTABLE-2">
121 <date>1998-04-15</date>
130 <term><computeroutput>ALTER</computeroutput></term>
133 Message returned from column or table renaming.
139 <term><computeroutput>ERROR</computeroutput></term>
142 Message returned if table or column is not available.
151 <refsect1 id="R1-SQL-ALTERTABLE-1">
153 <date>1998-04-15</date>
159 <command>ALTER TABLE</command> changes the definition of an existing table.
160 The <literal>ADD COLUMN</literal> form adds a new column to the table
161 using the same syntax as <xref linkend="SQL-CREATETABLE"
162 endterm="SQL-CREATETABLE-title">. The <literal>ALTER COLUMN</literal> form
163 allows you to set or remove the default for the column. Note that defaults
164 only apply to newly inserted rows.
165 The <literal>RENAME</literal> clause causes the name of a table or column
166 to change without changing any of the data contained in
167 the affected table. Thus, the table or column will
168 remain of the same type and size after this command is
170 The ADD <replaceable class="PARAMETER">table constraint definition</replaceable> clause
171 adds a new constraint to the table using the same syntax as <xref
172 linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">.
173 The OWNER clause chnages the owner of the table to the user <replaceable class="PARAMETER">
174 new user</replaceable>.
178 You must own the table in order to change its schema.
181 <refsect2 id="R2-SQL-ALTERTABLE-3">
183 <date>1998-04-15</date>
189 The keyword <literal>COLUMN</literal> is noise and can be omitted.
193 In the current implementation, default and constraint clauses for the
194 new column will be ignored. You can use the <literal>SET DEFAULT</literal>
195 form of <command>ALTER TABLE</command> to set the default later.
196 (You will also have to update the already existing rows to the
197 new default value, using <xref linkend="sql-update-title"
198 endterm="sql-update-title">.)
202 In the current implementation, only FOREIGN KEY constraints can
203 be added to a table. To create or remove a unique constraint, create
204 a unique index (see <xref linkend="SQL-CREATEINDEX"
205 endterm="SQL-CREATEINDEX-title">). To add check constraints
206 you need to recreate and reload the table, using other
207 parameters to the <xref linkend="SQL-CREATETABLE"
208 endterm="SQL-CREATETABLE-title"> command.
212 You must own the class in order to change its schema.
213 Renaming any part of the schema of a system
214 catalog is not permitted.
215 The <citetitle>PostgreSQL User's Guide</citetitle> has further
216 information on inheritance.
220 Refer to <command>CREATE TABLE</command> for a further description
226 <refsect1 id="R1-SQL-ALTERTABLE-2">
231 To add a column of type VARCHAR to a table:
233 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
238 To rename an existing column:
240 ALTER TABLE distributors RENAME COLUMN address TO city;
245 To rename an existing table:
247 ALTER TABLE distributors RENAME TO suppliers;
252 To add a foreign key constraint to a table:
254 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL
259 <refsect1 id="R1-SQL-ALTERTABLE-3">
264 <refsect2 id="R2-SQL-ALTERTABLE-4">
266 <date>1998-04-15</date>
270 The <literal>ADD COLUMN</literal> form is compliant with the exception that
271 it does not support defaults and constraints, as explained above.
272 The <literal>ALTER COLUMN</literal> form is in full compliance.
276 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
277 statement which are not yet directly supported by <productname>Postgres</productname>:
283 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
288 Removes a table constraint (such as a check constraint,
289 unique constraint, or foreign key constraint). To
290 remove a unique constraint, drop a unique index.
291 To remove other kinds of constraints you need to recreate
292 and reload the table, using other parameters to the
293 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">
297 For example, to drop any constraints on a table <literal>distributors</literal>:
299 CREATE TABLE temp AS SELECT * FROM distributors;
300 DROP TABLE distributors;
301 CREATE TABLE distributors AS SELECT * FROM temp;
311 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
316 Removes a column from a table.
317 Currently, to remove an existing column the table must be
318 recreated and reloaded:
320 CREATE TABLE temp AS SELECT did, city FROM distributors;
321 DROP TABLE distributors;
322 CREATE TABLE distributors (
323 did DECIMAL(3) DEFAULT 1,
324 name VARCHAR(40) NOT NULL
326 INSERT INTO distributors SELECT * FROM temp;
336 The clauses to rename columns and tables are <productname>Postgres</productname>
337 extensions from SQL92.
344 <!-- Keep this comment at the end of the file
349 sgml-minimize-attributes:nil
350 sgml-always-quote-attributes:t
353 sgml-parent-document:nil
354 sgml-default-dtd-file:"../reference.ced"
355 sgml-exposed-tags:nil
356 sgml-local-catalogs:"/usr/lib/sgml/catalog"
357 sgml-local-ecat-files:nil