Skip to content

Commit 077db40

Browse files
tglsfdctglsfdc
committed
ALTER TABLE rewrite. New cool stuff:
* ALTER ... ADD COLUMN with defaults and NOT NULL constraints works per SQL spec. A default is implemented by rewriting the table with the new value stored in each row. * ALTER COLUMN TYPE. You can change a column's datatype to anything you want, so long as you can specify how to convert the old value. Rewrites the table. (Possible future improvement: optimize no-op conversions such as varchar(N) to varchar(N+1).) * Multiple ALTER actions in a single ALTER TABLE command. You can perform any number of column additions, type changes, and constraint additions with only one pass over the table contents. Basic documentation provided in ALTER TABLE ref page, but some more docs work is needed. Original patch from Rod Taylor, additional work from Tom Lane.
1 parent 3e3cb0a commit 077db40

30 files changed

+3492
-2096
lines changed

‎doc/src/sgml/ref/alter_table.sgml

+106-69
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.68 2004/03/24 09:49:20 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.69 2004/05/05 04:48:45 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,31 +21,26 @@ PostgreSQL documentation
2121
<refsynopsisdiv>
2222
<synopsis>
2323
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
24-
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
24+
<replaceable class="PARAMETER">action</replaceable> [, ... ]
2525
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
26+
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
27+
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
28+
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
29+
30+
where <replaceable class="PARAMETER">action</replaceable> is one of:
31+
32+
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
2633
DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
27-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
28-
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable class="PARAMETER">expression</replaceable> | DROP DEFAULT }
29-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
34+
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
35+
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
36+
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
3037
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
31-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
3238
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
33-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
3439
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
35-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
36-
SET WITHOUT OIDS
37-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
38-
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
39-
class="PARAMETER">new_column</replaceable>
40-
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
41-
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
42-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
4340
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
44-
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
4541
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
46-
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
42+
SET WITHOUT OIDS
4743
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
48-
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
4944
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
5045
</synopsis>
5146
</refsynopsisdiv>
@@ -81,6 +76,23 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
8176
</listitem>
8277
</varlistentry>
8378

79+
<varlistentry>
80+
<term><literal>ALTER COLUMN TYPE</literal></term>
81+
<listitem>
82+
<para>
83+
This form changes the type of a column of a table. Indexes and
84+
simple table constraints involving the column will be automatically
85+
converted to use the new column type by reparsing the originally
86+
supplied expression. The optional <literal>USING</literal>
87+
clause specifies how to compute the new column value from the old;
88+
if omitted, the default conversion is the same as an assignment
89+
cast from old data type to new. A <literal>USING</literal>
90+
clause must be provided if there is no implicit or assignment
91+
cast from old to new type.
92+
</para>
93+
</listitem>
94+
</varlistentry>
95+
8496
<varlistentry>
8597
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
8698
<listitem>
@@ -147,53 +159,42 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
147159
</varlistentry>
148160

149161
<varlistentry>
150-
<term><literal>SET WITHOUT OIDS</literal></term>
162+
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
151163
<listitem>
152164
<para>
153-
This form removes the <literal>oid</literal> system column from the
154-
table. This is exactly equivalent to
155-
<literal>DROP COLUMN oid RESTRICT</literal>,
156-
except that it will not complain if there is already no
157-
<literal>oid</literal> column.
158-
</para>
159-
160-
<para>
161-
Note that there is no variant of <command>ALTER TABLE</command>
162-
that allows OIDs to be restored to a table once they have been
163-
removed.
165+
This form adds a new constraint to a table using the same syntax as
166+
<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
164167
</para>
165168
</listitem>
166169
</varlistentry>
167170

168171
<varlistentry>
169-
<term><literal>RENAME</literal></term>
172+
<term><literal>DROP CONSTRAINT</literal></term>
170173
<listitem>
171174
<para>
172-
The <literal>RENAME</literal> forms change the name of a table
173-
(or an index, sequence, or view) or the name of an individual column in
174-
a table. There is no effect on the stored data.
175+
This form drops constraints on a table.
176+
Currently, constraints on tables are not required to have unique
177+
names, so there may be more than one constraint matching the specified
178+
name. All matching constraints will be dropped.
175179
</para>
176180
</listitem>
177181
</varlistentry>
178182

179183
<varlistentry>
180-
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
184+
<term><literal>SET WITHOUT OIDS</literal></term>
181185
<listitem>
182186
<para>
183-
This form adds a new constraint to a table using the same syntax as
184-
<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
187+
This form removes the <literal>oid</literal> system column from the
188+
table. This is exactly equivalent to
189+
<literal>DROP COLUMN oid RESTRICT</literal>,
190+
except that it will not complain if there is already no
191+
<literal>oid</literal> column.
185192
</para>
186-
</listitem>
187-
</varlistentry>
188193

189-
<varlistentry>
190-
<term><literal>DROP CONSTRAINT</literal></term>
191-
<listitem>
192194
<para>
193-
This form drops constraints on a table.
194-
Currently, constraints on tables are not required to have unique
195-
names, so there may be more than one constraint matching the specified
196-
name. All such constraints will be dropped.
195+
Note that there is no variant of <command>ALTER TABLE</command>
196+
that allows OIDs to be restored to a table once they have been
197+
removed.
197198
</para>
198199
</listitem>
199200
</varlistentry>
@@ -212,15 +213,34 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
212213
<term><literal>CLUSTER</literal></term>
213214
<listitem>
214215
<para>
215-
This form marks a table for future <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
216+
This form selects the default controlling index for future <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
216217
operations.
217218
</para>
218219
</listitem>
219220
</varlistentry>
220221

222+
<varlistentry>
223+
<term><literal>RENAME</literal></term>
224+
<listitem>
225+
<para>
226+
The <literal>RENAME</literal> forms change the name of a table
227+
(or an index, sequence, or view) or the name of an individual column in
228+
a table. There is no effect on the stored data.
229+
</para>
230+
</listitem>
231+
</varlistentry>
232+
221233
</variablelist>
222234
</para>
223235

236+
<para>
237+
All the actions except <literal>RENAME</literal> can be combined into
238+
a list of multiple alterations to apply in parallel. For example, it
239+
is possible to add several columns and/or alter the type of several
240+
columns in a single command. This is particularly useful with large
241+
tables, since only one pass over the table need be made.
242+
</para>
243+
224244
<para>
225245
You must own the table to use <command>ALTER TABLE</>; except for
226246
<command>ALTER TABLE OWNER</>, which may only be executed by a superuser.
@@ -262,7 +282,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
262282
<term><replaceable class="PARAMETER">type</replaceable></term>
263283
<listitem>
264284
<para>
265-
Data type of the new column.
285+
Data type of the new column, or new data type for an existing
286+
column.
266287
</para>
267288
</listitem>
268289
</varlistentry>
@@ -352,16 +373,27 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
352373
</para>
353374

354375
<para>
355-
In the current implementation of <literal>ADD COLUMN</literal>,
356-
default and <literal>NOT NULL</> clauses for the new column are not supported.
357-
The new column always comes into being with all values null.
358-
You can use the <literal>SET DEFAULT</literal> form
359-
of <command>ALTER TABLE</command> to set the default afterward.
360-
(You may also want to update the already existing rows to the
361-
new default value, using
362-
<xref linkend="sql-update" endterm="sql-update-title">.)
363-
If you want to mark the column non-null, use the <literal>SET NOT NULL</>
364-
form after you've entered non-null values for the column in all rows.
376+
When a column is added with <literal>ADD COLUMN</literal>, all existing
377+
rows in the table are initialized with the column's default value
378+
(NULL if no <literal>DEFAULT</> clause is specified).
379+
</para>
380+
381+
<para>
382+
Adding a column with a non-null default or changing the type of an
383+
existing column will require the entire table to be rewritten. This
384+
may take a significant amount of time for a large table; and it will
385+
temporarily require double the disk space.
386+
</para>
387+
388+
<para>
389+
Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
390+
scanning the table to verify that existing rows meet the constraint.
391+
</para>
392+
393+
<para>
394+
The main reason for providing the option to specify multiple changes
395+
in a single <command>ALTER TABLE</> is that multiple table scans or
396+
rewrites can thereby be combined into a single pass over the table.
365397
</para>
366398

367399
<para>
@@ -381,9 +413,9 @@ VACUUM FULL table;
381413
</para>
382414

383415
<para>
384-
If a table has any descendant tables, it is not permitted to add
385-
or rename a column in the parent table without doing the same to
386-
the descendants. That is, <command>ALTER TABLE ONLY</command>
416+
If a table has any descendant tables, it is not permitted to add,
417+
rename, or change the type of a column in the parent table without doing
418+
the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
387419
will be rejected. This ensures that the descendants always have
388420
columns matching the parent.
389421
</para>
@@ -427,6 +459,15 @@ ALTER TABLE distributors DROP COLUMN address RESTRICT;
427459
</programlisting>
428460
</para>
429461

462+
<para>
463+
To change the types of two existing columns in one operation:
464+
<programlisting>
465+
ALTER TABLE distributors
466+
ALTER COLUMN address TYPE varchar(80),
467+
ALTER COLUMN name TYPE varchar(100);
468+
</programlisting>
469+
</para>
470+
430471
<para>
431472
To rename an existing column:
432473
<programlisting>
@@ -493,15 +534,11 @@ ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
493534
<title>Compatibility</title>
494535

495536
<para>
496-
The <literal>ADD COLUMN</literal> form conforms with the SQL
497-
standard, with the exception that it does not support defaults and
498-
not-null constraints, as explained above. The <literal>ALTER
499-
COLUMN</literal> form is in full conformance.
500-
</para>
501-
502-
<para>
503-
The clauses to rename tables, columns, indexes, views, and sequences are
537+
The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
538+
forms conform with the SQL standard. The other forms are
504539
<productname>PostgreSQL</productname> extensions of the SQL standard.
540+
Also, the ability to specify more than one manipulation in a single
541+
<command>ALTER TABLE</> command is an extension.
505542
</para>
506543

507544
<para>

‎src/backend/bootstrap/bootparse.y

+7-3
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.65 2004/03/23 19:35:16 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.66 2004/05/05 04:48:45 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -241,7 +241,9 @@ Boot_DeclareIndexStmt:
241241
LexIDStr($3),
242242
LexIDStr($7),
243243
$9,
244-
false, false, false, NULL, NIL);
244+
NULL, NIL,
245+
false, false, false,
246+
false, false, true, false);
245247
do_end();
246248
}
247249
;
@@ -255,7 +257,9 @@ Boot_DeclareUniqueIndexStmt:
255257
LexIDStr($4),
256258
LexIDStr($8),
257259
$10,
258-
true, false, false, NULL, NIL);
260+
NULL, NIL,
261+
true, false, false,
262+
false, false, true, false);
259263
do_end();
260264
}
261265
;

0 commit comments

Comments
 (0)