2
doc/src/sgml/ref/alter_table.sgml
3
PostgreSQL documentation
6
<refentry id="SQL-ALTERTABLE">
8
<refentrytitle>ALTER TABLE</refentrytitle>
9
<manvolnum>7</manvolnum>
10
<refmiscinfo>SQL - Language Statements</refmiscinfo>
14
<refname>ALTER TABLE</refname>
15
<refpurpose>change the definition of a table</refpurpose>
18
<indexterm zone="sql-altertable">
19
<primary>ALTER TABLE</primary>
24
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
25
<replaceable class="PARAMETER">action</replaceable> [, ... ]
26
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
27
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
28
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
29
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
30
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
31
SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
33
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
35
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
36
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
37
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ USING <replaceable class="PARAMETER">expression</replaceable> ]
38
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
39
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
40
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
41
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
42
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
43
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
44
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
45
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
46
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
47
ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
48
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
49
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
50
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
51
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
52
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
53
ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
54
DISABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
55
ENABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
56
ENABLE REPLICA RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
57
ENABLE ALWAYS RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
58
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
62
SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
63
RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
64
INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
65
NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
66
OF <replaceable class="PARAMETER">type_name</replaceable>
68
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
69
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
71
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
73
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
74
{ UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
75
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
80
<title>Description</title>
83
<command>ALTER TABLE</command> changes the definition of an existing table.
84
There are several subforms:
88
<term><literal>ADD COLUMN</literal></term>
91
This form adds a new column to the table, using the same syntax as
92
<xref linkend="SQL-CREATETABLE">.
98
<term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
101
This form drops a column from a table. Indexes and
102
table constraints involving the column will be automatically
103
dropped as well. You will need to say <literal>CASCADE</> if
104
anything outside the table depends on the column, for example,
105
foreign key references or views.
106
If <literal>IF EXISTS</literal> is specified and the column
107
does not exist, no error is thrown. In this case a notice
114
<term><literal>SET DATA TYPE</literal></term>
117
This form changes the type of a column of a table. Indexes and
118
simple table constraints involving the column will be automatically
119
converted to use the new column type by reparsing the originally
121
The optional <literal>COLLATE</literal> clause specifies a collation
122
for the new column; if omitted, the collation is the default for the
124
The optional <literal>USING</literal>
125
clause specifies how to compute the new column value from the old;
126
if omitted, the default conversion is the same as an assignment
127
cast from old data type to new. A <literal>USING</literal>
128
clause must be provided if there is no implicit or assignment
129
cast from old to new type.
135
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
138
These forms set or remove the default value for a column.
139
The default values only apply to subsequent <command>INSERT</command>
140
commands; they do not cause rows already in the table to change.
141
Defaults can also be created for views, in which case they are
142
inserted into <command>INSERT</> statements on the view before
143
the view's <literal>ON INSERT</literal> rule is applied.
149
<term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
152
These forms change whether a column is marked to allow null
153
values or to reject null values. You can only use <literal>SET
154
NOT NULL</> when the column contains no null values.
160
<term><literal>SET STATISTICS</literal></term>
164
sets the per-column statistics-gathering target for subsequent
165
<xref linkend="sql-analyze"> operations.
166
The target can be set in the range 0 to 10000; alternatively, set it
167
to -1 to revert to using the system default statistics
168
target (<xref linkend="guc-default-statistics-target">).
169
For more information on the use of statistics by the
170
<productname>PostgreSQL</productname> query planner, refer to
171
<xref linkend="planner-stats">.
177
<term><literal>SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
178
<term><literal>RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )</literal></term>
181
This form sets or resets per-attribute options. Currently, the only
182
defined per-attribute options are <literal>n_distinct</> and
183
<literal>n_distinct_inherited</>, which override the
184
number-of-distinct-values estimates made by subsequent
185
<xref linkend="sql-analyze">
186
operations. <literal>n_distinct</> affects the statistics for the table
187
itself, while <literal>n_distinct_inherited</> affects the statistics
188
gathered for the table plus its inheritance children. When set to a
189
positive value, <command>ANALYZE</> will assume that the column contains
190
exactly the specified number of distinct nonnull values. When set to a
191
negative value, which must be greater
192
than or equal to -1, <command>ANALYZE</> will assume that the number of
193
distinct nonnull values in the column is linear in the size of the
194
table; the exact count is to be computed by multiplying the estimated
195
table size by the absolute value of the given number. For example,
196
a value of -1 implies that all values in the column are distinct, while
197
a value of -0.5 implies that each value appears twice on the average.
198
This can be useful when the size of the table changes over time, since
199
the multiplication by the number of rows in the table is not performed
200
until query planning time. Specify a value of 0 to revert to estimating
201
the number of distinct values normally. For more information on the use
202
of statistics by the <productname>PostgreSQL</productname> query
203
planner, refer to <xref linkend="planner-stats">.
210
<primary>TOAST</primary>
211
<secondary>per-column storage settings</secondary>
214
<term><literal>SET STORAGE</literal></term>
217
This form sets the storage mode for a column. This controls whether this
218
column is held inline or in a secondary <acronym>TOAST</> table, and
220
should be compressed or not. <literal>PLAIN</literal> must be used
221
for fixed-length values such as <type>integer</type> and is
222
inline, uncompressed. <literal>MAIN</literal> is for inline,
223
compressible data. <literal>EXTERNAL</literal> is for external,
224
uncompressed data, and <literal>EXTENDED</literal> is for external,
225
compressed data. <literal>EXTENDED</literal> is the default for most
226
data types that support non-<literal>PLAIN</literal> storage.
227
Use of <literal>EXTERNAL</literal> will make substring operations on
228
very large <type>text</type> and <type>bytea</type> values run faster,
229
at the penalty of increased storage space. Note that
230
<literal>SET STORAGE</> doesn't itself change anything in the table,
231
it just sets the strategy to be pursued during future table updates.
232
See <xref linkend="storage-toast"> for more information.
238
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable>
239
[ NOT VALID ]</literal></term>
242
This form adds a new constraint to a table using the same syntax as
243
<xref linkend="SQL-CREATETABLE">. Newly added foreign key constraints can
244
also be defined as <literal>NOT VALID</literal> to avoid the
245
potentially lengthy initial check that must otherwise be performed.
246
Constraint checks are skipped at create table time, so
247
<xref linkend="SQL-CREATETABLE"> does not contain this option.
253
<term><literal>VALIDATE CONSTRAINT</literal></term>
256
This form validates a foreign key constraint that was previously created
257
as <literal>NOT VALID</literal>. Constraints already marked valid do not
258
cause an error response.
264
<term><literal>ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable></literal></term>
267
This form adds a new <literal>PRIMARY KEY</> or <literal>UNIQUE</>
268
constraint to a table based on an existing unique index. All the
269
columns of the index will be included in the constraint.
273
The index cannot have expression columns nor be a partial index.
274
Also, it must be a b-tree index with default sort ordering. These
275
restrictions ensure that the index is equivalent to one that would be
276
built by a regular <literal>ADD PRIMARY KEY</> or <literal>ADD UNIQUE</>
281
If <literal>PRIMARY KEY</> is specified, and the index's columns are not
282
already marked <literal>NOT NULL</>, then this command will attempt to
283
do <literal>ALTER COLUMN SET NOT NULL</> against each such column.
284
That requires a full table scan to verify the column(s) contain no
285
nulls. In all other cases, this is a fast operation.
289
If a constraint name is provided then the index will be renamed to match
290
the constraint name. Otherwise the constraint will be named the same as
295
After this command is executed, the index is <quote>owned</> by the
296
constraint, in the same way as if the index had been built by
297
a regular <literal>ADD PRIMARY KEY</> or <literal>ADD UNIQUE</>
298
command. In particular, dropping the constraint will make the index
304
Adding a constraint using an existing index can be helpful in
305
situations where a new constraint needs to be added without blocking
306
table updates for a long time. To do that, create the index using
307
<command>CREATE INDEX CONCURRENTLY</>, and then install it as an
308
official constraint using this syntax. See the example below.
315
<term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
318
This form drops the specified constraint on a table.
319
If <literal>IF EXISTS</literal> is specified and the constraint
320
does not exist, no error is thrown. In this case a notice is issued instead.
326
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
329
These forms configure the firing of trigger(s) belonging to the table.
330
A disabled trigger is still known to the system, but is not executed
331
when its triggering event occurs. For a deferred trigger, the enable
332
status is checked when the event occurs, not when the trigger function
333
is actually executed. One can disable or enable a single
334
trigger specified by name, or all triggers on the table, or only
335
user triggers (this option excludes internally generated constraint
336
triggers such as those that are used to implement foreign key
337
constraints or deferrable uniqueness and exclusion constraints).
338
Disabling or enabling internally generated constraint triggers
339
requires superuser privileges; it should be done with caution since
340
of course the integrity of the constraint cannot be guaranteed if the
341
triggers are not executed.
342
The trigger firing mechanism is also affected by the configuration
343
variable <xref linkend="guc-session-replication-role">. Simply enabled
344
triggers will fire when the replication role is <quote>origin</>
345
(the default) or <quote>local</>. Triggers configured as <literal>ENABLE
346
REPLICA</literal> will only fire if the session is in <quote>replica</>
347
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
348
fire regardless of the current replication mode.
354
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term>
357
These forms configure the firing of rewrite rules belonging to the table.
358
A disabled rule is still known to the system, but is not applied
359
during query rewriting. The semantics are as for disabled/enabled
360
triggers. This configuration is ignored for <literal>ON SELECT</literal> rules, which
361
are always applied in order to keep views working even if the current
362
session is in a non-default replication role.
368
<term><literal>CLUSTER</literal></term>
371
This form selects the default index for future
372
<xref linkend="SQL-CLUSTER">
373
operations. It does not actually re-cluster the table.
379
<term><literal>SET WITHOUT CLUSTER</literal></term>
382
This form removes the most recently used
383
<xref linkend="SQL-CLUSTER">
384
index specification from the table. This affects
385
future cluster operations that don't specify an index.
391
<term><literal>SET WITH OIDS</literal></term>
394
This form adds an <literal>oid</literal> system column to the
395
table (see <xref linkend="ddl-system-columns">).
396
It does nothing if the table already has OIDs.
400
Note that this is not equivalent to <literal>ADD COLUMN oid oid</>;
401
that would add a normal column that happened to be named
402
<literal>oid</>, not a system column.
408
<term><literal>SET WITHOUT OIDS</literal></term>
411
This form removes the <literal>oid</literal> system column from the
412
table. This is exactly equivalent to
413
<literal>DROP COLUMN oid RESTRICT</literal>,
414
except that it will not complain if there is already no
415
<literal>oid</literal> column.
421
<term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
424
This form changes one or more storage parameters for the table. See
425
<xref linkend="SQL-CREATETABLE-storage-parameters"
426
endterm="SQL-CREATETABLE-storage-parameters-title">
427
for details on the available parameters. Note that the table contents
428
will not be modified immediately by this command; depending on the
429
parameter you might need to rewrite the table to get the desired effects.
430
That can be done with <link linkend="SQL-VACUUM">VACUUM
431
FULL</>, <xref linkend="SQL-CLUSTER"> or one of the forms
432
of <command>ALTER TABLE</> that forces a table rewrite.
437
While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified
438
in the <literal>WITH (<replaceable
439
class="PARAMETER">storage_parameter</>)</literal> syntax,
440
<command>ALTER TABLE</> does not treat <literal>OIDS</> as a
441
storage parameter. Instead use the <literal>SET WITH OIDS</>
442
and <literal>SET WITHOUT OIDS</> forms to change OID status.
449
<term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
452
This form resets one or more storage parameters to their
453
defaults. As with <literal>SET</>, a table rewrite might be
454
needed to update the table entirely.
460
<term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
463
This form adds the target table as a new child of the specified parent
464
table. Subsequently, queries against the parent will include records
465
of the target table. To be added as a child, the target table must
466
already contain all the same columns as the parent (it could have
467
additional columns, too). The columns must have matching data types,
468
and if they have <literal>NOT NULL</literal> constraints in the parent
469
then they must also have <literal>NOT NULL</literal> constraints in the
474
There must also be matching child-table constraints for all
475
<literal>CHECK</literal> constraints of the parent. Currently
476
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
477
<literal>FOREIGN KEY</literal> constraints are not considered, but
478
this might change in the future.
484
<term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
487
This form removes the target table from the list of children of the
488
specified parent table.
489
Queries against the parent table will no longer include records drawn
490
from the target table.
496
<term><literal>OF <replaceable class="PARAMETER">type_name</replaceable></literal></term>
499
This form links the table to a composite type as though <command>CREATE
500
TABLE OF</> had formed it. The table's list of column names and types
501
must precisely match that of the composite type; the presence of
502
an <literal>oid</> system column is permitted to differ. The table must
503
not inherit from any other table. These restrictions ensure
504
that <command>CREATE TABLE OF</> would permit an equivalent table
511
<term><literal>NOT OF</literal></term>
514
This form dissociates a typed table from its type.
520
<term><literal>OWNER</literal></term>
523
This form changes the owner of the table, sequence, or view to the
530
<term><literal>SET TABLESPACE</literal></term>
533
This form changes the table's tablespace to the specified tablespace and
534
moves the data file(s) associated with the table to the new tablespace.
535
Indexes on the table, if any, are not moved; but they can be moved
536
separately with additional <literal>SET TABLESPACE</literal> commands.
538
<xref linkend="SQL-CREATETABLESPACE">.
544
<term><literal>RENAME</literal></term>
547
The <literal>RENAME</literal> forms change the name of a table
548
(or an index, sequence, or view) or the name of an individual column in
549
a table. There is no effect on the stored data.
555
<term><literal>SET SCHEMA</literal></term>
558
This form moves the table into another schema. Associated indexes,
559
constraints, and sequences owned by table columns are moved as well.
568
All the actions except <literal>RENAME</literal> and <literal>SET SCHEMA</>
570
a list of multiple alterations to apply in parallel. For example, it
571
is possible to add several columns and/or alter the type of several
572
columns in a single command. This is particularly useful with large
573
tables, since only one pass over the table need be made.
577
You must own the table to use <command>ALTER TABLE</>.
578
To change the schema of a table, you must also have
579
<literal>CREATE</literal> privilege on the new schema.
580
To add the table as a new child of a parent table, you must own the
581
parent table as well.
582
To alter the owner, you must also be a direct or indirect member of the new
583
owning role, and that role must have <literal>CREATE</literal> privilege on
584
the table's schema. (These restrictions enforce that altering the owner
585
doesn't do anything you couldn't do by dropping and recreating the table.
586
However, a superuser can alter ownership of any table anyway.)
591
<title>Parameters</title>
596
<term><replaceable class="PARAMETER">name</replaceable></term>
599
The name (possibly schema-qualified) of an existing table to
600
alter. If <literal>ONLY</> is specified, only that table is
601
altered. If <literal>ONLY</> is not specified, the table and any
602
descendant tables are altered.
608
<term><replaceable class="PARAMETER">column</replaceable></term>
611
Name of a new or existing column.
617
<term><replaceable class="PARAMETER">new_column</replaceable></term>
620
New name for an existing column.
626
<term><replaceable class="PARAMETER">new_name</replaceable></term>
629
New name for the table.
635
<term><replaceable class="PARAMETER">type</replaceable></term>
638
Data type of the new column, or new data type for an existing
645
<term><replaceable class="PARAMETER">table_constraint</replaceable></term>
648
New table constraint for the table.
654
<term><replaceable class="PARAMETER">constraint_name</replaceable></term>
657
Name of an existing constraint to drop.
663
<term><literal>CASCADE</literal></term>
666
Automatically drop objects that depend on the dropped column
667
or constraint (for example, views referencing the column).
673
<term><literal>RESTRICT</literal></term>
676
Refuse to drop the column or constraint if there are any dependent
677
objects. This is the default behavior.
683
<term><replaceable class="PARAMETER">trigger_name</replaceable></term>
686
Name of a single trigger to disable or enable.
692
<term><literal>ALL</literal></term>
695
Disable or enable all triggers belonging to the table.
696
(This requires superuser privilege if any of the triggers are
697
internally generated constraint triggers such as those that are used
698
to implement foreign key constraints or deferrable uniqueness and
699
exclusion constraints.)
705
<term><literal>USER</literal></term>
708
Disable or enable all triggers belonging to the table except for
709
internally generated constraint triggers such as those that are used
710
to implement foreign key constraints or deferrable uniqueness and
711
exclusion constraints.
717
<term><replaceable class="PARAMETER">index_name</replaceable></term>
720
The index name on which the table should be marked for clustering.
726
<term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
729
The name of a table storage parameter.
735
<term><replaceable class="PARAMETER">value</replaceable></term>
738
The new value for a table storage parameter.
739
This might be a number or a word depending on the parameter.
745
<term><replaceable class="PARAMETER">parent_table</replaceable></term>
748
A parent table to associate or de-associate with this table.
754
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
757
The user name of the new owner of the table.
763
<term><replaceable class="PARAMETER">new_tablespace</replaceable></term>
766
The name of the tablespace to which the table will be moved.
772
<term><replaceable class="PARAMETER">new_schema</replaceable></term>
775
The name of the schema to which the table will be moved.
787
The key word <literal>COLUMN</literal> is noise and can be omitted.
791
When a column is added with <literal>ADD COLUMN</literal>, all existing
792
rows in the table are initialized with the column's default value
793
(NULL if no <literal>DEFAULT</> clause is specified).
797
Adding a column with a non-null default or changing the type of an
798
existing column will require the entire table and indexes to be rewritten.
799
As an exception, if the <literal>USING</> clause does not change the column
800
contents and the old type is either binary coercible to the new type or
801
an unconstrained domain over the new type, a table rewrite is not needed,
802
but any indexes on the affected columns must still be rebuilt. Adding or
803
removing a system <literal>oid</> column also requires rewriting the entire
804
table. Table and/or index rebuilds may take a significant amount of time
805
for a large table; and will temporarily require as much as double the disk
810
Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
811
scanning the table to verify that existing rows meet the constraint.
815
The main reason for providing the option to specify multiple changes
816
in a single <command>ALTER TABLE</> is that multiple table scans or
817
rewrites can thereby be combined into a single pass over the table.
821
The <literal>DROP COLUMN</literal> form does not physically remove
822
the column, but simply makes it invisible to SQL operations. Subsequent
823
insert and update operations in the table will store a null value for the
824
column. Thus, dropping a column is quick but it will not immediately
825
reduce the on-disk size of your table, as the space occupied
826
by the dropped column is not reclaimed. The space will be
827
reclaimed over time as existing rows are updated. (These statements do
828
not apply when dropping the system <literal>oid</> column; that is done
829
with an immediate rewrite.)
833
To force an immediate rewrite of the table, you can use
834
<link linkend="SQL-VACUUM">VACUUM FULL</>, <xref linkend="SQL-CLUSTER">
835
or one of the forms of ALTER TABLE that forces a rewrite. This results in
836
no semantically-visible change in the table, but gets rid of
837
no-longer-useful data.
841
The <literal>USING</literal> option of <literal>SET DATA TYPE</> can actually
842
specify any expression involving the old values of the row; that is, it
843
can refer to other columns as well as the one being converted. This allows
844
very general conversions to be done with the <literal>SET DATA TYPE</>
845
syntax. Because of this flexibility, the <literal>USING</literal>
846
expression is not applied to the column's default value (if any); the
847
result might not be a constant expression as required for a default.
848
This means that when there is no implicit or assignment cast from old to
849
new type, <literal>SET DATA TYPE</> might fail to convert the default even
850
though a <literal>USING</literal> clause is supplied. In such cases,
851
drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
852
TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
853
default. Similar considerations apply to indexes and constraints involving
858
If a table has any descendant tables, it is not permitted to add,
859
rename, or change the type of a column in the parent table without doing
860
the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
861
will be rejected. This ensures that the descendants always have
862
columns matching the parent.
866
A recursive <literal>DROP COLUMN</literal> operation will remove a
867
descendant table's column only if the descendant does not inherit
868
that column from any other parents and never had an independent
869
definition of the column. A nonrecursive <literal>DROP
870
COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
871
COLUMN</command>) never removes any descendant columns, but
872
instead marks them as independently defined rather than inherited.
876
The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
877
and <literal>TABLESPACE</> actions never recurse to descendant tables;
878
that is, they always act as though <literal>ONLY</> were specified.
879
Adding a constraint can recurse only for <literal>CHECK</> constraints,
880
and is required to do so for such constraints.
884
Changing any part of a system catalog table is not permitted.
888
Refer to <xref linkend="sql-createtable"> for a further description of valid
889
parameters. <xref linkend="ddl"> has further information on
895
<title>Examples</title>
898
To add a column of type <type>varchar</type> to a table:
900
ALTER TABLE distributors ADD COLUMN address varchar(30);
905
To drop a column from a table:
907
ALTER TABLE distributors DROP COLUMN address RESTRICT;
912
To change the types of two existing columns in one operation:
914
ALTER TABLE distributors
915
ALTER COLUMN address TYPE varchar(80),
916
ALTER COLUMN name TYPE varchar(100);
921
To change an integer column containing UNIX timestamps to <type>timestamp
922
with time zone</type> via a <literal>USING</literal> clause:
925
ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
927
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
932
The same, when the column has a default expression that won't automatically
933
cast to the new data type:
936
ALTER COLUMN foo_timestamp DROP DEFAULT,
937
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
939
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
940
ALTER COLUMN foo_timestamp SET DEFAULT now();
945
To rename an existing column:
947
ALTER TABLE distributors RENAME COLUMN address TO city;
952
To rename an existing table:
954
ALTER TABLE distributors RENAME TO suppliers;
959
To add a not-null constraint to a column:
961
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
963
To remove a not-null constraint from a column:
965
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
970
To add a check constraint to a table and all its children:
972
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
977
To remove a check constraint from a table and all its children:
979
ALTER TABLE distributors DROP CONSTRAINT zipchk;
984
To remove a check constraint from a table only:
986
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
988
(The check constraint remains in place for any child tables.)
992
To add a foreign key constraint to a table:
994
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
999
To add a (multicolumn) unique constraint to a table:
1001
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
1006
To add an automatically named primary key constraint to a table, noting
1007
that a table can only ever have one primary key:
1009
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
1014
To move a table to a different tablespace:
1016
ALTER TABLE distributors SET TABLESPACE fasttablespace;
1021
To move a table to a different schema:
1023
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
1028
To recreate a primary key constraint, without blocking updates while the
1031
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
1032
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
1033
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
1040
<title>Compatibility</title>
1043
The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
1044
<literal>DROP</>, <literal>SET DEFAULT</>,
1045
and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)
1046
conform with the SQL standard. The other forms are
1047
<productname>PostgreSQL</productname> extensions of the SQL standard.
1048
Also, the ability to specify more than one manipulation in a single
1049
<command>ALTER TABLE</> command is an extension.
1053
<command>ALTER TABLE DROP COLUMN</> can be used to drop the only
1054
column of a table, leaving a zero-column table. This is an
1055
extension of SQL, which disallows zero-column tables.
1060
<title>See Also</title>
1062
<simplelist type="inline">
1063
<member><xref linkend="sql-createtable"></member>
added
removed
doc/src/sgml/ref/alter_table.sgml
