2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.29 2001/10/12 00:07:14 tgl Exp $
6 <refentry id="SQL-ALTERTABLE">
8 <refentrytitle id="sql-altertable-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 change the definition of a table
23 <date>1999-07-20</date>
26 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
27 ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
28 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
29 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
30 class="PARAMETER">value</replaceable> | DROP DEFAULT }
31 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
32 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
33 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
34 RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
35 class="PARAMETER">newcolumn</replaceable>
36 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
37 RENAME TO <replaceable class="PARAMETER">newtable</replaceable>
38 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
39 ADD <replaceable class="PARAMETER">table constraint definition</replaceable>
40 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
41 DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
42 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
43 OWNER TO <replaceable class="PARAMETER">new owner</replaceable>
46 <refsect2 id="R2-SQL-ALTERTABLE-1">
48 <date>1998-04-15</date>
56 <term><replaceable class="PARAMETER"> table </replaceable></term>
59 The name of an existing table to alter.
65 <term><replaceable class="PARAMETER"> column </replaceable></term>
68 Name of a new or existing column.
74 <term><replaceable class="PARAMETER"> type </replaceable></term>
77 Type of the new column.
83 <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
86 New name for an existing column.
92 <term><replaceable class="PARAMETER"> newtable </replaceable></term>
95 New name for the table.
101 <term><replaceable class="PARAMETER"> table constraint definition </replaceable></term>
104 New table constraint for the table
110 <term><replaceable class="PARAMETER">New user </replaceable></term>
113 The user name of the new owner of the table.
122 <refsect2 id="R2-SQL-ALTERTABLE-2">
124 <date>1998-04-15</date>
133 <term><computeroutput>ALTER</computeroutput></term>
136 Message returned from column or table renaming.
142 <term><computeroutput>ERROR</computeroutput></term>
145 Message returned if table or column is not available.
154 <refsect1 id="R1-SQL-ALTERTABLE-1">
156 <date>1998-04-15</date>
162 <command>ALTER TABLE</command> changes the definition of an existing table.
163 The <literal>ADD COLUMN</literal> form adds a new column to the table
164 using the same syntax as <xref linkend="SQL-CREATETABLE"
165 endterm="SQL-CREATETABLE-title">.
166 The <literal>ALTER COLUMN SET/DROP DEFAULT</literal> forms
167 allow you to set or remove the default for the column. Note that defaults
168 only apply to subsequent <command>INSERT</command> commands; they do not
169 cause rows already in the table to change.
170 The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to
171 set the statistics-gathering target for subsequent
172 <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
173 The <literal>RENAME</literal> clause causes the name of a table or column
174 to change without changing any of the data contained in
175 the affected table. Thus, the table or column will
176 remain of the same type and size after this command is
178 The ADD <replaceable class="PARAMETER">table constraint definition</replaceable> clause
179 adds a new constraint to the table using the same syntax as <xref
180 linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">.
181 The DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> clause
182 drops all CHECK constraints on the table (and its children) that match <replaceable class="PARAMETER">constraint</replaceable>.
183 The OWNER clause changes the owner of the table to the user <replaceable class="PARAMETER">
184 new user</replaceable>.
188 You must own the table in order to change its schema.
191 <refsect2 id="R2-SQL-ALTERTABLE-3">
193 <date>1998-04-15</date>
199 The keyword <literal>COLUMN</literal> is noise and can be omitted.
203 In the current implementation of <literal>ADD COLUMN</literal>,
204 default and NOT NULL clauses for the new column are not supported.
205 You can use the <literal>SET DEFAULT</literal> form
206 of <command>ALTER TABLE</command> to set the default later.
207 (You may also want to update the already existing rows to the
208 new default value, using <xref linkend="sql-update"
209 endterm="sql-update-title">.)
213 Currently only CHECK constraints can be dropped from a table. The RESTRICT
214 keyword is required, although dependencies are not checked. The CASCADE
215 option is unsupported. To remove a PRIMARY or UNIQUE constraint, drop the
216 relevant index using the <xref linkend="SQL-DROPINDEX" endterm="SQL-DROPINDEX-TITLE"> command.
217 To remove FOREIGN KEY constraints you need to recreate
218 and reload the table, using other parameters to the
219 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-title">
223 For example, to drop all constraints on a table <literal>distributors</literal>:
225 CREATE TABLE temp AS SELECT * FROM distributors;
226 DROP TABLE distributors;
227 CREATE TABLE distributors AS SELECT * FROM temp;
233 You must own the table in order to change it.
234 Changing any part of the schema of a system
235 catalog is not permitted.
236 The <citetitle>PostgreSQL User's Guide</citetitle> has further
237 information on inheritance.
241 Refer to <command>CREATE TABLE</command> for a further description
247 <refsect1 id="R1-SQL-ALTERTABLE-2">
252 To add a column of type VARCHAR to a table:
254 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
259 To rename an existing column:
261 ALTER TABLE distributors RENAME COLUMN address TO city;
266 To rename an existing table:
268 ALTER TABLE distributors RENAME TO suppliers;
273 To add a check constraint to a table:
275 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
280 To remove a check constraint from a table and all its children:
282 ALTER TABLE distributors DROP CONSTRAINT zipchk;
287 To add a foreign key constraint to a table:
289 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;
294 To add a (multi-column) unique constraint to a table:
296 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
301 <refsect1 id="R1-SQL-ALTERTABLE-3">
306 <refsect2 id="R2-SQL-ALTERTABLE-4">
308 <date>1998-04-15</date>
312 The <literal>ADD COLUMN</literal> form is compliant with the exception that
313 it does not support defaults and NOT NULL constraints, as explained above.
314 The <literal>ALTER COLUMN</literal> form is in full compliance.
318 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
319 statement which are not yet directly supported by <productname>Postgres</productname>:
325 ALTER TABLE <replaceable class="PARAMETER">table</replaceable> DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
330 Removes a column from a table.
331 Currently, to remove an existing column the table must be
332 recreated and reloaded:
334 CREATE TABLE temp AS SELECT did, city FROM distributors;
335 DROP TABLE distributors;
336 CREATE TABLE distributors (
337 did DECIMAL(3) DEFAULT 1,
338 name VARCHAR(40) NOT NULL
340 INSERT INTO distributors SELECT * FROM temp;
350 The clauses to rename columns and tables are <productname>Postgres</productname>
351 extensions from SQL92.
358 <!-- Keep this comment at the end of the file
363 sgml-minimize-attributes:nil
364 sgml-always-quote-attributes:t
367 sgml-parent-document:nil
368 sgml-default-dtd-file:"../reference.ced"
369 sgml-exposed-tags:nil
370 sgml-local-catalogs:"/usr/lib/sgml/catalog"
371 sgml-local-ecat-files:nil