1 <REFENTRY ID="SQL-ALTERTABLE">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Modifies table properties
18 <DATE>1998-04-15</DATE>
21 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
22 [ * ] ADD [ COLUMN ] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
23 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
24 [ * ] RENAME [ COLUMN ] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> TO <REPLACEABLE CLASS="PARAMETER">newcolumn</REPLACEABLE>
25 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
26 RENAME TO <REPLACEABLE CLASS="PARAMETER">newtable</REPLACEABLE>
29 <REFSECT2 ID="R2-SQL-ALTERTABLE-1">
31 <DATE>1998-04-15</DATE>
40 <REPLACEABLE CLASS="PARAMETER"> table </REPLACEABLE>
44 The name of an existing table to alter.
51 <REPLACEABLE CLASS="PARAMETER"> column </REPLACEABLE>
55 Name of a new or existing column.
62 <REPLACEABLE CLASS="PARAMETER"> type </REPLACEABLE>
66 Type of the new column.
73 <REPLACEABLE CLASS="PARAMETER"> newcolumn </REPLACEABLE>
77 New name for an existing column.
84 <REPLACEABLE CLASS="PARAMETER"> newtable </REPLACEABLE>
88 New name for an existing column.
96 <REFSECT2 ID="R2-SQL-ALTERTABLE-2">
98 <DATE>1998-04-15</DATE>
108 <returnvalue>ALTER</returnvalue>
112 Message returned from column or table renaming.
119 <returnvalue>NEW</returnvalue>
123 Message returned from column addition.
130 <ReturnValue>ERROR</ReturnValue>
134 Message returned if table or column is not available.
143 <REFSECT1 ID="R1-SQL-ALTERTABLE-1">
145 <DATE>1998-04-15</DATE>
151 <command>ALTER TABLE</command> changes the definition of an existing table.
152 The new columns and their types are specified in the same style
153 and with the the same restrictions as in <command>CREATE TABLE</command>.
154 The RENAME clause causes the name of a table or column
155 to change without changing any of the data contained in
156 the affected table. Thus, the table or column will
157 remain of the same type and size after this command is
161 You must own the table in order to change its schema.
164 <REFSECT2 ID="R2-SQL-ALTERTABLE-3">
166 <DATE>1998-04-15</DATE>
172 The keyword COLUMN is noise and can be omitted.
175 <Quote>[*]</Quote> following a name of a table indicates that statement
176 should be run over that table and all tables below it in the
177 inheritance hierarchy.
178 The <citetitle>PostgreSQL User's Guide</citetitle> has further
179 information on inheritance.
183 Refer to CREATE TABLE for a further description
189 <REFSECT1 ID="R1-SQL-ALTERTABLE-2">
194 To add a column of type VARCHAR to a table:
196 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
201 To rename an existing column:
203 ALTER TABLE distributors RENAME COLUMN address TO city;
208 To rename an existing table:
210 ALTER TABLE distributors RENAME TO suppliers;
216 <REFSECT1 ID="R1-SQL-ALTERTABLE-3">
221 <REFSECT2 ID="R2-SQL-ALTERTABLE-4">
223 <DATE>1998-04-15</DATE>
229 <command>ALTER TABLE/RENAME</command>
230 is a <productname>Postgres</productname> language extension.
234 SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
235 statement which are not yet directly supported by
236 <ProductName>Postgres</ProductName>:
243 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ALTER [ COLUMN ] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
244 SET DEFAULT <REPLACEABLE CLASS="PARAMETER">default</REPLACEABLE>
246 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ALTER [ COLUMN ] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
247 ADD [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">constraint</REPLACEABLE> ] <REPLACEABLE CLASS="PARAMETER">table-constraint</REPLACEABLE>
252 Puts the default value or constraint specified into the
253 definition of column in the table.
254 See <command>CREATE TABLE</command> for the
255 syntax of the default and table-constraint clauses.
256 If a default clause already exists, it will be replaced by
257 the new definition. If any constraints on this column already
258 exist, they will be retained using a boolean AND with the new
263 Currently, to set new default constraints on an existing column
264 the table must be recreated and reloaded:
267 CREATE TABLE temp AS SELECT * FROM distributors;
268 DROP TABLE distributors;
269 CREATE TABLE distributors (
270 did DECIMAL(3) DEFAULT 1,
271 name VARCHAR(40) NOT NULL,
274 INSERT INTO distributors SELECT * FROM temp;
284 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
285 DROP DEFAULT <REPLACEABLE CLASS="PARAMETER">default</REPLACEABLE>
286 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
287 DROP CONSTRAINT <REPLACEABLE CLASS="PARAMETER">constraint</REPLACEABLE> { RESTRICT | CASCADE }
292 Removes the default value specified by default or the rule
293 specified by constraint from the definition of a table.
294 If RESTRICT is specified only a constraint with no dependent
295 constraints can be destroyed.
296 If CASCADE is specified, Any constraints that are dependent on
297 this constraint are also dropped.
301 Currently, to remove a default value or constraints on an
302 existing column the table must be recreated and reloaded:
305 CREATE TABLE temp AS SELECT * FROM distributors;
306 DROP TABLE distributors;
307 CREATE TABLE distributors AS SELECT * FROM temp;
317 ALTER TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
318 DROP [ COLUMN ] <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> { RESTRICT | CASCADE }
323 Removes a column from a table.
324 If RESTRICT is specified only a column with no dependent
325 objects can be destroyed.
326 If CASCADE is specified, all objects that are dependent on
327 this column are also dropped.
331 Currently, to remove an existing column the table must be
332 recreated and reloaded:
335 CREATE TABLE temp AS SELECT did, city FROM distributors;
336 DROP TABLE distributors;
337 CREATE TABLE distributors (
338 did DECIMAL(3) DEFAULT 1,
339 name VARCHAR(40) NOT NULL,
341 INSERT INTO distributors SELECT * FROM temp;