1 <REFENTRY ID="SQL-ALTERTABLE-1">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Modifies table properties
17 <DATE>1998-04-15</DATE>
20 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
21 [*] ADD [COLUMN] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
22 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
23 [*] RENAME [COLUMN] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> TO <REPLACEABLE CLASS="PARAMETER">newcolumn</REPLACEABLE>
24 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
25 RENAME TO <REPLACEABLE CLASS="PARAMETER">newtable</REPLACEABLE>
28 <REFSECT2 ID="R2-SQL-ALTERTABLE-1">
30 <DATE>1998-04-15</DATE>
39 <REPLACEABLE CLASS="PARAMETER"> table </REPLACEABLE>
43 The name of an existing table to alter.
50 <REPLACEABLE CLASS="PARAMETER"> column </REPLACEABLE>
54 Name of a new or existing column.
61 <REPLACEABLE CLASS="PARAMETER"> type </REPLACEABLE>
65 Type of the new column.
72 <REPLACEABLE CLASS="PARAMETER"> newcolumn </REPLACEABLE>
76 New name for an existing column.
83 <REPLACEABLE CLASS="PARAMETER"> newtable </REPLACEABLE>
87 New name for an existing column.
95 <REFSECT2 ID="R2-SQL-ALTERTABLE-2">
97 <DATE>1998-04-15</DATE>
106 <ReturnValue>status</ReturnValue>
113 <ReturnValue>ALTER</ReturnValue>
117 Message returned from column or table renaming.
124 <ReturnValue>NEW</ReturnValue>
128 Message returned from column addition.
135 <ReturnValue>ERROR</ReturnValue>
139 Message returned if table or column is not available.
151 <REFSECT1 ID="R1-SQL-ALTERTABLE-1">
153 <DATE>1998-04-15</DATE>
159 ALTER TABLE changes the definition of an existing table.
160 The new columns and their types are specified in the same style
161 and with the the same restrictions as in CREATE TABLE.
162 The RENAME clause causes the name of a table or column
163 to change without changing any of the data contained in
164 the affected table. Thus, the table or column will
165 remain of the same type and size after this command is
168 You must own the table in order to change its schema.
171 <REFSECT2 ID="R2-SQL-ALTERTABLE-3">
173 <DATE>1998-04-15</DATE>
179 The keyword COLUMN is noise and can be omitted.
182 ALTER TABLE/RENAME is a PostgreSQL language extension.
185 <Quote>[*]</Quote> following a name of a table indicates that statement
186 should be run over that table and all tables below it in the
187 inheritance hierarchy.
188 Refer to PostgreSQL User's Guide for further
189 information on inheritance.
192 Refer to the CREATE TABLE reference for further description
198 <REFSECT1 ID="R1-SQL-ALTERTABLE-2">
203 To add a column of type VARCHAR to a table:
205 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
209 To rename an existing column:
211 ALTER TABLE distributors RENAME COLUMN address TO city;
215 To rename an existing table:
217 ALTER TABLE distributors RENAME TO suppliers;
222 <REFSECT1 ID="R1-SQL-ALTERTABLE-3">
227 <REFSECT2 ID="R2-SQL-ALTERTABLE-4">
229 <DATE>1998-04-15</DATE>
235 SQL92 specifies some additional capabilities for ALTER TABLE
236 statement which are not yet directly supported by <ProductName>Postgres</ProductName>:
242 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ALTER [COLUMN] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
243 SET DEFAULT <REPLACEABLE CLASS="PARAMETER">default</REPLACEABLE>
244 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ALTER [COLUMN] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
245 ADD [CONSTRAINT <REPLACEABLE CLASS="PARAMETER">constraint</REPLACEABLE>] <REPLACEABLE CLASS="PARAMETER">table-constraint</REPLACEABLE>
250 Puts the default value or constraint specified into the
251 definition of column in the table. See CREATE TABLE for the
252 syntax of the default and table-constraint clauses.
253 If a default clause already exists, it will be replaced by
254 the new definition. If any constraints on this column already
255 exist, they will be retained using a boolean AND with the new
259 Currently, to set new default constraints on an existing column
260 the table must be recreated and reloaded:
263 CREATE TABLE temp AS SELECT * FROM distributors;
264 DROP TABLE distributors;
265 CREATE TABLE distributors (
266 did DECIMAL(3) DEFAULT 1,
267 name VARCHAR(40) NOT NULL,
270 INSERT INTO distributors SELECT * FROM temp;
280 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
281 DROP DEFAULT <REPLACEABLE CLASS="PARAMETER">default</REPLACEABLE>
282 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
283 DROP CONSTRAINT <REPLACEABLE CLASS="PARAMETER">constraint</REPLACEABLE> { RESTRICT | CASCADE }
288 Removes the default value specified by default or the rule
289 specified by constraint from the definition of a table.
290 If RESTRICT is specified only a constraint with no dependent
291 constraints can be destroyed.
292 If CASCADE is specified, Any constraints that are dependent on
293 this constraint are also dropped.
296 Currently, to remove a default value or constraints on an
297 existing column the table must be recreated and reloaded:
300 CREATE TABLE temp AS SELECT * FROM distributors;
301 DROP TABLE distributors;
302 CREATE TABLE distributors AS SELECT * FROM temp;
309 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
310 DROP [COLUMN] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> { RESTRICT | CASCADE }
315 Removes a column from a table.
316 If RESTRICT is specified only a column with no dependent
317 objects can be destroyed.
318 If CASCADE is specified, all objects that are dependent on
319 this column are also dropped.
322 Currently, to remove an existing column the table must be
323 recreated and reloaded:
326 CREATE TABLE temp AS SELECT did, city FROM distributors;
327 DROP TABLE distributors;
328 CREATE TABLE distributors (
329 did DECIMAL(3) DEFAULT 1,
330 name VARCHAR(40) NOT NULL,
332 INSERT INTO distributors SELECT * FROM temp;
340 <REPLACEABLE CLASS="PARAMETER">
342 <ReturnValue></ReturnValue>