2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.23 2001/05/07 00:43:15 tgl 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 [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
33 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
34 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
35 RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
36 class="PARAMETER">newcolumn</replaceable>
37 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
38 RENAME TO <replaceable class="PARAMETER">newtable</replaceable>
39 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
40 ADD <replaceable class="PARAMETER">table constraint definition</replaceable>
41 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
42 OWNER TO <replaceable class="PARAMETER">new owner</replaceable>
45 <refsect2 id="R2-SQL-ALTERTABLE-1">
47 <date>1998-04-15</date>
55 <term><replaceable class="PARAMETER"> table </replaceable></term>
58 The name of an existing table to alter.
64 <term><replaceable class="PARAMETER"> column </replaceable></term>
67 Name of a new or existing column.
73 <term><replaceable class="PARAMETER"> type </replaceable></term>
76 Type of the new column.
82 <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
85 New name for an existing column.
91 <term><replaceable class="PARAMETER"> newtable </replaceable></term>
94 New name for the table.
100 <term><replaceable class="PARAMETER"> table constraint definition </replaceable></term>
103 New table constraint for the table
109 <term><replaceable class="PARAMETER">New user </replaceable></term>
112 The user name of the new owner of the table.
121 <refsect2 id="R2-SQL-ALTERTABLE-2">
123 <date>1998-04-15</date>
132 <term><computeroutput>ALTER</computeroutput></term>
135 Message returned from column or table renaming.
141 <term><computeroutput>ERROR</computeroutput></term>
144 Message returned if table or column is not available.
153 <refsect1 id="R1-SQL-ALTERTABLE-1">
155 <date>1998-04-15</date>
161 <command>ALTER TABLE</command> changes the definition of an existing table.
162 The <literal>ADD COLUMN</literal> form adds a new column to the table
163 using the same syntax as <xref linkend="SQL-CREATETABLE"
164 endterm="SQL-CREATETABLE-title">.
165 The <literal>ALTER COLUMN SET/DROP DEFAULT</literal> forms
166 allow you to set or remove the default for the column. Note that defaults
167 only apply to subsequent <command>INSERT</command> commands; they do not
168 cause rows already in the table to change.
169 The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to
170 set the statistics-gathering target for subsequent
171 <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
172 The <literal>RENAME</literal> clause causes the name of a table or column
173 to change without changing any of the data contained in
174 the affected table. Thus, the table or column will
175 remain of the same type and size after this command is
177 The ADD <replaceable class="PARAMETER">table constraint definition</replaceable> clause
178 adds a new constraint to the table using the same syntax as <xref
179 linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">.
180 The OWNER clause changes the owner of the table to the user <replaceable class="PARAMETER">
181 new user</replaceable>.
185 You must own the table in order to change its schema.
188 <refsect2 id="R2-SQL-ALTERTABLE-3">
190 <date>1998-04-15</date>
196 The keyword <literal>COLUMN</literal> is noise and can be omitted.
200 In the current implementation of <literal>ADD COLUMN</literal>,
201 default and constraint clauses for the
202 new column will be ignored. You can use the <literal>SET DEFAULT</literal>
203 form of <command>ALTER TABLE</command> to set the default later.
204 (You may also want to update the already existing rows to the
205 new default value, using <xref linkend="sql-update"
206 endterm="sql-update-title">.)
210 In the current implementation, only FOREIGN KEY constraints can
211 be added to a table. To create or remove a unique constraint, create
212 a unique index (see <xref linkend="SQL-CREATEINDEX"
213 endterm="SQL-CREATEINDEX-title">). To add check constraints
214 you need to recreate and reload the table, using other
215 parameters to the <xref linkend="SQL-CREATETABLE"
216 endterm="SQL-CREATETABLE-title"> command.
220 You must own the table in order to change it.
221 Changing any part of the schema of a system
222 catalog is not permitted.
223 The <citetitle>PostgreSQL User's Guide</citetitle> has further
224 information on inheritance.
228 Refer to <command>CREATE TABLE</command> for a further description
234 <refsect1 id="R1-SQL-ALTERTABLE-2">
239 To add a column of type VARCHAR to a table:
241 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
246 To rename an existing column:
248 ALTER TABLE distributors RENAME COLUMN address TO city;
253 To rename an existing table:
255 ALTER TABLE distributors RENAME TO suppliers;
260 To add a foreign key constraint to a table:
262 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL
267 <refsect1 id="R1-SQL-ALTERTABLE-3">
272 <refsect2 id="R2-SQL-ALTERTABLE-4">
274 <date>1998-04-15</date>
278 The <literal>ADD COLUMN</literal> form is compliant with the exception that
279 it does not support defaults and constraints, as explained above.
280 The <literal>ALTER COLUMN</literal> form is in full compliance.
284 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
285 statement which are not yet directly supported by <productname>Postgres</productname>:
291 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
296 Removes a table constraint (such as a check constraint,
297 unique constraint, or foreign key constraint). To
298 remove a unique constraint, drop a unique index.
299 To remove other kinds of constraints you need to recreate
300 and reload the table, using other parameters to the
301 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">
305 For example, to drop any constraints on a table <literal>distributors</literal>:
307 CREATE TABLE temp AS SELECT * FROM distributors;
308 DROP TABLE distributors;
309 CREATE TABLE distributors AS SELECT * FROM temp;
319 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
324 Removes a column from a table.
325 Currently, to remove an existing column the table must be
326 recreated and reloaded:
328 CREATE TABLE temp AS SELECT did, city FROM distributors;
329 DROP TABLE distributors;
330 CREATE TABLE distributors (
331 did DECIMAL(3) DEFAULT 1,
332 name VARCHAR(40) NOT NULL
334 INSERT INTO distributors SELECT * FROM temp;
344 The clauses to rename columns and tables are <productname>Postgres</productname>
345 extensions from SQL92.
352 <!-- Keep this comment at the end of the file
357 sgml-minimize-attributes:nil
358 sgml-always-quote-attributes:t
361 sgml-parent-document:nil
362 sgml-default-dtd-file:"../reference.ced"
363 sgml-exposed-tags:nil
364 sgml-local-catalogs:"/usr/lib/sgml/catalog"
365 sgml-local-ecat-files:nil