3
.\" Author: The PostgreSQL Global Development Group
4
.\" Generator: DocBook XSL Stylesheets v1.75.1 <http://docbook.sf.net/>
6
.\" Manual: PostgreSQL 9.1beta1 Documentation
7
.\" Source: PostgreSQL 9.1beta1
10
.TH "ALTER TABLE" "7" "2011-04-27" "PostgreSQL 9.1beta1" "PostgreSQL 9.1beta1 Documentation"
11
.\" -----------------------------------------------------------------
12
.\" * set default formatting
13
.\" -----------------------------------------------------------------
14
.\" disable hyphenation
16
.\" disable justification (adjust text to left margin only)
18
.\" -----------------------------------------------------------------
19
.\" * MAIN CONTENT STARTS HERE *
20
.\" -----------------------------------------------------------------
22
ALTER_TABLE \- change the definition of a table
27
ALTER TABLE [ ONLY ] \fIname\fR [ * ]
28
\fIaction\fR [, \&.\&.\&. ]
29
ALTER TABLE [ ONLY ] \fIname\fR [ * ]
30
RENAME [ COLUMN ] \fIcolumn\fR TO \fInew_column\fR
31
ALTER TABLE \fIname\fR
32
RENAME TO \fInew_name\fR
33
ALTER TABLE \fIname\fR
34
SET SCHEMA \fInew_schema\fR
36
where \fIaction\fR is one of:
38
ADD [ COLUMN ] \fIcolumn\fR \fIdata_type\fR [ COLLATE \fIcollation\fR ] [ \fIcolumn_constraint\fR [ \&.\&.\&. ] ]
39
DROP [ COLUMN ] [ IF EXISTS ] \fIcolumn\fR [ RESTRICT | CASCADE ]
40
ALTER [ COLUMN ] \fIcolumn\fR [ SET DATA ] TYPE \fIdata_type\fR [ COLLATE \fIcollation\fR ] [ USING \fIexpression\fR ]
41
ALTER [ COLUMN ] \fIcolumn\fR SET DEFAULT \fIexpression\fR
42
ALTER [ COLUMN ] \fIcolumn\fR DROP DEFAULT
43
ALTER [ COLUMN ] \fIcolumn\fR { SET | DROP } NOT NULL
44
ALTER [ COLUMN ] \fIcolumn\fR SET STATISTICS \fIinteger\fR
45
ALTER [ COLUMN ] \fIcolumn\fR SET ( \fIattribute_option\fR = \fIvalue\fR [, \&.\&.\&. ] )
46
ALTER [ COLUMN ] \fIcolumn\fR RESET ( \fIattribute_option\fR [, \&.\&.\&. ] )
47
ALTER [ COLUMN ] \fIcolumn\fR SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
48
ADD \fItable_constraint\fR
49
ADD \fItable_constraint_using_index\fR
50
ADD \fItable_constraint\fR [ NOT VALID ]
51
VALIDATE CONSTRAINT \fIconstraint_name\fR
52
DROP CONSTRAINT [ IF EXISTS ] \fIconstraint_name\fR [ RESTRICT | CASCADE ]
53
DISABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ]
54
ENABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ]
55
ENABLE REPLICA TRIGGER \fItrigger_name\fR
56
ENABLE ALWAYS TRIGGER \fItrigger_name\fR
57
DISABLE RULE \fIrewrite_rule_name\fR
58
ENABLE RULE \fIrewrite_rule_name\fR
59
ENABLE REPLICA RULE \fIrewrite_rule_name\fR
60
ENABLE ALWAYS RULE \fIrewrite_rule_name\fR
61
CLUSTER ON \fIindex_name\fR
65
SET ( \fIstorage_parameter\fR = \fIvalue\fR [, \&.\&.\&. ] )
66
RESET ( \fIstorage_parameter\fR [, \&.\&.\&. ] )
67
INHERIT \fIparent_table\fR
68
NO INHERIT \fIparent_table\fR
71
OWNER TO \fInew_owner\fR
72
SET TABLESPACE \fInew_tablespace\fR
74
and \fItable_constraint_using_index\fR is:
76
[ CONSTRAINT \fIconstraint_name\fR ]
77
{ UNIQUE | PRIMARY KEY } USING INDEX \fIindex_name\fR
78
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
83
changes the definition of an existing table\&. There are several subforms:
87
This form adds a new column to the table, using the same syntax as
88
CREATE TABLE (\fBCREATE_TABLE\fR(7))\&.
91
DROP COLUMN [ IF EXISTS ]
93
This form drops a column from a table\&. Indexes and table constraints involving the column will be automatically dropped as well\&. You will need to say
95
if anything outside the table depends on the column, for example, foreign key references or views\&. If
97
is specified and the column does not exist, no error is thrown\&. In this case a notice is issued instead\&.
102
This form changes the type of a column of a table\&. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression\&. The optional
104
clause specifies a collation for the new column; if omitted, the collation is the default for the new column type\&. The optional
106
clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new\&. A
108
clause must be provided if there is no implicit or assignment cast from old to new type\&.
113
These forms set or remove the default value for a column\&. The default values only apply to subsequent
115
commands; they do not cause rows already in the table to change\&. Defaults can also be created for views, in which case they are inserted into
117
statements on the view before the view\(aqs
124
These forms change whether a column is marked to allow null values or to reject null values\&. You can only use
126
when the column contains no null values\&.
131
This form sets the per\-column statistics\-gathering target for subsequent
133
operations\&. The target can be set in the range 0 to 10000; alternatively, set it to \-1 to revert to using the system default statistics target (default_statistics_target)\&. For more information on the use of statistics by the
135
query planner, refer to
136
Section 14.2, \(lqStatistics Used by the Planner\(rq, in the documentation\&.
139
SET ( \fIattribute_option\fR = \fIvalue\fR [, \&.\&.\&. ] ), RESET ( \fIattribute_option\fR [, \&.\&.\&. ] )
141
This form sets or resets per\-attribute options\&. Currently, the only defined per\-attribute options are
144
n_distinct_inherited, which override the number\-of\-distinct\-values estimates made by subsequent
148
affects the statistics for the table itself, while
150
affects the statistics gathered for the table plus its inheritance children\&. When set to a positive value,
152
will assume that the column contains exactly the specified number of distinct nonnull values\&. When set to a negative value, which must be greater than or equal to \-1,
154
will assume that the number of distinct nonnull values in the column is linear in the size of the table; the exact count is to be computed by multiplying the estimated table size by the absolute value of the given number\&. For example, a value of \-1 implies that all values in the column are distinct, while a value of \-0\&.5 implies that each value appears twice on the average\&. This can be useful when the size of the table changes over time, since the multiplication by the number of rows in the table is not performed until query planning time\&. Specify a value of 0 to revert to estimating the number of distinct values normally\&. For more information on the use of statistics by the
156
query planner, refer to
157
Section 14.2, \(lqStatistics Used by the Planner\(rq, in the documentation\&.
162
.\" TOAST: per-column storage settings
163
This form sets the storage mode for a column\&. This controls whether this column is held inline or in a secondary
165
table, and whether the data should be compressed or not\&.
167
must be used for fixed\-length values such as
169
and is inline, uncompressed\&.
171
is for inline, compressible data\&.
173
is for external, uncompressed data, and
175
is for external, compressed data\&.
177
is the default for most data types that support non\-PLAIN
180
will make substring operations on very large
184
values run faster, at the penalty of increased storage space\&. Note that
186
doesn\(aqt itself change anything in the table, it just sets the strategy to be pursued during future table updates\&. See
187
Section 55.2, \(lqTOAST\(rq, in the documentation
188
for more information\&.
191
ADD \fItable_constraint\fR [ NOT VALID ]
193
This form adds a new constraint to a table using the same syntax as
194
CREATE TABLE (\fBCREATE_TABLE\fR(7))\&. Newly added foreign key constraints can also be defined as
196
to avoid the potentially lengthy initial check that must otherwise be performed\&. Constraint checks are skipped at create table time, so
197
CREATE TABLE (\fBCREATE_TABLE\fR(7))
198
does not contain this option\&.
203
This form validates a foreign key constraint that was previously created as
204
NOT VALID\&. Constraints already marked valid do not cause an error response\&.
207
ADD \fItable_constraint_using_index\fR
213
constraint to a table based on an existing unique index\&. All the columns of the index will be included in the constraint\&.
215
The index cannot have expression columns nor be a partial index\&. Also, it must be a b\-tree index with default sort ordering\&. These restrictions ensure that the index is equivalent to one that would be built by a regular
223
is specified, and the index\(aqs columns are not already marked
224
NOT NULL, then this command will attempt to do
225
ALTER COLUMN SET NOT NULL
226
against each such column\&. That requires a full table scan to verify the column(s) contain no nulls\&. In all other cases, this is a fast operation\&.
228
If a constraint name is provided then the index will be renamed to match the constraint name\&. Otherwise the constraint will be named the same as the index\&.
230
After this command is executed, the index is
232
by the constraint, in the same way as if the index had been built by a regular
236
command\&. In particular, dropping the constraint will make the index disappear too\&.
242
.nr an-no-space-flag 1
249
Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time\&. To do that, create the index using
250
CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax\&. See the example below\&.
255
DROP CONSTRAINT [ IF EXISTS ]
257
This form drops the specified constraint on a table\&. If
259
is specified and the constraint does not exist, no error is thrown\&. In this case a notice is issued instead\&.
262
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
264
These forms configure the firing of trigger(s) belonging to the table\&. A disabled trigger is still known to the system, but is not executed when its triggering event occurs\&. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed\&. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints)\&. Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed\&. The trigger firing mechanism is also affected by the configuration variable
265
session_replication_role\&. Simply enabled triggers will fire when the replication role is
268
\(lqlocal\(rq\&. Triggers configured as
270
will only fire if the session is in
272
mode, and triggers configured as
274
will fire regardless of the current replication mode\&.
277
DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
279
These forms configure the firing of rewrite rules belonging to the table\&. A disabled rule is still known to the system, but is not applied during query rewriting\&. The semantics are as for disabled/enabled triggers\&. This configuration is ignored for
281
rules, which are always applied in order to keep views working even if the current session is in a non\-default replication role\&.
286
This form selects the default index for future
288
operations\&. It does not actually re\-cluster the table\&.
293
This form removes the most recently used
295
index specification from the table\&. This affects future cluster operations that don\(aqt specify an index\&.
302
system column to the table (see
303
Section 5.4, \(lqSystem Columns\(rq, in the documentation)\&. It does nothing if the table already has OIDs\&.
305
Note that this is not equivalent to
306
ADD COLUMN oid oid; that would add a normal column that happened to be named
307
oid, not a system column\&.
312
This form removes the
314
system column from the table\&. This is exactly equivalent to
315
DROP COLUMN oid RESTRICT, except that it will not complain if there is already no
320
SET ( \fIstorage_parameter\fR = \fIvalue\fR [, \&.\&.\&. ] )
322
This form changes one or more storage parameters for the table\&. See
324
for details on the available parameters\&. Note that the table contents will not be modified immediately by this command; depending on the parameter you might need to rewrite the table to get the desired effects\&. That can be done with
327
or one of the forms of
329
that forces a table rewrite\&.
335
.nr an-no-space-flag 1
346
to be specified in the
347
WITH (\fIstorage_parameter\fR)
352
as a storage parameter\&. Instead use the
356
forms to change OID status\&.
361
RESET ( \fIstorage_parameter\fR [, \&.\&.\&. ] )
363
This form resets one or more storage parameters to their defaults\&. As with
364
SET, a table rewrite might be needed to update the table entirely\&.
367
INHERIT \fIparent_table\fR
369
This form adds the target table as a new child of the specified parent table\&. Subsequently, queries against the parent will include records of the target table\&. To be added as a child, the target table must already contain all the same columns as the parent (it could have additional columns, too)\&. The columns must have matching data types, and if they have
371
constraints in the parent then they must also have
373
constraints in the child\&.
375
There must also be matching child\-table constraints for all
377
constraints of the parent\&. Currently
381
constraints are not considered, but this might change in the future\&.
384
NO INHERIT \fIparent_table\fR
386
This form removes the target table from the list of children of the specified parent table\&. Queries against the parent table will no longer include records drawn from the target table\&.
391
This form links the table to a composite type as though
393
had formed it\&. The table\(aqs list of column names and types must precisely match that of the composite type; the presence of an
395
system column is permitted to differ\&. The table must not inherit from any other table\&. These restrictions ensure that
397
would permit an equivalent table definition\&.
402
This form dissociates a typed table from its type\&.
407
This form changes the owner of the table, sequence, or view to the specified user\&.
412
This form changes the table\(aqs tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace\&. Indexes on the table, if any, are not moved; but they can be moved separately with additional
415
CREATE TABLESPACE (\fBCREATE_TABLESPACE\fR(7))\&.
422
forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table\&. There is no effect on the stored data\&.
427
This form moves the table into another schema\&. Associated indexes, constraints, and sequences owned by table columns are moved as well\&.
430
All the actions except
434
can be combined into a list of multiple alterations to apply in parallel\&. For example, it is possible to add several columns and/or alter the type of several columns in a single command\&. This is particularly useful with large tables, since only one pass over the table need be made\&.
436
You must own the table to use
437
ALTER TABLE\&. To change the schema of a table, you must also have
439
privilege on the new schema\&. To add the table as a new child of a parent table, you must own the parent table as well\&. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have
441
privilege on the table\(aqs schema\&. (These restrictions enforce that altering the owner doesn\(aqt do anything you couldn\(aqt do by dropping and recreating the table\&. However, a superuser can alter ownership of any table anyway\&.)
446
The name (possibly schema\-qualified) of an existing table to alter\&. If
448
is specified, only that table is altered\&. If
450
is not specified, the table and any descendant tables are altered\&.
455
Name of a new or existing column\&.
460
New name for an existing column\&.
465
New name for the table\&.
470
Data type of the new column, or new data type for an existing column\&.
473
\fItable_constraint\fR
475
New table constraint for the table\&.
478
\fIconstraint_name\fR
480
Name of an existing constraint to drop\&.
485
Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column)\&.
490
Refuse to drop the column or constraint if there are any dependent objects\&. This is the default behavior\&.
495
Name of a single trigger to disable or enable\&.
500
Disable or enable all triggers belonging to the table\&. (This requires superuser privilege if any of the triggers are internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints\&.)
505
Disable or enable all triggers belonging to the table except for internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints\&.
510
The index name on which the table should be marked for clustering\&.
513
\fIstorage_parameter\fR
515
The name of a table storage parameter\&.
520
The new value for a table storage parameter\&. This might be a number or a word depending on the parameter\&.
525
A parent table to associate or de\-associate with this table\&.
530
The user name of the new owner of the table\&.
535
The name of the tablespace to which the table will be moved\&.
540
The name of the schema to which the table will be moved\&.
546
is noise and can be omitted\&.
548
When a column is added with
549
ADD COLUMN, all existing rows in the table are initialized with the column\(aqs default value (NULL if no
551
clause is specified)\&.
553
Adding a column with a non\-null default or changing the type of an existing column will require the entire table and indexes to be rewritten\&. As an exception, if the
555
clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed, but any indexes on the affected columns must still be rebuilt\&. Adding or removing a system
557
column also requires rewriting the entire table\&. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space\&.
563
constraint requires scanning the table to verify that existing rows meet the constraint\&.
565
The main reason for providing the option to specify multiple changes in a single
567
is that multiple table scans or rewrites can thereby be combined into a single pass over the table\&.
571
form does not physically remove the column, but simply makes it invisible to SQL operations\&. Subsequent insert and update operations in the table will store a null value for the column\&. Thus, dropping a column is quick but it will not immediately reduce the on\-disk size of your table, as the space occupied by the dropped column is not reclaimed\&. The space will be reclaimed over time as existing rows are updated\&. (These statements do not apply when dropping the system
573
column; that is done with an immediate rewrite\&.)
575
To force an immediate rewrite of the table, you can use
578
or one of the forms of ALTER TABLE that forces a rewrite\&. This results in no semantically\-visible change in the table, but gets rid of no\-longer\-useful data\&.
584
can actually specify any expression involving the old values of the row; that is, it can refer to other columns as well as the one being converted\&. This allows very general conversions to be done with the
586
syntax\&. Because of this flexibility, the
588
expression is not applied to the column\(aqs default value (if any); the result might not be a constant expression as required for a default\&. This means that when there is no implicit or assignment cast from old to new type,
590
might fail to convert the default even though a
592
clause is supplied\&. In such cases, drop the default with
593
DROP DEFAULT, perform the
594
ALTER TYPE, and then use
596
to add a suitable new default\&. Similar considerations apply to indexes and constraints involving the column\&.
598
If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column in the parent table without doing the same to the descendants\&. That is,
600
will be rejected\&. This ensures that the descendants always have columns matching the parent\&.
604
operation will remove a descendant table\(aqs column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column\&. A nonrecursive
607
ALTER TABLE ONLY \&.\&.\&. DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited\&.
614
actions never recurse to descendant tables; that is, they always act as though
616
were specified\&. Adding a constraint can recurse only for
618
constraints, and is required to do so for such constraints\&.
620
Changing any part of a system catalog table is not permitted\&.
623
CREATE TABLE (\fBCREATE_TABLE\fR(7))
624
for a further description of valid parameters\&.
625
Chapter 5, Data Definition, in the documentation
626
has further information on inheritance\&.
629
To add a column of type
637
ALTER TABLE distributors ADD COLUMN address varchar(30);
643
To drop a column from a table:
649
ALTER TABLE distributors DROP COLUMN address RESTRICT;
655
To change the types of two existing columns in one operation:
661
ALTER TABLE distributors
662
ALTER COLUMN address TYPE varchar(80),
663
ALTER COLUMN name TYPE varchar(100);
669
To change an integer column containing UNIX timestamps to
670
timestamp with time zone
680
ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
682
timestamp with time zone \(aqepoch\(aq + foo_timestamp * interval \(aq1 second\(aq;
688
The same, when the column has a default expression that won\(aqt automatically cast to the new data type:
695
ALTER COLUMN foo_timestamp DROP DEFAULT,
696
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
698
timestamp with time zone \(aqepoch\(aq + foo_timestamp * interval \(aq1 second\(aq,
699
ALTER COLUMN foo_timestamp SET DEFAULT now();
705
To rename an existing column:
711
ALTER TABLE distributors RENAME COLUMN address TO city;
717
To rename an existing table:
723
ALTER TABLE distributors RENAME TO suppliers;
729
To add a not\-null constraint to a column:
735
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
741
To remove a not\-null constraint from a column:
747
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
753
To add a check constraint to a table and all its children:
759
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
765
To remove a check constraint from a table and all its children:
771
ALTER TABLE distributors DROP CONSTRAINT zipchk;
777
To remove a check constraint from a table only:
783
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
789
(The check constraint remains in place for any child tables\&.)
791
To add a foreign key constraint to a table:
797
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
803
To add a (multicolumn) unique constraint to a table:
809
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
815
To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:
821
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
827
To move a table to a different tablespace:
833
ALTER TABLE distributors SET TABLESPACE fasttablespace;
839
To move a table to a different schema:
845
ALTER TABLE myschema\&.distributors SET SCHEMA yourschema;
851
To recreate a primary key constraint, without blocking updates while the index is rebuilt:
857
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
858
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
859
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
874
USING) conform with the SQL standard\&. The other forms are
876
extensions of the SQL standard\&. Also, the ability to specify more than one manipulation in a single
878
command is an extension\&.
880
ALTER TABLE DROP COLUMN
881
can be used to drop the only column of a table, leaving a zero\-column table\&. This is an extension of SQL, which disallows zero\-column tables\&.
883
CREATE TABLE (\fBCREATE_TABLE\fR(7))
added
removed
doc/src/sgml/man7/ALTER_TABLE.7
