2
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.77 2005-01-14 01:16:52 tgl Exp $
3
PostgreSQL documentation
6
<refentry id="SQL-ALTERTABLE">
8
<refentrytitle id="sql-altertable-title">ALTER TABLE</refentrytitle>
9
<refmiscinfo>SQL - Language Statements</refmiscinfo>
13
<refname>ALTER TABLE</refname>
14
<refpurpose>change the definition of a table</refpurpose>
17
<indexterm zone="sql-altertable">
18
<primary>ALTER TABLE</primary>
23
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
24
<replaceable class="PARAMETER">action</replaceable> [, ... ]
25
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>
30
where <replaceable class="PARAMETER">action</replaceable> is one of:
32
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
33
DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
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
37
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
38
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
39
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
40
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
41
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
42
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
45
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
46
SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
51
<title>Description</title>
54
<command>ALTER TABLE</command> changes the definition of an existing table.
55
There are several subforms:
59
<term><literal>ADD COLUMN</literal></term>
62
This form adds a new column to the table using the same syntax as
63
<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
69
<term><literal>DROP COLUMN</literal></term>
72
This form drops a column from a table. Indexes and
73
table constraints involving the column will be automatically
74
dropped as well. You will need to say <literal>CASCADE</> if
75
anything outside the table depends on the column, for example,
76
foreign key references or views.
82
<term><literal>ALTER COLUMN TYPE</literal></term>
85
This form changes the type of a column of a table. Indexes and
86
simple table constraints involving the column will be automatically
87
converted to use the new column type by reparsing the originally
88
supplied expression. The optional <literal>USING</literal>
89
clause specifies how to compute the new column value from the old;
90
if omitted, the default conversion is the same as an assignment
91
cast from old data type to new. A <literal>USING</literal>
92
clause must be provided if there is no implicit or assignment
93
cast from old to new type.
99
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
102
These forms set or remove the default value for a column.
103
The default values only apply to subsequent <command>INSERT</command>
104
commands; they do not cause rows already in the table to change.
105
Defaults may also be created for views, in which case they are
106
inserted into <command>INSERT</> statements on the view before
107
the view's <literal>ON INSERT</literal> rule is applied.
113
<term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
116
These forms change whether a column is marked to allow null
117
values or to reject null values. You can only use <literal>SET
118
NOT NULL</> when the column contains no null values.
124
<term><literal>SET STATISTICS</literal></term>
128
sets the per-column statistics-gathering target for subsequent
129
<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
130
The target can be set in the range 0 to 1000; alternatively, set it
131
to -1 to revert to using the system default statistics
132
target (<xref linkend="guc-default-statistics-target">).
133
For more information on the use of statistics by the
134
<productname>PostgreSQL</productname> query planner, refer to
135
<xref linkend="planner-stats">.
142
<primary>TOAST</primary>
143
<secondary>per-column storage settings</secondary>
146
<term><literal>SET STORAGE</literal></term>
149
This form sets the storage mode for a column. This controls whether this
150
column is held inline or in a supplementary table, and whether the data
151
should be compressed or not. <literal>PLAIN</literal> must be used
152
for fixed-length values such as <type>integer</type> and is
153
inline, uncompressed. <literal>MAIN</literal> is for inline,
154
compressible data. <literal>EXTERNAL</literal> is for external,
155
uncompressed data, and <literal>EXTENDED</literal> is for external,
156
compressed data. <literal>EXTENDED</literal> is the default for most
157
data types that support non-<literal>PLAIN</literal> storage.
158
Use of <literal>EXTERNAL</literal> will
159
make substring operations on <type>text</type> and <type>bytea</type>
160
columns faster, at the penalty of increased storage space. Note that
161
<literal>SET STORAGE</> doesn't itself change anything in the table,
162
it just sets the strategy to be pursued during future table updates.
163
See <xref linkend="storage-toast"> for more information.
169
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
172
This form adds a new constraint to a table using the same syntax as
173
<xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
179
<term><literal>DROP CONSTRAINT</literal></term>
182
This form drops constraints on a table.
183
Currently, constraints on tables are not required to have unique
184
names, so there may be more than one constraint matching the specified
185
name. All matching constraints will be dropped.
191
<term><literal>CLUSTER</literal></term>
194
This form selects the default index for future
195
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
196
operations. It does not actually re-cluster the table.
202
<term><literal>SET WITHOUT CLUSTER</literal></term>
205
This form removes the most recently used
206
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
207
index specification from the table. This affects
208
future cluster operations that don't specify an index.
214
<term><literal>SET WITHOUT OIDS</literal></term>
217
This form removes the <literal>oid</literal> system column from the
218
table. This is exactly equivalent to
219
<literal>DROP COLUMN oid RESTRICT</literal>,
220
except that it will not complain if there is already no
221
<literal>oid</literal> column.
225
Note that there is no variant of <command>ALTER TABLE</command>
226
that allows OIDs to be restored to a table once they have been
233
<term><literal>OWNER</literal></term>
236
This form changes the owner of the table, index, sequence, or view to the
243
<term><literal>SET TABLESPACE</literal></term>
246
This form changes the table's tablespace to the specified tablespace and
247
moves the data file(s) associated with the table to the new tablespace.
248
Indexes on the table, if any, are not moved; but they can be moved
249
separately with additional <literal>SET TABLESPACE</literal> commands.
251
<xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
257
<term><literal>RENAME</literal></term>
260
The <literal>RENAME</literal> forms change the name of a table
261
(or an index, sequence, or view) or the name of an individual column in
262
a table. There is no effect on the stored data.
271
All the actions except <literal>RENAME</literal> can be combined into
272
a list of multiple alterations to apply in parallel. For example, it
273
is possible to add several columns and/or alter the type of several
274
columns in a single command. This is particularly useful with large
275
tables, since only one pass over the table need be made.
279
You must own the table to use <command>ALTER TABLE</>; except for
280
<command>ALTER TABLE OWNER</>, which may only be executed by a superuser.
285
<title>Parameters</title>
290
<term><replaceable class="PARAMETER">name</replaceable></term>
293
The name (possibly schema-qualified) of an existing table to
294
alter. If <literal>ONLY</> is specified, only that table is
295
altered. If <literal>ONLY</> is not specified, the table and all
296
its descendant tables (if any) are updated. <literal>*</> can be
297
appended to the table name to indicate that descendant tables are
298
to be altered, but in the current version, this is the default
299
behavior. (In releases before 7.1, <literal>ONLY</> was the
300
default behavior. The default can be altered by changing the
301
configuration parameter <xref linkend="guc-sql-inheritance">.)
307
<term><replaceable class="PARAMETER">column</replaceable></term>
310
Name of a new or existing column.
316
<term><replaceable class="PARAMETER">new_column</replaceable></term>
319
New name for an existing column.
325
<term><replaceable class="PARAMETER">new_name</replaceable></term>
328
New name for the table.
334
<term><replaceable class="PARAMETER">type</replaceable></term>
337
Data type of the new column, or new data type for an existing
344
<term><replaceable class="PARAMETER">table_constraint</replaceable></term>
347
New table constraint for the table.
353
<term><replaceable class="PARAMETER">constraint_name</replaceable></term>
356
Name of an existing constraint to drop.
362
<term><literal>CASCADE</literal></term>
365
Automatically drop objects that depend on the dropped column
366
or constraint (for example, views referencing the column).
372
<term><literal>RESTRICT</literal></term>
375
Refuse to drop the column or constraint if there are any dependent
376
objects. This is the default behavior.
382
<term><replaceable class="PARAMETER">index_name</replaceable></term>
385
The index name on which the table should be marked for clustering.
391
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
394
The user name of the new owner of the table.
400
<term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
403
The tablespace name to which the table will be moved.
415
The key word <literal>COLUMN</literal> is noise and can be omitted.
419
When a column is added with <literal>ADD COLUMN</literal>, all existing
420
rows in the table are initialized with the column's default value
421
(NULL if no <literal>DEFAULT</> clause is specified).
425
Adding a column with a non-null default or changing the type of an
426
existing column will require the entire table to be rewritten. This
427
may take a significant amount of time for a large table; and it will
428
temporarily require double the disk space.
432
Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
433
scanning the table to verify that existing rows meet the constraint.
437
The main reason for providing the option to specify multiple changes
438
in a single <command>ALTER TABLE</> is that multiple table scans or
439
rewrites can thereby be combined into a single pass over the table.
443
The <literal>DROP COLUMN</literal> form does not physically remove
444
the column, but simply makes it invisible to SQL operations. Subsequent
445
insert and update operations in the table will store a null value for the
446
column. Thus, dropping a column is quick but it will not immediately
447
reduce the on-disk size of your table, as the space occupied
448
by the dropped column is not reclaimed. The space will be
449
reclaimed over time as existing rows are updated.
453
The fact that <literal>ALTER TYPE</> requires rewriting the whole table
454
is sometimes an advantage, because the rewriting process eliminates
455
any dead space in the table. For example, to reclaim the space occupied
456
by a dropped column immediately, the fastest way is
458
ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
460
where <literal>anycol</> is any remaining table column and
461
<literal>anytype</> is the same type that column already has.
462
This results in no semantically-visible change in the table,
463
but the command forces rewriting, which gets rid of no-longer-useful
468
The <literal>USING</literal> option of <literal>ALTER TYPE</> can actually
469
specify any expression involving the old values of the row; that is, it
470
can refer to other columns as well as the one being converted. This allows
471
very general conversions to be done with the <literal>ALTER TYPE</>
472
syntax. Because of this flexibility, the <literal>USING</literal>
473
expression is not applied to the column's default value (if any); the
474
result might not be a constant expression as required for a default.
475
This means that when there is no implicit or assignment cast from old to
476
new type, <literal>ALTER TYPE</> may fail to convert the default even
477
though a <literal>USING</literal> clause is supplied. In such cases,
478
drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
479
TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
480
default. Similar considerations apply to indexes and constraints involving
485
If a table has any descendant tables, it is not permitted to add,
486
rename, or change the type of a column in the parent table without doing
487
the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
488
will be rejected. This ensures that the descendants always have
489
columns matching the parent.
493
A recursive <literal>DROP COLUMN</literal> operation will remove a
494
descendant table's column only if the descendant does not inherit
495
that column from any other parents and never had an independent
496
definition of the column. A nonrecursive <literal>DROP
497
COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
498
COLUMN</command>) never removes any descendant columns, but
499
instead marks them as independently defined rather than inherited.
503
Changing any part of a system catalog table is not permitted.
507
Refer to <xref linkend="sql-createtable"
508
endterm="sql-createtable-title"> for a further description of valid
509
parameters. <xref linkend="ddl"> has further information on
515
<title>Examples</title>
518
To add a column of type <type>varchar</type> to a table:
520
ALTER TABLE distributors ADD COLUMN address varchar(30);
525
To drop a column from a table:
527
ALTER TABLE distributors DROP COLUMN address RESTRICT;
532
To change the types of two existing columns in one operation:
534
ALTER TABLE distributors
535
ALTER COLUMN address TYPE varchar(80),
536
ALTER COLUMN name TYPE varchar(100);
541
To change an integer column containing UNIX timestamps to <type>timestamp
542
with time zone</type> via a <literal>USING</literal> clause:
545
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
547
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
552
To rename an existing column:
554
ALTER TABLE distributors RENAME COLUMN address TO city;
559
To rename an existing table:
561
ALTER TABLE distributors RENAME TO suppliers;
566
To add a not-null constraint to a column:
568
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
570
To remove a not-null constraint from a column:
572
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
577
To add a check constraint to a table:
579
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
584
To remove a check constraint from a table and all its children:
586
ALTER TABLE distributors DROP CONSTRAINT zipchk;
591
To add a foreign key constraint to a table:
593
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
598
To add a (multicolumn) unique constraint to a table:
600
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
605
To add an automatically named primary key constraint to a table, noting
606
that a table can only ever have one primary key:
608
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
613
To move a table to a different tablespace:
615
ALTER TABLE distributors SET TABLESPACE fasttablespace;
622
<title>Compatibility</title>
625
The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
626
forms conform with the SQL standard. The other forms are
627
<productname>PostgreSQL</productname> extensions of the SQL standard.
628
Also, the ability to specify more than one manipulation in a single
629
<command>ALTER TABLE</> command is an extension.
633
<command>ALTER TABLE DROP COLUMN</> can be used to drop the only
634
column of a table, leaving a zero-column table. This is an
635
extension of SQL, which disallows zero-column tables.
640
<!-- Keep this comment at the end of the file
645
sgml-minimize-attributes:nil
646
sgml-always-quote-attributes:t
649
sgml-parent-document:nil
650
sgml-default-dtd-file:"../reference.ced"
651
sgml-exposed-tags:nil
652
sgml-local-catalogs:"/usr/lib/sgml/catalog"
653
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/ref/alter_table.sgml
