| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/alter_table.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-altertable"> |
| 7 |
<indexterm zone="sql-altertable"> |
| 8 |
<primary>ALTER TABLE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>ALTER TABLE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>ALTER TABLE</refname> |
| 19 |
<refpurpose>change the definition of a table</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] |
| 25 |
<replaceable class="parameter">action</replaceable> [, ... ] |
| 26 |
ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] |
| 27 |
RENAME [ COLUMN ] <replaceable class="parameter">column_name</replaceable> TO <replaceable class="parameter">new_column_name</replaceable> |
| 28 |
ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] |
| 29 |
RENAME CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> TO <replaceable class="parameter">new_constraint_name</replaceable> |
| 30 |
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> |
| 31 |
RENAME TO <replaceable class="parameter">new_name</replaceable> |
| 32 |
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> |
| 33 |
SET SCHEMA <replaceable class="parameter">new_schema</replaceable> |
| 34 |
ALTER TABLE ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ] |
| 35 |
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ] |
| 36 |
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> |
| 37 |
ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } |
| 38 |
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> |
| 39 |
DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ] |
| 40 |
|
| 41 |
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> |
| 42 |
|
| 43 |
ADD [ COLUMN ] [ IF NOT EXISTS ] <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] |
| 44 |
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="parameter">column_name</replaceable> [ RESTRICT | CASCADE ] |
| 45 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ USING <replaceable class="parameter">expression</replaceable> ] |
| 46 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable> |
| 47 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT |
| 48 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL |
| 49 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET EXPRESSION AS ( <replaceable class="parameter">expression</replaceable> ) |
| 50 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ] |
| 51 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
| 52 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...] |
| 53 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP IDENTITY [ IF EXISTS ] |
| 54 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STATISTICS { <replaceable class="parameter">integer</replaceable> | DEFAULT } |
| 55 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) |
| 56 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] ) |
| 57 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } |
| 58 |
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable> |
| 59 |
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ] |
| 60 |
ADD <replaceable class="parameter">table_constraint_using_index</replaceable> |
| 61 |
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] |
| 62 |
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ INHERIT | NO INHERIT ] |
| 63 |
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> |
| 64 |
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ] |
| 65 |
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ] |
| 66 |
ENABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ] |
| 67 |
ENABLE REPLICA TRIGGER <replaceable class="parameter">trigger_name</replaceable> |
| 68 |
ENABLE ALWAYS TRIGGER <replaceable class="parameter">trigger_name</replaceable> |
| 69 |
DISABLE RULE <replaceable class="parameter">rewrite_rule_name</replaceable> |
| 70 |
ENABLE RULE <replaceable class="parameter">rewrite_rule_name</replaceable> |
| 71 |
ENABLE REPLICA RULE <replaceable class="parameter">rewrite_rule_name</replaceable> |
| 72 |
ENABLE ALWAYS RULE <replaceable class="parameter">rewrite_rule_name</replaceable> |
| 73 |
DISABLE ROW LEVEL SECURITY |
| 74 |
ENABLE ROW LEVEL SECURITY |
| 75 |
FORCE ROW LEVEL SECURITY |
| 76 |
NO FORCE ROW LEVEL SECURITY |
| 77 |
CLUSTER ON <replaceable class="parameter">index_name</replaceable> |
| 78 |
SET WITHOUT CLUSTER |
| 79 |
SET WITHOUT OIDS |
| 80 |
SET ACCESS METHOD { <replaceable class="parameter">new_access_method</replaceable> | DEFAULT } |
| 81 |
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> |
| 82 |
SET { LOGGED | UNLOGGED } |
| 83 |
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) |
| 84 |
RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] ) |
| 85 |
INHERIT <replaceable class="parameter">parent_table</replaceable> |
| 86 |
NO INHERIT <replaceable class="parameter">parent_table</replaceable> |
| 87 |
OF <replaceable class="parameter">type_name</replaceable> |
| 88 |
NOT OF |
| 89 |
OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } |
| 90 |
REPLICA IDENTITY { DEFAULT | USING INDEX <replaceable class="parameter">index_name</replaceable> | FULL | NOTHING } |
| 91 |
|
| 92 |
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> |
| 93 |
|
| 94 |
IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) | |
| 95 |
FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) |
| 96 |
TO ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) | |
| 97 |
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> ) |
| 98 |
|
| 99 |
<phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase> |
| 100 |
|
| 101 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 102 |
{ NOT NULL [ NO INHERIT ] | |
| 103 |
NULL | |
| 104 |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | |
| 105 |
DEFAULT <replaceable>default_expr</replaceable> | |
| 106 |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] | |
| 107 |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] | |
| 108 |
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> | |
| 109 |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> | |
| 110 |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] |
| 111 |
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] } |
| 112 |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] |
| 113 |
|
| 114 |
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase> |
| 115 |
|
| 116 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 117 |
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | |
| 118 |
NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] | |
| 119 |
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | |
| 120 |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | |
| 121 |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] | |
| 122 |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">refcolumn</replaceable> ] ) ] |
| 123 |
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] } |
| 124 |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] |
| 125 |
|
| 126 |
<phrase>and <replaceable class="parameter">table_constraint_using_index</replaceable> is:</phrase> |
| 127 |
|
| 128 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 129 |
{ UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="parameter">index_name</replaceable> |
| 130 |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] |
| 131 |
|
| 132 |
<phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase> |
| 133 |
|
| 134 |
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] |
| 135 |
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] |
| 136 |
[ USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ] |
| 137 |
|
| 138 |
<phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> |
| 139 |
|
| 140 |
{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] |
| 141 |
|
| 142 |
<phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> |
| 143 |
|
| 144 |
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] } |
| 145 |
</synopsis> |
| 146 |
</refsynopsisdiv> |
| 147 |
|
| 148 |
<refsect1> |
| 149 |
<title>Description</title> |
| 150 |
|
| 151 |
<para> |
| 152 |
<command>ALTER TABLE</command> changes the definition of an existing table. |
| 153 |
There are several subforms described below. Note that the lock level required |
| 154 |
may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is |
| 155 |
acquired unless explicitly noted. When multiple subcommands are given, the |
| 156 |
lock acquired will be the strictest one required by any subcommand. |
| 157 |
|
| 158 |
<variablelist> |
| 159 |
<varlistentry id="sql-altertable-desc-add-column"> |
| 160 |
<term><literal>ADD [ COLUMN ] [ IF NOT EXISTS ]</literal></term> |
| 161 |
<listitem> |
| 162 |
<para> |
| 163 |
This form adds a new column to the table, using the same syntax as |
| 164 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link>. If <literal>IF NOT EXISTS</literal> |
| 165 |
is specified and a column already exists with this name, |
| 166 |
no error is thrown. |
| 167 |
</para> |
| 168 |
</listitem> |
| 169 |
</varlistentry> |
| 170 |
|
| 171 |
<varlistentry id="sql-altertable-desc-drop-column"> |
| 172 |
<term><literal>DROP [ COLUMN ] [ IF EXISTS ]</literal></term> |
| 173 |
<listitem> |
| 174 |
<para> |
| 175 |
This form drops a column from a table. Indexes and |
| 176 |
table constraints involving the column will be automatically |
| 177 |
dropped as well. |
| 178 |
Multivariate statistics referencing the dropped column will also be |
| 179 |
removed if the removal of the column would cause the statistics to |
| 180 |
contain data for only a single column. |
| 181 |
You will need to say <literal>CASCADE</literal> if anything outside the table |
| 182 |
depends on the column, for example, foreign key references or views. |
| 183 |
If <literal>IF EXISTS</literal> is specified and the column |
| 184 |
does not exist, no error is thrown. In this case a notice |
| 185 |
is issued instead. |
| 186 |
</para> |
| 187 |
</listitem> |
| 188 |
</varlistentry> |
| 189 |
|
| 190 |
<varlistentry id="sql-altertable-desc-set-data-type"> |
| 191 |
<term><literal>SET DATA TYPE</literal></term> |
| 192 |
<listitem> |
| 193 |
<para> |
| 194 |
This form changes the type of a column of a table. Indexes and |
| 195 |
simple table constraints involving the column will be automatically |
| 196 |
converted to use the new column type by reparsing the originally |
| 197 |
supplied expression. |
| 198 |
The optional <literal>COLLATE</literal> clause specifies a collation |
| 199 |
for the new column; if omitted, the collation is the default for the |
| 200 |
new column type. |
| 201 |
The optional <literal>USING</literal> |
| 202 |
clause specifies how to compute the new column value from the old; |
| 203 |
if omitted, the default conversion is the same as an assignment |
| 204 |
cast from old data type to new. A <literal>USING</literal> |
| 205 |
clause must be provided if there is no implicit or assignment |
| 206 |
cast from old to new type. |
| 207 |
</para> |
| 208 |
|
| 209 |
<para> |
| 210 |
When this form is used, the column's statistics are removed, |
| 211 |
so running <link linkend="sql-analyze"><command>ANALYZE</command></link> |
| 212 |
on the table afterwards is recommended. |
| 213 |
For a virtual generated column, <command>ANALYZE</command> |
| 214 |
is not necessary because such columns never have statistics. |
| 215 |
</para> |
| 216 |
</listitem> |
| 217 |
</varlistentry> |
| 218 |
|
| 219 |
<varlistentry id="sql-altertable-desc-set-drop-default"> |
| 220 |
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term> |
| 221 |
<listitem> |
| 222 |
<para> |
| 223 |
These forms set or remove the default value for a column (where |
| 224 |
removal is equivalent to setting the default value to NULL). The new |
| 225 |
default value will only apply in subsequent <command>INSERT</command> |
| 226 |
or <command>UPDATE</command> commands; it does not cause rows already |
| 227 |
in the table to change. |
| 228 |
</para> |
| 229 |
</listitem> |
| 230 |
</varlistentry> |
| 231 |
|
| 232 |
<varlistentry id="sql-altertable-desc-set-drop-not-null"> |
| 233 |
<term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term> |
| 234 |
<listitem> |
| 235 |
<para> |
| 236 |
These forms change whether a column is marked to allow null |
| 237 |
values or to reject null values. |
| 238 |
</para> |
| 239 |
|
| 240 |
<para> |
| 241 |
<literal>SET NOT NULL</literal> may only be applied to a column |
| 242 |
provided none of the records in the table contain a |
| 243 |
<literal>NULL</literal> value for the column. Ordinarily this is |
| 244 |
checked during the <literal>ALTER TABLE</literal> by scanning the |
| 245 |
entire table, unless <literal>NOT VALID</literal> is specified; |
| 246 |
however, if a valid <literal>CHECK</literal> constraint exists |
| 247 |
(and is not dropped in the same command) which proves no |
| 248 |
<literal>NULL</literal> can exist, then the table scan is skipped. |
| 249 |
If a column has an invalid not-null constraint, |
| 250 |
<literal>SET NOT NULL</literal> validates it. |
| 251 |
</para> |
| 252 |
|
| 253 |
<para> |
| 254 |
If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal> |
| 255 |
on a column if it is marked <literal>NOT NULL</literal> in the parent |
| 256 |
table. To drop the <literal>NOT NULL</literal> constraint from all the |
| 257 |
partitions, perform <literal>DROP NOT NULL</literal> on the parent |
| 258 |
table. Even if there is no <literal>NOT NULL</literal> constraint on the |
| 259 |
parent, such a constraint can still be added to individual partitions, |
| 260 |
if desired; that is, the children can disallow nulls even if the parent |
| 261 |
allows them, but not the other way around. It is also possible to drop |
| 262 |
the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal> |
| 263 |
the parent table, which does not remove it from the children. |
| 264 |
</para> |
| 265 |
</listitem> |
| 266 |
</varlistentry> |
| 267 |
|
| 268 |
<varlistentry id="sql-altertable-desc-set-expression"> |
| 269 |
<term><literal>SET EXPRESSION AS</literal></term> |
| 270 |
<listitem> |
| 271 |
<para> |
| 272 |
This form replaces the expression of a generated column. Existing data |
| 273 |
in a stored generated column is rewritten and all the future changes |
| 274 |
will apply the new generation expression. |
| 275 |
</para> |
| 276 |
|
| 277 |
<para> |
| 278 |
When this form is used on a stored generated column, its statistics |
| 279 |
are removed, so running |
| 280 |
<link linkend="sql-analyze"><command>ANALYZE</command></link> |
| 281 |
on the table afterwards is recommended. |
| 282 |
For a virtual generated column, <command>ANALYZE</command> |
| 283 |
is not necessary because such columns never have statistics. |
| 284 |
</para> |
| 285 |
</listitem> |
| 286 |
</varlistentry> |
| 287 |
|
| 288 |
<varlistentry id="sql-altertable-desc-drop-expression"> |
| 289 |
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term> |
| 290 |
<listitem> |
| 291 |
<para> |
| 292 |
This form turns a stored generated column into a normal base column. |
| 293 |
Existing data in the columns is retained, but future changes will no |
| 294 |
longer apply the generation expression. |
| 295 |
</para> |
| 296 |
|
| 297 |
<para> |
| 298 |
This form is currently only supported for stored generated columns (not |
| 299 |
virtual ones). |
| 300 |
</para> |
| 301 |
|
| 302 |
<para> |
| 303 |
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the |
| 304 |
column is not a generated column, no error is thrown. In this case a |
| 305 |
notice is issued instead. |
| 306 |
</para> |
| 307 |
</listitem> |
| 308 |
</varlistentry> |
| 309 |
|
| 310 |
<varlistentry id="sql-altertable-desc-generated-identity"> |
| 311 |
<term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term> |
| 312 |
<term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term> |
| 313 |
<term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term> |
| 314 |
<listitem> |
| 315 |
<para> |
| 316 |
These forms change whether a column is an identity column or change the |
| 317 |
generation attribute of an existing identity column. |
| 318 |
See <link linkend="sql-createtable"><command>CREATE TABLE</command></link> for details. |
| 319 |
Like <literal>SET DEFAULT</literal>, these forms only affect the |
| 320 |
behavior of subsequent <command>INSERT</command> |
| 321 |
and <command>UPDATE</command> commands; they do not cause rows |
| 322 |
already in the table to change. |
| 323 |
</para> |
| 324 |
|
| 325 |
<para> |
| 326 |
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the |
| 327 |
column is not an identity column, no error is thrown. In this case a |
| 328 |
notice is issued instead. |
| 329 |
</para> |
| 330 |
</listitem> |
| 331 |
</varlistentry> |
| 332 |
|
| 333 |
<varlistentry id="sql-altertable-desc-set-sequence-option"> |
| 334 |
<term><literal>SET <replaceable>sequence_option</replaceable></literal></term> |
| 335 |
<term><literal>RESTART</literal></term> |
| 336 |
<listitem> |
| 337 |
<para> |
| 338 |
These forms alter the sequence that underlies an existing identity |
| 339 |
column. <replaceable>sequence_option</replaceable> is an option |
| 340 |
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such |
| 341 |
as <literal>INCREMENT BY</literal>. |
| 342 |
</para> |
| 343 |
</listitem> |
| 344 |
</varlistentry> |
| 345 |
|
| 346 |
<varlistentry id="sql-altertable-desc-set-statistics"> |
| 347 |
<term><literal>SET STATISTICS</literal></term> |
| 348 |
<listitem> |
| 349 |
<para> |
| 350 |
This form |
| 351 |
sets the per-column statistics-gathering target for subsequent |
| 352 |
<link linkend="sql-analyze"><command>ANALYZE</command></link> operations. |
| 353 |
The target can be set in the range 0 to 10000. Set it |
| 354 |
to <literal>DEFAULT</literal> to revert to using the system default |
| 355 |
statistics target (<xref linkend="guc-default-statistics-target"/>). |
| 356 |
(Setting to a value of -1 is an obsolete way spelling to get the same |
| 357 |
outcome.) |
| 358 |
For more information on the use of statistics by the |
| 359 |
<productname>PostgreSQL</productname> query planner, refer to |
| 360 |
<xref linkend="planner-stats"/>. |
| 361 |
</para> |
| 362 |
<para> |
| 363 |
<literal>SET STATISTICS</literal> acquires a |
| 364 |
<literal>SHARE UPDATE EXCLUSIVE</literal> lock. |
| 365 |
</para> |
| 366 |
</listitem> |
| 367 |
</varlistentry> |
| 368 |
|
| 369 |
<varlistentry id="sql-altertable-desc-set-attribute-option"> |
| 370 |
<term><literal>SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )</literal></term> |
| 371 |
<term><literal>RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )</literal></term> |
| 372 |
<listitem> |
| 373 |
<para> |
| 374 |
This form sets or resets per-attribute options. Currently, the only |
| 375 |
defined per-attribute options are <literal>n_distinct</literal> and |
| 376 |
<literal>n_distinct_inherited</literal>, which override the |
| 377 |
number-of-distinct-values estimates made by subsequent |
| 378 |
<link linkend="sql-analyze"><command>ANALYZE</command></link> |
| 379 |
operations. <literal>n_distinct</literal> affects the statistics for the |
| 380 |
table itself, while <literal>n_distinct_inherited</literal> affects the |
| 381 |
statistics gathered for the table plus its inheritance children, and for |
| 382 |
the statistics gathered for partitioned tables. When the value |
| 383 |
specified is a positive value, the query planner will assume that the |
| 384 |
column contains exactly the specified number of distinct nonnull values. |
| 385 |
Fractional values may also be specified by using values below 0 and |
| 386 |
above or equal to -1. This instructs the query planner to estimate the |
| 387 |
number of distinct values by multiplying the absolute value of the |
| 388 |
specified number by the estimated number of rows in the table. For |
| 389 |
example, a value of -1 implies that all values in the column are |
| 390 |
distinct, while a value of -0.5 implies that each value appears twice on |
| 391 |
average. This can be useful when the size of the table changes over |
| 392 |
time. For more information on the use of statistics by the |
| 393 |
<productname>PostgreSQL</productname> query planner, refer to |
| 394 |
<xref linkend="planner-stats"/>. |
| 395 |
</para> |
| 396 |
<para> |
| 397 |
Changing per-attribute options acquires a |
| 398 |
<literal>SHARE UPDATE EXCLUSIVE</literal> lock. |
| 399 |
</para> |
| 400 |
</listitem> |
| 401 |
</varlistentry> |
| 402 |
|
| 403 |
<varlistentry id="sql-altertable-desc-set-storage"> |
| 404 |
<term> |
| 405 |
<literal>SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</literal> |
| 406 |
<indexterm> |
| 407 |
<primary>TOAST</primary> |
| 408 |
<secondary>per-column storage settings</secondary> |
| 409 |
</indexterm> |
| 410 |
</term> |
| 411 |
<listitem> |
| 412 |
<para> |
| 413 |
This form sets the storage mode for a column. This controls whether this |
| 414 |
column is held inline or in a secondary <acronym>TOAST</acronym> table, |
| 415 |
and whether the data |
| 416 |
should be compressed or not. <literal>PLAIN</literal> must be used |
| 417 |
for fixed-length values such as <type>integer</type> and is |
| 418 |
inline, uncompressed. <literal>MAIN</literal> is for inline, |
| 419 |
compressible data. <literal>EXTERNAL</literal> is for external, |
| 420 |
uncompressed data, and <literal>EXTENDED</literal> is for external, |
| 421 |
compressed data. |
| 422 |
Writing <literal>DEFAULT</literal> sets the storage mode to the default |
| 423 |
mode for the column's data type. <literal>EXTENDED</literal> is the |
| 424 |
default for most data types that support non-<literal>PLAIN</literal> |
| 425 |
storage. |
| 426 |
Use of <literal>EXTERNAL</literal> will make substring operations on |
| 427 |
very large <type>text</type> and <type>bytea</type> values run faster, |
| 428 |
at the penalty of increased storage space. |
| 429 |
Note that <literal>ALTER TABLE ... SET STORAGE</literal> doesn't itself |
| 430 |
change anything in the table; it just sets the strategy to be pursued |
| 431 |
during future table updates. |
| 432 |
See <xref linkend="storage-toast"/> for more information. |
| 433 |
</para> |
| 434 |
</listitem> |
| 435 |
</varlistentry> |
| 436 |
|
| 437 |
<varlistentry id="sql-altertable-desc-set-compression"> |
| 438 |
<term> |
| 439 |
<literal>SET COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal> |
| 440 |
</term> |
| 441 |
<listitem> |
| 442 |
<para> |
| 443 |
This form sets the compression method for a column, determining how |
| 444 |
values inserted in future will be compressed (if the storage mode |
| 445 |
permits compression at all). |
| 446 |
This does not cause the table to be rewritten, so existing data may still |
| 447 |
be compressed with other compression methods. If the table is restored |
| 448 |
with <application>pg_restore</application>, then all values are rewritten |
| 449 |
with the configured compression method. |
| 450 |
However, when data is inserted from another relation (for example, |
| 451 |
by <command>INSERT ... SELECT</command>), values from the source table are |
| 452 |
not necessarily detoasted, so any previously compressed data may retain |
| 453 |
its existing compression method, rather than being recompressed with the |
| 454 |
compression method of the target column. |
| 455 |
The supported compression |
| 456 |
methods are <literal>pglz</literal> and <literal>lz4</literal>. |
| 457 |
(<literal>lz4</literal> is available only if <option>--with-lz4</option> |
| 458 |
was used when building <productname>PostgreSQL</productname>.) In |
| 459 |
addition, <replaceable class="parameter">compression_method</replaceable> |
| 460 |
can be <literal>default</literal>, which selects the default behavior of |
| 461 |
consulting the <xref linkend="guc-default-toast-compression"/> setting |
| 462 |
at the time of data insertion to determine the method to use. |
| 463 |
</para> |
| 464 |
</listitem> |
| 465 |
</varlistentry> |
| 466 |
|
| 467 |
<varlistentry id="sql-altertable-desc-add-table-constraint"> |
| 468 |
<term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term> |
| 469 |
<listitem> |
| 470 |
<para> |
| 471 |
This form adds a new constraint to a table using the same constraint |
| 472 |
syntax as <link linkend="sql-createtable"><command>CREATE TABLE</command></link>, plus the option <literal>NOT |
| 473 |
VALID</literal>, which is currently only allowed for foreign-key, |
| 474 |
<literal>CHECK</literal>, and not-null constraints. |
| 475 |
</para> |
| 476 |
|
| 477 |
<para> |
| 478 |
Normally, this form will cause a scan of the table to verify that all |
| 479 |
existing rows in the table satisfy the new constraint. But if |
| 480 |
the <literal>NOT VALID</literal> option is used, this |
| 481 |
potentially-lengthy scan is skipped. The constraint will still be |
| 482 |
applied against subsequent inserts or updates (that is, they'll fail |
| 483 |
unless there is a matching row in the referenced table, in the case |
| 484 |
of foreign keys, or they'll fail unless the new row matches the |
| 485 |
specified check condition). But the |
| 486 |
database will not assume that the constraint holds for all rows in |
| 487 |
the table, until it is validated by using the <literal>VALIDATE |
| 488 |
CONSTRAINT</literal> option. |
| 489 |
See <xref linkend="sql-altertable-notes"/> below for more information |
| 490 |
about using the <literal>NOT VALID</literal> option. |
| 491 |
</para> |
| 492 |
|
| 493 |
<para> |
| 494 |
Although most forms of <literal>ADD |
| 495 |
<replaceable class="parameter">table_constraint</replaceable></literal> |
| 496 |
require an <literal>ACCESS EXCLUSIVE</literal> lock, <literal>ADD |
| 497 |
FOREIGN KEY</literal> requires only a <literal>SHARE ROW |
| 498 |
EXCLUSIVE</literal> lock. Note that <literal>ADD FOREIGN KEY</literal> |
| 499 |
also acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the |
| 500 |
referenced table, in addition to the lock on the table on which the |
| 501 |
constraint is declared. |
| 502 |
</para> |
| 503 |
|
| 504 |
<para> |
| 505 |
Additional restrictions apply when unique or primary key constraints |
| 506 |
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>. |
| 507 |
</para> |
| 508 |
|
| 509 |
</listitem> |
| 510 |
</varlistentry> |
| 511 |
|
| 512 |
<varlistentry id="sql-altertable-desc-add-table-constraint-using-index"> |
| 513 |
<term><literal>ADD <replaceable class="parameter">table_constraint_using_index</replaceable></literal></term> |
| 514 |
<listitem> |
| 515 |
<para> |
| 516 |
This form adds a new <literal>PRIMARY KEY</literal> or <literal>UNIQUE</literal> |
| 517 |
constraint to a table based on an existing unique index. All the |
| 518 |
columns of the index will be included in the constraint. |
| 519 |
</para> |
| 520 |
|
| 521 |
<para> |
| 522 |
The index cannot have expression columns nor be a partial index. |
| 523 |
Also, it must be a b-tree index with default sort ordering. These |
| 524 |
restrictions ensure that the index is equivalent to one that would be |
| 525 |
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal> |
| 526 |
command. |
| 527 |
</para> |
| 528 |
|
| 529 |
<para> |
| 530 |
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not |
| 531 |
already marked <literal>NOT NULL</literal>, then this command will attempt to |
| 532 |
do <literal>ALTER COLUMN SET NOT NULL</literal> against each such column. |
| 533 |
That requires a full table scan to verify the column(s) contain no |
| 534 |
nulls. In all other cases, this is a fast operation. |
| 535 |
</para> |
| 536 |
|
| 537 |
<para> |
| 538 |
If a constraint name is provided then the index will be renamed to match |
| 539 |
the constraint name. Otherwise the constraint will be named the same as |
| 540 |
the index. |
| 541 |
</para> |
| 542 |
|
| 543 |
<para> |
| 544 |
After this command is executed, the index is <quote>owned</quote> by the |
| 545 |
constraint, in the same way as if the index had been built by |
| 546 |
a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal> |
| 547 |
command. In particular, dropping the constraint will make the index |
| 548 |
disappear too. |
| 549 |
</para> |
| 550 |
|
| 551 |
<para> |
| 552 |
This form is not currently supported on partitioned tables. |
| 553 |
</para> |
| 554 |
|
| 555 |
<note> |
| 556 |
<para> |
| 557 |
Adding a constraint using an existing index can be helpful in |
| 558 |
situations where a new constraint needs to be added without blocking |
| 559 |
table updates for a long time. To do that, create the index using |
| 560 |
<command>CREATE UNIQUE INDEX CONCURRENTLY</command>, and then convert it to a |
| 561 |
constraint using this syntax. See the example below. |
| 562 |
</para> |
| 563 |
</note> |
| 564 |
</listitem> |
| 565 |
</varlistentry> |
| 566 |
|
| 567 |
<varlistentry id="sql-altertable-desc-alter-constraint"> |
| 568 |
<term><literal>ALTER CONSTRAINT</literal></term> |
| 569 |
<listitem> |
| 570 |
<para> |
| 571 |
This form alters the attributes of a constraint that was previously |
| 572 |
created. Currently only foreign key constraints may be altered in |
| 573 |
this fashion, but see below. |
| 574 |
</para> |
| 575 |
</listitem> |
| 576 |
</varlistentry> |
| 577 |
|
| 578 |
<varlistentry id="sql-altertable-desc-alter-constraint-inherit"> |
| 579 |
<term><literal>ALTER CONSTRAINT ... INHERIT</literal></term> |
| 580 |
<term><literal>ALTER CONSTRAINT ... NO INHERIT</literal></term> |
| 581 |
<listitem> |
| 582 |
<para> |
| 583 |
These forms modify a inheritable constraint so that it becomes not |
| 584 |
inheritable, or vice-versa. Only not-null constraints may be altered |
| 585 |
in this fashion at present. |
| 586 |
In addition to changing the inheritability status of the constraint, |
| 587 |
in the case where a non-inheritable constraint is being marked |
| 588 |
inheritable, if the table has children, an equivalent constraint |
| 589 |
will be added to them. If marking an inheritable constraint as |
| 590 |
non-inheritable on a table with children, then the corresponding |
| 591 |
constraint on children will be marked as no longer inherited, |
| 592 |
but not removed. |
| 593 |
</para> |
| 594 |
</listitem> |
| 595 |
</varlistentry> |
| 596 |
|
| 597 |
<varlistentry id="sql-altertable-desc-validate-constraint"> |
| 598 |
<term><literal>VALIDATE CONSTRAINT</literal></term> |
| 599 |
<listitem> |
| 600 |
<para> |
| 601 |
This form validates a foreign key, check, or not-null constraint that was |
| 602 |
previously created as <literal>NOT VALID</literal>, by scanning the |
| 603 |
table to ensure there are no rows for which the constraint is not |
| 604 |
satisfied. If the constraint was set to <literal>NOT ENFORCED</literal>, an error is thrown. |
| 605 |
Nothing happens if the constraint is already marked valid. |
| 606 |
(See <xref linkend="sql-altertable-notes"/> below for an explanation |
| 607 |
of the usefulness of this command.) |
| 608 |
</para> |
| 609 |
<para> |
| 610 |
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. |
| 611 |
</para> |
| 612 |
</listitem> |
| 613 |
</varlistentry> |
| 614 |
|
| 615 |
<varlistentry id="sql-altertable-desc-drop-constraint"> |
| 616 |
<term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term> |
| 617 |
<listitem> |
| 618 |
<para> |
| 619 |
This form drops the specified constraint on a table, along with |
| 620 |
any index underlying the constraint. |
| 621 |
If <literal>IF EXISTS</literal> is specified and the constraint |
| 622 |
does not exist, no error is thrown. In this case a notice is issued instead. |
| 623 |
</para> |
| 624 |
</listitem> |
| 625 |
</varlistentry> |
| 626 |
|
| 627 |
<varlistentry id="sql-altertable-desc-disable-enable-trigger"> |
| 628 |
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term> |
| 629 |
<listitem> |
| 630 |
<para> |
| 631 |
These forms configure the firing of trigger(s) belonging to the table. |
| 632 |
A disabled trigger is still known to the system, but is not executed |
| 633 |
when its triggering event occurs. (For a deferred trigger, the enable |
| 634 |
status is checked when the event occurs, not when the trigger function |
| 635 |
is actually executed.) One can disable or enable a single |
| 636 |
trigger specified by name, or all triggers on the table, or only |
| 637 |
user triggers (this option excludes internally generated constraint |
| 638 |
triggers, such as those that are used to implement foreign key |
| 639 |
constraints or deferrable uniqueness and exclusion constraints). |
| 640 |
Disabling or enabling internally generated constraint triggers |
| 641 |
requires superuser privileges; it should be done with caution since |
| 642 |
of course the integrity of the constraint cannot be guaranteed if the |
| 643 |
triggers are not executed. |
| 644 |
</para> |
| 645 |
|
| 646 |
<para> |
| 647 |
The trigger firing mechanism is also affected by the configuration |
| 648 |
variable <xref linkend="guc-session-replication-role"/>. Simply enabled |
| 649 |
triggers (the default) will fire when the replication role is <quote>origin</quote> |
| 650 |
(the default) or <quote>local</quote>. Triggers configured as <literal>ENABLE |
| 651 |
REPLICA</literal> will only fire if the session is in <quote>replica</quote> |
| 652 |
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will |
| 653 |
fire regardless of the current replication role. |
| 654 |
</para> |
| 655 |
|
| 656 |
<para> |
| 657 |
The effect of this mechanism is that in the default configuration, |
| 658 |
triggers do not fire on replicas. This is useful because if a trigger |
| 659 |
is used on the origin to propagate data between tables, then the |
| 660 |
replication system will also replicate the propagated data; so the |
| 661 |
trigger should not fire a second time on the replica, because that would |
| 662 |
lead to duplication. However, if a trigger is used for another purpose |
| 663 |
such as creating external alerts, then it might be appropriate to set it |
| 664 |
to <literal>ENABLE ALWAYS</literal> so that it is also fired on |
| 665 |
replicas. |
| 666 |
</para> |
| 667 |
|
| 668 |
<para> |
| 669 |
When this command is applied to a partitioned table, the states of |
| 670 |
corresponding clone triggers in the partitions are updated too, |
| 671 |
unless <literal>ONLY</literal> is specified. |
| 672 |
</para> |
| 673 |
|
| 674 |
<para> |
| 675 |
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock. |
| 676 |
</para> |
| 677 |
</listitem> |
| 678 |
</varlistentry> |
| 679 |
|
| 680 |
<varlistentry id="sql-altertable-desc-disable-enable-rule"> |
| 681 |
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term> |
| 682 |
<listitem> |
| 683 |
<para> |
| 684 |
These forms configure the firing of rewrite rules belonging to the table. |
| 685 |
A disabled rule is still known to the system, but is not applied |
| 686 |
during query rewriting. The semantics are as for disabled/enabled |
| 687 |
triggers. This configuration is ignored for <literal>ON SELECT</literal> rules, which |
| 688 |
are always applied in order to keep views working even if the current |
| 689 |
session is in a non-default replication role. |
| 690 |
</para> |
| 691 |
|
| 692 |
<para> |
| 693 |
The rule firing mechanism is also affected by the configuration variable |
| 694 |
<xref linkend="guc-session-replication-role"/>, analogous to triggers as |
| 695 |
described above. |
| 696 |
</para> |
| 697 |
</listitem> |
| 698 |
</varlistentry> |
| 699 |
|
| 700 |
<varlistentry id="sql-altertable-desc-disable-enable-row-level-security"> |
| 701 |
<term><literal>DISABLE</literal>/<literal>ENABLE ROW LEVEL SECURITY</literal></term> |
| 702 |
<listitem> |
| 703 |
<para> |
| 704 |
These forms control the application of row security policies belonging |
| 705 |
to the table. If enabled and no policies exist for the table, then a |
| 706 |
default-deny policy is applied. Note that policies can exist for a table |
| 707 |
even if row-level security is disabled. In this case, the policies will |
| 708 |
<emphasis>not</emphasis> be applied and the policies will be ignored. |
| 709 |
See also |
| 710 |
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>. |
| 711 |
</para> |
| 712 |
</listitem> |
| 713 |
</varlistentry> |
| 714 |
|
| 715 |
<varlistentry id="sql-altertable-desc-force-row-level-security"> |
| 716 |
<term><literal>NO FORCE</literal>/<literal>FORCE ROW LEVEL SECURITY</literal></term> |
| 717 |
<listitem> |
| 718 |
<para> |
| 719 |
These forms control the application of row security policies belonging |
| 720 |
to the table when the user is the table owner. If enabled, row-level |
| 721 |
security policies will be applied when the user is the table owner. If |
| 722 |
disabled (the default) then row-level security will not be applied when |
| 723 |
the user is the table owner. |
| 724 |
See also |
| 725 |
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>. |
| 726 |
</para> |
| 727 |
</listitem> |
| 728 |
</varlistentry> |
| 729 |
|
| 730 |
<varlistentry id="sql-altertable-desc-cluster-on"> |
| 731 |
<term><literal>CLUSTER ON</literal></term> |
| 732 |
<listitem> |
| 733 |
<para> |
| 734 |
This form selects the default index for future |
| 735 |
<link linkend="sql-cluster"><command>CLUSTER</command></link> |
| 736 |
operations. It does not actually re-cluster the table. |
| 737 |
</para> |
| 738 |
<para> |
| 739 |
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. |
| 740 |
</para> |
| 741 |
</listitem> |
| 742 |
</varlistentry> |
| 743 |
|
| 744 |
<varlistentry id="sql-altertable-desc-set-without-cluster"> |
| 745 |
<term><literal>SET WITHOUT CLUSTER</literal></term> |
| 746 |
<listitem> |
| 747 |
<para> |
| 748 |
This form removes the most recently used |
| 749 |
<link linkend="sql-cluster"><command>CLUSTER</command></link> |
| 750 |
index specification from the table. This affects |
| 751 |
future cluster operations that don't specify an index. |
| 752 |
</para> |
| 753 |
<para> |
| 754 |
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. |
| 755 |
</para> |
| 756 |
</listitem> |
| 757 |
</varlistentry> |
| 758 |
|
| 759 |
<varlistentry id="sql-altertable-desc-set-without-oids"> |
| 760 |
<term><literal>SET WITHOUT OIDS</literal></term> |
| 761 |
<listitem> |
| 762 |
<para> |
| 763 |
Backward-compatible syntax for removing the <literal>oid</literal> |
| 764 |
system column. As <literal>oid</literal> system columns cannot be |
| 765 |
added anymore, this never has an effect. |
| 766 |
</para> |
| 767 |
</listitem> |
| 768 |
</varlistentry> |
| 769 |
|
| 770 |
<varlistentry id="sql-altertable-desc-set-access-method"> |
| 771 |
<term><literal>SET ACCESS METHOD</literal></term> |
| 772 |
<listitem> |
| 773 |
<para> |
| 774 |
This form changes the access method of the table by rewriting it |
| 775 |
using the indicated access method; specifying |
| 776 |
<literal>DEFAULT</literal> selects the access method set as the |
| 777 |
<xref linkend="guc-default-table-access-method"/> configuration |
| 778 |
parameter. |
| 779 |
See <xref linkend="tableam"/> for more information. |
| 780 |
</para> |
| 781 |
<para> |
| 782 |
When applied to a partitioned table, there is no data to rewrite, |
| 783 |
but partitions created afterwards will default to the given access |
| 784 |
method unless overridden by a <literal>USING</literal> clause. |
| 785 |
Specifying <varname>DEFAULT</varname> removes a previous value, |
| 786 |
causing future partitions to default to |
| 787 |
<varname>default_table_access_method</varname>. |
| 788 |
</para> |
| 789 |
</listitem> |
| 790 |
</varlistentry> |
| 791 |
|
| 792 |
<varlistentry id="sql-altertable-desc-set-tablespace"> |
| 793 |
<term><literal>SET TABLESPACE</literal></term> |
| 794 |
<listitem> |
| 795 |
<para> |
| 796 |
This form changes the table's tablespace to the specified tablespace and |
| 797 |
moves the data file(s) associated with the table to the new tablespace. |
| 798 |
Indexes on the table, if any, are not moved; but they can be moved |
| 799 |
separately with additional <literal>SET TABLESPACE</literal> commands. |
| 800 |
When applied to a partitioned table, nothing is moved, but any |
| 801 |
partitions created afterwards with |
| 802 |
<command>CREATE TABLE PARTITION OF</command> will use that tablespace, |
| 803 |
unless overridden by a <literal>TABLESPACE</literal> clause. |
| 804 |
</para> |
| 805 |
|
| 806 |
<para> |
| 807 |
All tables in the current database in a tablespace can be moved by using |
| 808 |
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables |
| 809 |
to be moved first and then move each one. This form also supports |
| 810 |
<literal>OWNED BY</literal>, which will only move tables owned by the |
| 811 |
roles specified. If the <literal>NOWAIT</literal> option is specified |
| 812 |
then the command will fail if it is unable to acquire all of the locks |
| 813 |
required immediately. Note that system catalogs are not moved by this |
| 814 |
command; use <command>ALTER DATABASE</command> or explicit |
| 815 |
<command>ALTER TABLE</command> invocations instead if desired. The |
| 816 |
<literal>information_schema</literal> relations are not considered part |
| 817 |
of the system catalogs and will be moved. |
| 818 |
See also |
| 819 |
<link linkend="sql-createtablespace"><command>CREATE TABLESPACE</command></link>. |
| 820 |
</para> |
| 821 |
</listitem> |
| 822 |
</varlistentry> |
| 823 |
|
| 824 |
<varlistentry id="sql-altertable-desc-set-logged-unlogged"> |
| 825 |
<term><literal>SET { LOGGED | UNLOGGED }</literal></term> |
| 826 |
<listitem> |
| 827 |
<para> |
| 828 |
This form changes the table from unlogged to logged or vice-versa |
| 829 |
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied |
| 830 |
to a temporary table. |
| 831 |
</para> |
| 832 |
|
| 833 |
<para> |
| 834 |
This also changes the persistence of any sequences linked to the table |
| 835 |
(for identity or serial columns). However, it is also possible to |
| 836 |
change the persistence of such sequences separately. |
| 837 |
</para> |
| 838 |
|
| 839 |
<para> |
| 840 |
This form is not supported for partitioned tables. |
| 841 |
</para> |
| 842 |
</listitem> |
| 843 |
</varlistentry> |
| 844 |
|
| 845 |
<varlistentry id="sql-altertable-desc-set-storage-parameter"> |
| 846 |
<term><literal>SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> |
| 847 |
<listitem> |
| 848 |
<para> |
| 849 |
This form changes one or more storage parameters for the table. See |
| 850 |
<xref linkend="sql-createtable-storage-parameters"/> in the |
| 851 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link> documentation |
| 852 |
for details on the available parameters. Note that the table contents |
| 853 |
will not be modified immediately by this command; depending on the |
| 854 |
parameter you might need to rewrite the table to get the desired effects. |
| 855 |
That can be done with <link linkend="sql-vacuum"><command>VACUUM |
| 856 |
FULL</command></link>, <link linkend="sql-cluster"><command>CLUSTER</command></link> or one of the forms |
| 857 |
of <command>ALTER TABLE</command> that forces a table rewrite. |
| 858 |
For planner related parameters, changes will take effect from the next |
| 859 |
time the table is locked so currently executing queries will not be |
| 860 |
affected. |
| 861 |
</para> |
| 862 |
|
| 863 |
<para> |
| 864 |
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for |
| 865 |
fillfactor, toast and autovacuum storage parameters, as well as the |
| 866 |
planner parameter <varname>parallel_workers</varname>. |
| 867 |
</para> |
| 868 |
</listitem> |
| 869 |
</varlistentry> |
| 870 |
|
| 871 |
<varlistentry id="sql-altertable-desc-reset-storage-parameter"> |
| 872 |
<term><literal>RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )</literal></term> |
| 873 |
<listitem> |
| 874 |
<para> |
| 875 |
This form resets one or more storage parameters to their |
| 876 |
defaults. As with <literal>SET</literal>, a table rewrite might be |
| 877 |
needed to update the table entirely. |
| 878 |
</para> |
| 879 |
</listitem> |
| 880 |
</varlistentry> |
| 881 |
|
| 882 |
<varlistentry id="sql-altertable-desc-inherit"> |
| 883 |
<term><literal>INHERIT <replaceable class="parameter">parent_table</replaceable></literal></term> |
| 884 |
<listitem> |
| 885 |
<para> |
| 886 |
This form adds the target table as a new child of the specified parent |
| 887 |
table. Subsequently, queries against the parent will include records |
| 888 |
of the target table. To be added as a child, the target table must |
| 889 |
already contain all the same columns as the parent (it could have |
| 890 |
additional columns, too). The columns must have matching data types. |
| 891 |
</para> |
| 892 |
|
| 893 |
<para> |
| 894 |
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal> |
| 895 |
constraints on the parent must also exist on the child, except those |
| 896 |
marked non-inheritable (that is, created with |
| 897 |
<literal>ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT</literal>), which |
| 898 |
are ignored. All child-table constraints matched must not be marked |
| 899 |
non-inheritable. Currently |
| 900 |
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and |
| 901 |
<literal>FOREIGN KEY</literal> constraints are not considered, but |
| 902 |
this might change in the future. |
| 903 |
</para> |
| 904 |
</listitem> |
| 905 |
</varlistentry> |
| 906 |
|
| 907 |
<varlistentry id="sql-altertable-desc-no-inherit"> |
| 908 |
<term><literal>NO INHERIT <replaceable class="parameter">parent_table</replaceable></literal></term> |
| 909 |
<listitem> |
| 910 |
<para> |
| 911 |
This form removes the target table from the list of children of the |
| 912 |
specified parent table. |
| 913 |
Queries against the parent table will no longer include records drawn |
| 914 |
from the target table. |
| 915 |
</para> |
| 916 |
</listitem> |
| 917 |
</varlistentry> |
| 918 |
|
| 919 |
<varlistentry id="sql-altertable-desc-of"> |
| 920 |
<term><literal>OF <replaceable class="parameter">type_name</replaceable></literal></term> |
| 921 |
<listitem> |
| 922 |
<para> |
| 923 |
This form links the table to a composite type as though <command>CREATE |
| 924 |
TABLE OF</command> had formed it. The table's list of column names and types |
| 925 |
must precisely match that of the composite type. The table must |
| 926 |
not inherit from any other table. These restrictions ensure |
| 927 |
that <command>CREATE TABLE OF</command> would permit an equivalent table |
| 928 |
definition. |
| 929 |
</para> |
| 930 |
</listitem> |
| 931 |
</varlistentry> |
| 932 |
|
| 933 |
<varlistentry id="sql-altertable-desc-not-of"> |
| 934 |
<term><literal>NOT OF</literal></term> |
| 935 |
<listitem> |
| 936 |
<para> |
| 937 |
This form dissociates a typed table from its type. |
| 938 |
</para> |
| 939 |
</listitem> |
| 940 |
</varlistentry> |
| 941 |
|
| 942 |
<varlistentry id="sql-altertable-desc-owner-to"> |
| 943 |
<term><literal>OWNER TO</literal></term> |
| 944 |
<listitem> |
| 945 |
<para> |
| 946 |
This form changes the owner of the table, sequence, view, materialized view, |
| 947 |
or foreign table to the specified user. |
| 948 |
</para> |
| 949 |
</listitem> |
| 950 |
</varlistentry> |
| 951 |
|
| 952 |
<varlistentry id="sql-altertable-replica-identity"> |
| 953 |
<term><literal>REPLICA IDENTITY</literal></term> |
| 954 |
<listitem> |
| 955 |
<para> |
| 956 |
This form changes the information which is written to the write-ahead log |
| 957 |
to identify rows which are updated or deleted. |
| 958 |
In most cases, the old value of each column is only logged if it differs |
| 959 |
from the new value; however, if the old value is stored externally, it is |
| 960 |
always logged regardless of whether it changed. |
| 961 |
This option has no effect except when logical replication is in use. |
| 962 |
<variablelist> |
| 963 |
<varlistentry id="sql-altertable-replica-identity-default"> |
| 964 |
<term><literal>DEFAULT</literal></term> |
| 965 |
<listitem> |
| 966 |
<para> |
| 967 |
Records the old values of the columns of the primary key. |
| 968 |
This is the default for non-system tables. |
| 969 |
When there is no primary key, the behavior is the same as <literal>NOTHING</literal>. |
| 970 |
</para> |
| 971 |
</listitem> |
| 972 |
</varlistentry> |
| 973 |
|
| 974 |
<varlistentry id="sql-altertable-replica-identity-using-index"> |
| 975 |
<term><literal>USING INDEX <replaceable class="parameter">index_name</replaceable></literal></term> |
| 976 |
<listitem> |
| 977 |
<para> |
| 978 |
Records the old values of the columns covered by the named index, |
| 979 |
that must be unique, not partial, not deferrable, and include only |
| 980 |
columns marked <literal>NOT NULL</literal>. If this index is |
| 981 |
dropped, the behavior is the same as <literal>NOTHING</literal>. |
| 982 |
</para> |
| 983 |
</listitem> |
| 984 |
</varlistentry> |
| 985 |
|
| 986 |
<varlistentry id="sql-altertable-replica-identity-full"> |
| 987 |
<term><literal>FULL</literal></term> |
| 988 |
<listitem> |
| 989 |
<para> |
| 990 |
Records the old values of all columns in the row. |
| 991 |
</para> |
| 992 |
</listitem> |
| 993 |
</varlistentry> |
| 994 |
|
| 995 |
<varlistentry id="sql-altertable-replica-identity-nothing"> |
| 996 |
<term><literal>NOTHING</literal></term> |
| 997 |
<listitem> |
| 998 |
<para> |
| 999 |
Records no information about the old row. This is the default for |
| 1000 |
system tables. |
| 1001 |
</para> |
| 1002 |
</listitem> |
| 1003 |
</varlistentry> |
| 1004 |
</variablelist></para> |
| 1005 |
</listitem> |
| 1006 |
</varlistentry> |
| 1007 |
|
| 1008 |
<varlistentry id="sql-altertable-desc-rename"> |
| 1009 |
<term><literal>RENAME</literal></term> |
| 1010 |
<listitem> |
| 1011 |
<para> |
| 1012 |
The <literal>RENAME</literal> forms change the name of a table |
| 1013 |
(or an index, sequence, view, materialized view, or foreign table), the |
| 1014 |
name of an individual column in a table, or the name of a constraint of |
| 1015 |
the table. When renaming a constraint that has an underlying index, |
| 1016 |
the index is renamed as well. |
| 1017 |
There is no effect on the stored data. |
| 1018 |
</para> |
| 1019 |
</listitem> |
| 1020 |
</varlistentry> |
| 1021 |
|
| 1022 |
<varlistentry id="sql-altertable-desc-set-schema"> |
| 1023 |
<term><literal>SET SCHEMA</literal></term> |
| 1024 |
<listitem> |
| 1025 |
<para> |
| 1026 |
This form moves the table into another schema. Associated indexes, |
| 1027 |
constraints, and sequences owned by table columns are moved as well. |
| 1028 |
</para> |
| 1029 |
</listitem> |
| 1030 |
</varlistentry> |
| 1031 |
|
| 1032 |
<varlistentry id="sql-altertable-attach-partition"> |
| 1033 |
<term><literal>ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }</literal></term> |
| 1034 |
<listitem> |
| 1035 |
<para> |
| 1036 |
This form attaches an existing table (which might itself be partitioned) |
| 1037 |
as a partition of the target table. The table can be attached |
| 1038 |
as a partition for specific values using <literal>FOR VALUES</literal> |
| 1039 |
or as a default partition by using <literal>DEFAULT</literal>. |
| 1040 |
For each index in the target table, if a valid equivalent index |
| 1041 |
already exists in the partition, it will be attached to the target |
| 1042 |
table's index, as if ALTER INDEX ATTACH PARTITION had been executed; |
| 1043 |
otherwise, a new corresponding index will be created. Invalid indexes |
| 1044 |
on the partition are skipped. |
| 1045 |
Note that if the existing table is a foreign table, it is currently not |
| 1046 |
allowed to attach the table as a partition of the target table if there |
| 1047 |
are <literal>UNIQUE</literal> indexes on the target table. (See also |
| 1048 |
<xref linkend="sql-createforeigntable"/>.) For each user-defined |
| 1049 |
row-level trigger that exists in the target table, a corresponding one |
| 1050 |
is created in the attached table. |
| 1051 |
</para> |
| 1052 |
|
| 1053 |
<para> |
| 1054 |
A partition using <literal>FOR VALUES</literal> uses same syntax for |
| 1055 |
<replaceable class="parameter">partition_bound_spec</replaceable> as |
| 1056 |
<link linkend="sql-createtable"><command>CREATE TABLE</command></link>. |
| 1057 |
The partition bound specification |
| 1058 |
must correspond to the partitioning strategy and partition key of the |
| 1059 |
target table. The table to be attached must have all the same columns |
| 1060 |
as the target table and no more; moreover, the column types must also |
| 1061 |
match. Also, it must have all the <literal>NOT NULL</literal> and |
| 1062 |
<literal>CHECK</literal> constraints of the target table, not marked |
| 1063 |
<literal>NO INHERIT</literal>. Currently |
| 1064 |
<literal>FOREIGN KEY</literal> constraints are not considered. |
| 1065 |
<literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints |
| 1066 |
from the parent table will be created in the partition, if they don't |
| 1067 |
already exist. |
| 1068 |
</para> |
| 1069 |
|
| 1070 |
<para> |
| 1071 |
If the new partition is a regular table, a full table scan is performed |
| 1072 |
to check that existing rows in the table do not violate the partition |
| 1073 |
constraint. It is possible to avoid this scan by adding a valid |
| 1074 |
<literal>CHECK</literal> constraint to the table that allows only |
| 1075 |
rows satisfying the desired partition constraint before running this |
| 1076 |
command. The <literal>CHECK</literal> constraint will be used to |
| 1077 |
determine that the table need not be scanned to validate the partition |
| 1078 |
constraint. This does not work, however, if any of the partition keys |
| 1079 |
is an expression and the partition does not accept |
| 1080 |
<literal>NULL</literal> values. If attaching a list partition that will |
| 1081 |
not accept <literal>NULL</literal> values, also add a |
| 1082 |
<literal>NOT NULL</literal> constraint to the partition key column, |
| 1083 |
unless it's an expression. |
| 1084 |
</para> |
| 1085 |
|
| 1086 |
<para> |
| 1087 |
If the new partition is a foreign table, nothing is done to verify |
| 1088 |
that all the rows in the foreign table obey the partition constraint. |
| 1089 |
(See the discussion in <xref linkend="sql-createforeigntable"/> about |
| 1090 |
constraints on the foreign table.) |
| 1091 |
</para> |
| 1092 |
|
| 1093 |
<para> |
| 1094 |
When a table has a default partition, defining a new partition changes |
| 1095 |
the partition constraint for the default partition. The default |
| 1096 |
partition can't contain any rows that would need to be moved to the new |
| 1097 |
partition, and will be scanned to verify that none are present. This |
| 1098 |
scan, like the scan of the new partition, can be avoided if an |
| 1099 |
appropriate <literal>CHECK</literal> constraint is present. Also like |
| 1100 |
the scan of the new partition, it is always skipped when the default |
| 1101 |
partition is a foreign table. |
| 1102 |
</para> |
| 1103 |
|
| 1104 |
<para> |
| 1105 |
Attaching a partition acquires a |
| 1106 |
<literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table, |
| 1107 |
in addition to the <literal>ACCESS EXCLUSIVE</literal> locks on the table |
| 1108 |
being attached and on the default partition (if any). |
| 1109 |
</para> |
| 1110 |
|
| 1111 |
<para> |
| 1112 |
Further locks must also be held on all sub-partitions if the table being |
| 1113 |
attached is itself a partitioned table. Likewise if the default |
| 1114 |
partition is itself a partitioned table. The locking of the |
| 1115 |
sub-partitions can be avoided by adding a <literal>CHECK</literal> |
| 1116 |
constraint as described in |
| 1117 |
<xref linkend="ddl-partitioning-declarative-maintenance"/>. |
| 1118 |
</para> |
| 1119 |
</listitem> |
| 1120 |
</varlistentry> |
| 1121 |
|
| 1122 |
<varlistentry id="sql-altertable-detach-partition"> |
| 1123 |
<term><literal>DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]</literal></term> |
| 1124 |
|
| 1125 |
<listitem> |
| 1126 |
<para> |
| 1127 |
This form detaches the specified partition of the target table. The detached |
| 1128 |
partition continues to exist as a standalone table, but no longer has any |
| 1129 |
ties to the table from which it was detached. Any indexes that were |
| 1130 |
attached to the target table's indexes are detached. Any triggers that |
| 1131 |
were created as clones of those in the target table are removed. |
| 1132 |
<literal>SHARE</literal> lock is obtained on any tables that reference |
| 1133 |
this partitioned table in foreign key constraints. |
| 1134 |
</para> |
| 1135 |
<para> |
| 1136 |
If <literal>CONCURRENTLY</literal> is specified, it runs using a reduced |
| 1137 |
lock level to avoid blocking other sessions that might be accessing the |
| 1138 |
partitioned table. In this mode, two transactions are used internally. |
| 1139 |
During the first transaction, a <literal>SHARE UPDATE EXCLUSIVE</literal> |
| 1140 |
lock is taken on both parent table and partition, and the partition is |
| 1141 |
marked as undergoing detach; at that point, the transaction is committed |
| 1142 |
and all other transactions using the partitioned table are waited for. |
| 1143 |
Once all those transactions have completed, the second transaction |
| 1144 |
acquires <literal>SHARE UPDATE EXCLUSIVE</literal> on the partitioned |
| 1145 |
table and <literal>ACCESS EXCLUSIVE</literal> on the partition, |
| 1146 |
and the detach process completes. A <literal>CHECK</literal> constraint |
| 1147 |
that duplicates the partition constraint is added to the partition. |
| 1148 |
<literal>CONCURRENTLY</literal> cannot be run in a transaction block and |
| 1149 |
is not allowed if the partitioned table contains a default partition. |
| 1150 |
</para> |
| 1151 |
<para> |
| 1152 |
If <literal>FINALIZE</literal> is specified, a previous |
| 1153 |
<literal>DETACH CONCURRENTLY</literal> invocation that was canceled or |
| 1154 |
interrupted is completed. |
| 1155 |
At most one partition in a partitioned table can be pending detach at |
| 1156 |
a time. |
| 1157 |
</para> |
| 1158 |
</listitem> |
| 1159 |
</varlistentry> |
| 1160 |
|
| 1161 |
</variablelist> |
| 1162 |
</para> |
| 1163 |
|
| 1164 |
<para> |
| 1165 |
All the forms of <command>ALTER TABLE</command> that act on a single table, |
| 1166 |
except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>, |
| 1167 |
<literal>ATTACH PARTITION</literal>, and |
| 1168 |
<literal>DETACH PARTITION</literal> can be combined into |
| 1169 |
a list of multiple alterations to be applied together. For example, it |
| 1170 |
is possible to add several columns and/or alter the type of several |
| 1171 |
columns in a single command. This is particularly useful with large |
| 1172 |
tables, since only one pass over the table need be made. |
| 1173 |
</para> |
| 1174 |
|
| 1175 |
<para> |
| 1176 |
You must own the table to use <command>ALTER TABLE</command>. |
| 1177 |
To change the schema or tablespace of a table, you must also have |
| 1178 |
<literal>CREATE</literal> privilege on the new schema or tablespace. |
| 1179 |
To add the table as a new child of a parent table, you must own the parent |
| 1180 |
table as well. Also, to attach a table as a new partition of the table, |
| 1181 |
you must own the table being attached. |
| 1182 |
To alter the owner, you must be able to <literal>SET ROLE</literal> to the |
| 1183 |
new owning role, and that role must have <literal>CREATE</literal> |
| 1184 |
privilege on the table's schema. |
| 1185 |
(These restrictions enforce that altering the owner |
| 1186 |
doesn't do anything you couldn't do by dropping and recreating the table. |
| 1187 |
However, a superuser can alter ownership of any table anyway.) |
| 1188 |
To add a column or alter a column type or use the <literal>OF</literal> |
| 1189 |
clause, you must also have <literal>USAGE</literal> privilege on the data |
| 1190 |
type. |
| 1191 |
</para> |
| 1192 |
</refsect1> |
| 1193 |
|
| 1194 |
<refsect1> |
| 1195 |
<title>Parameters</title> |
| 1196 |
|
| 1197 |
<variablelist> |
| 1198 |
|
| 1199 |
<varlistentry id="sql-altertable-parms-if-exists"> |
| 1200 |
<term><literal>IF EXISTS</literal></term> |
| 1201 |
<listitem> |
| 1202 |
<para> |
| 1203 |
Do not throw an error if the table does not exist. A notice is issued |
| 1204 |
in this case. |
| 1205 |
</para> |
| 1206 |
</listitem> |
| 1207 |
</varlistentry> |
| 1208 |
|
| 1209 |
<varlistentry id="sql-altertable-parms-name"> |
| 1210 |
<term><replaceable class="parameter">name</replaceable></term> |
| 1211 |
<listitem> |
| 1212 |
<para> |
| 1213 |
The name (optionally schema-qualified) of an existing table to |
| 1214 |
alter. If <literal>ONLY</literal> is specified before the table name, only |
| 1215 |
that table is altered. If <literal>ONLY</literal> is not specified, the table |
| 1216 |
and all its descendant tables (if any) are altered. Optionally, |
| 1217 |
<literal>*</literal> can be specified after the table name to explicitly |
| 1218 |
indicate that descendant tables are included. |
| 1219 |
</para> |
| 1220 |
</listitem> |
| 1221 |
</varlistentry> |
| 1222 |
|
| 1223 |
<varlistentry id="sql-altertable-parms-column-name"> |
| 1224 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 1225 |
<listitem> |
| 1226 |
<para> |
| 1227 |
Name of a new or existing column. |
| 1228 |
</para> |
| 1229 |
</listitem> |
| 1230 |
</varlistentry> |
| 1231 |
|
| 1232 |
<varlistentry id="sql-altertable-parms-new-column-name"> |
| 1233 |
<term><replaceable class="parameter">new_column_name</replaceable></term> |
| 1234 |
<listitem> |
| 1235 |
<para> |
| 1236 |
New name for an existing column. |
| 1237 |
</para> |
| 1238 |
</listitem> |
| 1239 |
</varlistentry> |
| 1240 |
|
| 1241 |
<varlistentry id="sql-altertable-parms-new-name"> |
| 1242 |
<term><replaceable class="parameter">new_name</replaceable></term> |
| 1243 |
<listitem> |
| 1244 |
<para> |
| 1245 |
New name for the table. |
| 1246 |
</para> |
| 1247 |
</listitem> |
| 1248 |
</varlistentry> |
| 1249 |
|
| 1250 |
<varlistentry id="sql-altertable-parms-data-type"> |
| 1251 |
<term><replaceable class="parameter">data_type</replaceable></term> |
| 1252 |
<listitem> |
| 1253 |
<para> |
| 1254 |
Data type of the new column, or new data type for an existing |
| 1255 |
column. |
| 1256 |
</para> |
| 1257 |
</listitem> |
| 1258 |
</varlistentry> |
| 1259 |
|
| 1260 |
<varlistentry id="sql-altertable-parms-table-constraint"> |
| 1261 |
<term><replaceable class="parameter">table_constraint</replaceable></term> |
| 1262 |
<listitem> |
| 1263 |
<para> |
| 1264 |
New table constraint for the table. |
| 1265 |
</para> |
| 1266 |
</listitem> |
| 1267 |
</varlistentry> |
| 1268 |
|
| 1269 |
<varlistentry id="sql-altertable-parms-constraint-name"> |
| 1270 |
<term><replaceable class="parameter">constraint_name</replaceable></term> |
| 1271 |
<listitem> |
| 1272 |
<para> |
| 1273 |
Name of a new or existing constraint. |
| 1274 |
</para> |
| 1275 |
</listitem> |
| 1276 |
</varlistentry> |
| 1277 |
|
| 1278 |
<varlistentry id="sql-altertable-parms-cascade"> |
| 1279 |
<term><literal>CASCADE</literal></term> |
| 1280 |
<listitem> |
| 1281 |
<para> |
| 1282 |
Automatically drop objects that depend on the dropped column |
| 1283 |
or constraint (for example, views referencing the column), |
| 1284 |
and in turn all objects that depend on those objects |
| 1285 |
(see <xref linkend="ddl-depend"/>). |
| 1286 |
</para> |
| 1287 |
</listitem> |
| 1288 |
</varlistentry> |
| 1289 |
|
| 1290 |
<varlistentry id="sql-altertable-parms-restrict"> |
| 1291 |
<term><literal>RESTRICT</literal></term> |
| 1292 |
<listitem> |
| 1293 |
<para> |
| 1294 |
Refuse to drop the column or constraint if there are any dependent |
| 1295 |
objects. This is the default behavior. |
| 1296 |
</para> |
| 1297 |
</listitem> |
| 1298 |
</varlistentry> |
| 1299 |
|
| 1300 |
<varlistentry id="sql-altertable-parms-trigger-name"> |
| 1301 |
<term><replaceable class="parameter">trigger_name</replaceable></term> |
| 1302 |
<listitem> |
| 1303 |
<para> |
| 1304 |
Name of a single trigger to disable or enable. |
| 1305 |
</para> |
| 1306 |
</listitem> |
| 1307 |
</varlistentry> |
| 1308 |
|
| 1309 |
<varlistentry id="sql-altertable-parms-all"> |
| 1310 |
<term><literal>ALL</literal></term> |
| 1311 |
<listitem> |
| 1312 |
<para> |
| 1313 |
Disable or enable all triggers belonging to the table. |
| 1314 |
(This requires superuser privilege if any of the triggers are |
| 1315 |
internally generated constraint triggers, such as those that are used |
| 1316 |
to implement foreign key constraints or deferrable uniqueness and |
| 1317 |
exclusion constraints.) |
| 1318 |
</para> |
| 1319 |
</listitem> |
| 1320 |
</varlistentry> |
| 1321 |
|
| 1322 |
<varlistentry id="sql-altertable-parms-user"> |
| 1323 |
<term><literal>USER</literal></term> |
| 1324 |
<listitem> |
| 1325 |
<para> |
| 1326 |
Disable or enable all triggers belonging to the table except for |
| 1327 |
internally generated constraint triggers, such as those that are used |
| 1328 |
to implement foreign key constraints or deferrable uniqueness and |
| 1329 |
exclusion constraints. |
| 1330 |
</para> |
| 1331 |
</listitem> |
| 1332 |
</varlistentry> |
| 1333 |
|
| 1334 |
<varlistentry id="sql-altertable-parms-index-name"> |
| 1335 |
<term><replaceable class="parameter">index_name</replaceable></term> |
| 1336 |
<listitem> |
| 1337 |
<para> |
| 1338 |
The name of an existing index. |
| 1339 |
</para> |
| 1340 |
</listitem> |
| 1341 |
</varlistentry> |
| 1342 |
|
| 1343 |
<varlistentry id="sql-altertable-parms-storage-parameter"> |
| 1344 |
<term><replaceable class="parameter">storage_parameter</replaceable></term> |
| 1345 |
<listitem> |
| 1346 |
<para> |
| 1347 |
The name of a table storage parameter. |
| 1348 |
</para> |
| 1349 |
</listitem> |
| 1350 |
</varlistentry> |
| 1351 |
|
| 1352 |
<varlistentry id="sql-altertable-parms-value"> |
| 1353 |
<term><replaceable class="parameter">value</replaceable></term> |
| 1354 |
<listitem> |
| 1355 |
<para> |
| 1356 |
The new value for a table storage parameter. |
| 1357 |
This might be a number or a word depending on the parameter. |
| 1358 |
</para> |
| 1359 |
</listitem> |
| 1360 |
</varlistentry> |
| 1361 |
|
| 1362 |
<varlistentry id="sql-altertable-parms-parent-table"> |
| 1363 |
<term><replaceable class="parameter">parent_table</replaceable></term> |
| 1364 |
<listitem> |
| 1365 |
<para> |
| 1366 |
A parent table to associate or de-associate with this table. |
| 1367 |
</para> |
| 1368 |
</listitem> |
| 1369 |
</varlistentry> |
| 1370 |
|
| 1371 |
<varlistentry id="sql-altertable-parms-new-owner"> |
| 1372 |
<term><replaceable class="parameter">new_owner</replaceable></term> |
| 1373 |
<listitem> |
| 1374 |
<para> |
| 1375 |
The user name of the new owner of the table. |
| 1376 |
</para> |
| 1377 |
</listitem> |
| 1378 |
</varlistentry> |
| 1379 |
|
| 1380 |
<varlistentry id="sql-altertable-parms-new-access-method"> |
| 1381 |
<term><replaceable class="parameter">new_access_method</replaceable></term> |
| 1382 |
<listitem> |
| 1383 |
<para> |
| 1384 |
The name of the access method to which the table will be converted. |
| 1385 |
</para> |
| 1386 |
</listitem> |
| 1387 |
</varlistentry> |
| 1388 |
|
| 1389 |
<varlistentry id="sql-altertable-parms-new-tablespace"> |
| 1390 |
<term><replaceable class="parameter">new_tablespace</replaceable></term> |
| 1391 |
<listitem> |
| 1392 |
<para> |
| 1393 |
The name of the tablespace to which the table will be moved. |
| 1394 |
</para> |
| 1395 |
</listitem> |
| 1396 |
</varlistentry> |
| 1397 |
|
| 1398 |
<varlistentry id="sql-altertable-parms-new-schema"> |
| 1399 |
<term><replaceable class="parameter">new_schema</replaceable></term> |
| 1400 |
<listitem> |
| 1401 |
<para> |
| 1402 |
The name of the schema to which the table will be moved. |
| 1403 |
</para> |
| 1404 |
</listitem> |
| 1405 |
</varlistentry> |
| 1406 |
|
| 1407 |
<varlistentry id="sql-altertable-parms-partition-name"> |
| 1408 |
<term><replaceable class="parameter">partition_name</replaceable></term> |
| 1409 |
<listitem> |
| 1410 |
<para> |
| 1411 |
The name of the table to attach as a new partition or to detach from this table. |
| 1412 |
</para> |
| 1413 |
</listitem> |
| 1414 |
</varlistentry> |
| 1415 |
|
| 1416 |
<varlistentry id="sql-altertable-parms-partition-bound-spec"> |
| 1417 |
<term><replaceable class="parameter">partition_bound_spec</replaceable></term> |
| 1418 |
<listitem> |
| 1419 |
<para> |
| 1420 |
The partition bound specification for a new partition. Refer to |
| 1421 |
<xref linkend="sql-createtable"/> for more details on the syntax of the same. |
| 1422 |
</para> |
| 1423 |
</listitem> |
| 1424 |
</varlistentry> |
| 1425 |
|
| 1426 |
</variablelist> |
| 1427 |
</refsect1> |
| 1428 |
|
| 1429 |
<refsect1 id="sql-altertable-notes" xreflabel="Notes"> |
| 1430 |
<title>Notes</title> |
| 1431 |
|
| 1432 |
<para> |
| 1433 |
The key word <literal>COLUMN</literal> is noise and can be omitted. |
| 1434 |
</para> |
| 1435 |
|
| 1436 |
<para> |
| 1437 |
When a column is added with <literal>ADD COLUMN</literal> and a |
| 1438 |
non-volatile <literal>DEFAULT</literal> is specified, the default value is |
| 1439 |
evaluated at the time of the statement and the result stored in the |
| 1440 |
table's metadata, where it will be returned when any existing rows are |
| 1441 |
accessed. The value will be only applied when the table is rewritten, |
| 1442 |
making the <command>ALTER TABLE</command> very fast even on large tables. |
| 1443 |
If no column constraints are specified, NULL is used as the |
| 1444 |
<literal>DEFAULT</literal>. In neither case is a rewrite of the table |
| 1445 |
required. |
| 1446 |
</para> |
| 1447 |
|
| 1448 |
<para> |
| 1449 |
Adding a column with a volatile <literal>DEFAULT</literal> |
| 1450 |
(e.g., <function>clock_timestamp()</function>), a stored generated column, |
| 1451 |
an identity column, or a column with a domain data type that has |
| 1452 |
constraints will cause the entire table and its indexes to be rewritten. |
| 1453 |
Adding a virtual generated column never requires a rewrite. |
| 1454 |
</para> |
| 1455 |
|
| 1456 |
<para> |
| 1457 |
Changing the type of an existing column will normally cause the entire table |
| 1458 |
and its indexes to be rewritten. |
| 1459 |
As an exception, when changing the type of an existing column, |
| 1460 |
if the <literal>USING</literal> clause does not change the column |
| 1461 |
contents and the old type is either binary coercible to the new type |
| 1462 |
or an unconstrained domain over the new type, a table rewrite is not |
| 1463 |
needed. However, indexes will still be rebuilt unless the system |
| 1464 |
can verify that the new index would be logically equivalent to the |
| 1465 |
existing one. For example, if the collation for a column has been |
| 1466 |
changed, an index rebuild is required because the new sort |
| 1467 |
order might be different. However, in the absence of a collation |
| 1468 |
change, a column can be changed from <type>text</type> to |
| 1469 |
<type>varchar</type> (or vice versa) without rebuilding the indexes |
| 1470 |
because these data types sort identically. |
| 1471 |
</para> |
| 1472 |
|
| 1473 |
<para> |
| 1474 |
Table and/or index |
| 1475 |
rebuilds may take a significant amount of time for a large table, |
| 1476 |
and will temporarily require as much as double the disk space. |
| 1477 |
</para> |
| 1478 |
|
| 1479 |
<para> |
| 1480 |
Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal> |
| 1481 |
constraint requires scanning the table to verify that existing rows meet the |
| 1482 |
constraint, but does not require a table rewrite. If a <literal>CHECK</literal> |
| 1483 |
constraint is added as <literal>NOT ENFORCED</literal>, no verification will |
| 1484 |
be performed. |
| 1485 |
</para> |
| 1486 |
|
| 1487 |
<para> |
| 1488 |
Similarly, when attaching a new partition it may be scanned to verify that |
| 1489 |
existing rows meet the partition constraint. |
| 1490 |
</para> |
| 1491 |
|
| 1492 |
<para> |
| 1493 |
The main reason for providing the option to specify multiple changes |
| 1494 |
in a single <command>ALTER TABLE</command> is that multiple table scans or |
| 1495 |
rewrites can thereby be combined into a single pass over the table. |
| 1496 |
</para> |
| 1497 |
|
| 1498 |
<para> |
| 1499 |
Scanning a large table to verify new foreign-key, check, or not-null constraints |
| 1500 |
can take a long time, and other updates to the table are locked out |
| 1501 |
until the <command>ALTER TABLE ADD CONSTRAINT</command> command is |
| 1502 |
committed. The main purpose of the <literal>NOT VALID</literal> |
| 1503 |
constraint option is to reduce the impact of adding a constraint on |
| 1504 |
concurrent updates. With <literal>NOT VALID</literal>, |
| 1505 |
the <command>ADD CONSTRAINT</command> command does not scan the table |
| 1506 |
and can be committed immediately. After that, a <literal>VALIDATE |
| 1507 |
CONSTRAINT</literal> command can be issued to verify that existing rows |
| 1508 |
satisfy the constraint. The validation step does not need to lock out |
| 1509 |
concurrent updates, since it knows that other transactions will be |
| 1510 |
enforcing the constraint for rows that they insert or update; only |
| 1511 |
pre-existing rows need to be checked. Hence, validation acquires only |
| 1512 |
a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the table being |
| 1513 |
altered. (If the constraint is a foreign key then a <literal>ROW |
| 1514 |
SHARE</literal> lock is also required on the table referenced by the |
| 1515 |
constraint.) In addition to improving concurrency, it can be useful to |
| 1516 |
use <literal>NOT VALID</literal> and <literal>VALIDATE |
| 1517 |
CONSTRAINT</literal> in cases where the table is known to contain |
| 1518 |
pre-existing violations. Once the constraint is in place, no new |
| 1519 |
violations can be inserted, and the existing problems can be corrected |
| 1520 |
at leisure until <literal>VALIDATE CONSTRAINT</literal> finally |
| 1521 |
succeeds. |
| 1522 |
</para> |
| 1523 |
|
| 1524 |
<para> |
| 1525 |
The <literal>DROP COLUMN</literal> form does not physically remove |
| 1526 |
the column, but simply makes it invisible to SQL operations. Subsequent |
| 1527 |
insert and update operations in the table will store a null value for the |
| 1528 |
column. Thus, dropping a column is quick but it will not immediately |
| 1529 |
reduce the on-disk size of your table, as the space occupied |
| 1530 |
by the dropped column is not reclaimed. The space will be |
| 1531 |
reclaimed over time as existing rows are updated. |
| 1532 |
</para> |
| 1533 |
|
| 1534 |
<para> |
| 1535 |
To force immediate reclamation of space occupied by a dropped column, |
| 1536 |
you can execute one of the forms of <command>ALTER TABLE</command> that |
| 1537 |
performs a rewrite of the whole table. This results in reconstructing |
| 1538 |
each row with the dropped column replaced by a null value. |
| 1539 |
</para> |
| 1540 |
|
| 1541 |
<para> |
| 1542 |
The rewriting forms of <command>ALTER TABLE</command> are not MVCC-safe. |
| 1543 |
After a table rewrite, the table will appear empty to concurrent |
| 1544 |
transactions, if they are using a snapshot taken before the rewrite |
| 1545 |
occurred. See <xref linkend="mvcc-caveats"/> for more details. |
| 1546 |
</para> |
| 1547 |
|
| 1548 |
<para> |
| 1549 |
The <literal>USING</literal> option of <literal>SET DATA TYPE</literal> can actually |
| 1550 |
specify any expression involving the old values of the row; that is, it |
| 1551 |
can refer to other columns as well as the one being converted. This allows |
| 1552 |
very general conversions to be done with the <literal>SET DATA TYPE</literal> |
| 1553 |
syntax. Because of this flexibility, the <literal>USING</literal> |
| 1554 |
expression is not applied to the column's default value (if any); the |
| 1555 |
result might not be a constant expression as required for a default. |
| 1556 |
This means that when there is no implicit or assignment cast from old to |
| 1557 |
new type, <literal>SET DATA TYPE</literal> might fail to convert the default even |
| 1558 |
though a <literal>USING</literal> clause is supplied. In such cases, |
| 1559 |
drop the default with <literal>DROP DEFAULT</literal>, perform the <literal>ALTER |
| 1560 |
TYPE</literal>, and then use <literal>SET DEFAULT</literal> to add a suitable new |
| 1561 |
default. Similar considerations apply to indexes and constraints involving |
| 1562 |
the column. |
| 1563 |
</para> |
| 1564 |
|
| 1565 |
<para> |
| 1566 |
If a table has any descendant tables, it is not permitted to add, |
| 1567 |
rename, or change the type of a column in the parent table without doing |
| 1568 |
the same to the descendants. This ensures that the descendants always |
| 1569 |
have columns matching the parent. Similarly, a <literal>CHECK</literal> |
| 1570 |
constraint cannot be renamed in the parent without also renaming it in |
| 1571 |
all descendants, so that <literal>CHECK</literal> constraints also match |
| 1572 |
between the parent and its descendants. (That restriction does not apply |
| 1573 |
to index-based constraints, however.) |
| 1574 |
Also, because selecting from the parent also selects from its descendants, |
| 1575 |
a constraint on the parent cannot be marked valid unless it is also marked |
| 1576 |
valid for those descendants. In all of these cases, <command>ALTER TABLE |
| 1577 |
ONLY</command> will be rejected. |
| 1578 |
</para> |
| 1579 |
|
| 1580 |
<para> |
| 1581 |
A recursive <literal>DROP COLUMN</literal> operation will remove a |
| 1582 |
descendant table's column only if the descendant does not inherit |
| 1583 |
that column from any other parents and never had an independent |
| 1584 |
definition of the column. A nonrecursive <literal>DROP |
| 1585 |
COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP |
| 1586 |
COLUMN</command>) never removes any descendant columns, but |
| 1587 |
instead marks them as independently defined rather than inherited. |
| 1588 |
A nonrecursive <literal>DROP COLUMN</literal> command will fail for a |
| 1589 |
partitioned table, because all partitions of a table must have the same |
| 1590 |
columns as the partitioning root. |
| 1591 |
</para> |
| 1592 |
|
| 1593 |
<para> |
| 1594 |
The actions for identity columns (<literal>ADD |
| 1595 |
GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP |
| 1596 |
IDENTITY</literal>), as well as the actions |
| 1597 |
<literal>CLUSTER</literal>, <literal>OWNER</literal>, |
| 1598 |
and <literal>TABLESPACE</literal> never recurse to descendant tables; |
| 1599 |
that is, they always act as though <literal>ONLY</literal> were specified. |
| 1600 |
Actions affecting trigger states recurse to partitions of partitioned |
| 1601 |
tables (unless <literal>ONLY</literal> is specified), but never to |
| 1602 |
traditional-inheritance descendants. |
| 1603 |
Adding a constraint recurses only for <literal>CHECK</literal> constraints |
| 1604 |
that are not marked <literal>NO INHERIT</literal>. |
| 1605 |
</para> |
| 1606 |
|
| 1607 |
<para> |
| 1608 |
Changing any part of a system catalog table is not permitted. |
| 1609 |
</para> |
| 1610 |
|
| 1611 |
<para> |
| 1612 |
Refer to <xref linkend="sql-createtable"/> for a further description of valid |
| 1613 |
parameters. <xref linkend="ddl"/> has further information on |
| 1614 |
inheritance. |
| 1615 |
</para> |
| 1616 |
</refsect1> |
| 1617 |
|
| 1618 |
<refsect1> |
| 1619 |
<title>Examples</title> |
| 1620 |
|
| 1621 |
<para> |
| 1622 |
To add a column of type <type>varchar</type> to a table: |
| 1623 |
<programlisting> |
| 1624 |
ALTER TABLE distributors ADD COLUMN address varchar(30); |
| 1625 |
</programlisting> |
| 1626 |
That will cause all existing rows in the table to be filled with null |
| 1627 |
values for the new column. |
| 1628 |
</para> |
| 1629 |
|
| 1630 |
<para> |
| 1631 |
To add a column with a non-null default: |
| 1632 |
<programlisting> |
| 1633 |
ALTER TABLE measurements |
| 1634 |
ADD COLUMN mtime timestamp with time zone DEFAULT now(); |
| 1635 |
</programlisting> |
| 1636 |
Existing rows will be filled with the current time as the value of the |
| 1637 |
new column, and then new rows will receive the time of their insertion. |
| 1638 |
</para> |
| 1639 |
|
| 1640 |
<para> |
| 1641 |
To add a column and fill it with a value different from the default to |
| 1642 |
be used later: |
| 1643 |
<programlisting> |
| 1644 |
ALTER TABLE transactions |
| 1645 |
ADD COLUMN status varchar(30) DEFAULT 'old', |
| 1646 |
ALTER COLUMN status SET default 'current'; |
| 1647 |
</programlisting> |
| 1648 |
Existing rows will be filled with <literal>old</literal>, but then |
| 1649 |
the default for subsequent commands will be <literal>current</literal>. |
| 1650 |
The effects are the same as if the two sub-commands had been issued |
| 1651 |
in separate <command>ALTER TABLE</command> commands. |
| 1652 |
</para> |
| 1653 |
|
| 1654 |
<para> |
| 1655 |
To drop a column from a table: |
| 1656 |
<programlisting> |
| 1657 |
ALTER TABLE distributors DROP COLUMN address RESTRICT; |
| 1658 |
</programlisting> |
| 1659 |
</para> |
| 1660 |
|
| 1661 |
<para> |
| 1662 |
To change the types of two existing columns in one operation: |
| 1663 |
<programlisting> |
| 1664 |
ALTER TABLE distributors |
| 1665 |
ALTER COLUMN address TYPE varchar(80), |
| 1666 |
ALTER COLUMN name TYPE varchar(100); |
| 1667 |
</programlisting> |
| 1668 |
</para> |
| 1669 |
|
| 1670 |
<para> |
| 1671 |
To change an integer column containing Unix timestamps to <type>timestamp |
| 1672 |
with time zone</type> via a <literal>USING</literal> clause: |
| 1673 |
<programlisting> |
| 1674 |
ALTER TABLE foo |
| 1675 |
ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone |
| 1676 |
USING |
| 1677 |
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; |
| 1678 |
</programlisting> |
| 1679 |
</para> |
| 1680 |
|
| 1681 |
<para> |
| 1682 |
The same, when the column has a default expression that won't automatically |
| 1683 |
cast to the new data type: |
| 1684 |
<programlisting> |
| 1685 |
ALTER TABLE foo |
| 1686 |
ALTER COLUMN foo_timestamp DROP DEFAULT, |
| 1687 |
ALTER COLUMN foo_timestamp TYPE timestamp with time zone |
| 1688 |
USING |
| 1689 |
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second', |
| 1690 |
ALTER COLUMN foo_timestamp SET DEFAULT now(); |
| 1691 |
</programlisting> |
| 1692 |
</para> |
| 1693 |
|
| 1694 |
<para> |
| 1695 |
To rename an existing column: |
| 1696 |
<programlisting> |
| 1697 |
ALTER TABLE distributors RENAME COLUMN address TO city; |
| 1698 |
</programlisting> |
| 1699 |
</para> |
| 1700 |
|
| 1701 |
<para> |
| 1702 |
To rename an existing table: |
| 1703 |
<programlisting> |
| 1704 |
ALTER TABLE distributors RENAME TO suppliers; |
| 1705 |
</programlisting> |
| 1706 |
</para> |
| 1707 |
|
| 1708 |
<para> |
| 1709 |
To rename an existing constraint: |
| 1710 |
<programlisting> |
| 1711 |
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check; |
| 1712 |
</programlisting> |
| 1713 |
</para> |
| 1714 |
|
| 1715 |
<para> |
| 1716 |
To add a not-null constraint to a column: |
| 1717 |
<programlisting> |
| 1718 |
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; |
| 1719 |
</programlisting> |
| 1720 |
To remove a not-null constraint from a column: |
| 1721 |
<programlisting> |
| 1722 |
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; |
| 1723 |
</programlisting> |
| 1724 |
</para> |
| 1725 |
|
| 1726 |
<para> |
| 1727 |
To add a check constraint to a table and all its children: |
| 1728 |
<programlisting> |
| 1729 |
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); |
| 1730 |
</programlisting> |
| 1731 |
</para> |
| 1732 |
|
| 1733 |
<para> |
| 1734 |
To add a check constraint only to a table and not to its children: |
| 1735 |
<programlisting> |
| 1736 |
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT; |
| 1737 |
</programlisting> |
| 1738 |
(The check constraint will not be inherited by future children, either.) |
| 1739 |
</para> |
| 1740 |
|
| 1741 |
<para> |
| 1742 |
To remove a check constraint from a table and all its children: |
| 1743 |
<programlisting> |
| 1744 |
ALTER TABLE distributors DROP CONSTRAINT zipchk; |
| 1745 |
</programlisting> |
| 1746 |
</para> |
| 1747 |
|
| 1748 |
<para> |
| 1749 |
To remove a check constraint from one table only: |
| 1750 |
<programlisting> |
| 1751 |
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk; |
| 1752 |
</programlisting> |
| 1753 |
(The check constraint remains in place for any child tables.) |
| 1754 |
</para> |
| 1755 |
|
| 1756 |
<para> |
| 1757 |
To add a foreign key constraint to a table: |
| 1758 |
<programlisting> |
| 1759 |
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address); |
| 1760 |
</programlisting> |
| 1761 |
</para> |
| 1762 |
|
| 1763 |
<para> |
| 1764 |
To add a foreign key constraint to a table with the least impact on other work: |
| 1765 |
<programlisting> |
| 1766 |
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; |
| 1767 |
ALTER TABLE distributors VALIDATE CONSTRAINT distfk; |
| 1768 |
</programlisting> |
| 1769 |
</para> |
| 1770 |
|
| 1771 |
<para> |
| 1772 |
To add a (multicolumn) unique constraint to a table: |
| 1773 |
<programlisting> |
| 1774 |
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode); |
| 1775 |
</programlisting> |
| 1776 |
</para> |
| 1777 |
|
| 1778 |
<para> |
| 1779 |
To add an automatically named primary key constraint to a table, noting |
| 1780 |
that a table can only ever have one primary key: |
| 1781 |
<programlisting> |
| 1782 |
ALTER TABLE distributors ADD PRIMARY KEY (dist_id); |
| 1783 |
</programlisting> |
| 1784 |
</para> |
| 1785 |
|
| 1786 |
<para> |
| 1787 |
To move a table to a different tablespace: |
| 1788 |
<programlisting> |
| 1789 |
ALTER TABLE distributors SET TABLESPACE fasttablespace; |
| 1790 |
</programlisting> |
| 1791 |
</para> |
| 1792 |
|
| 1793 |
<para> |
| 1794 |
To move a table to a different schema: |
| 1795 |
<programlisting> |
| 1796 |
ALTER TABLE myschema.distributors SET SCHEMA yourschema; |
| 1797 |
</programlisting> |
| 1798 |
</para> |
| 1799 |
|
| 1800 |
<para> |
| 1801 |
To recreate a primary key constraint, without blocking updates while the |
| 1802 |
index is rebuilt: |
| 1803 |
<programlisting> |
| 1804 |
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); |
| 1805 |
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, |
| 1806 |
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; |
| 1807 |
</programlisting></para> |
| 1808 |
|
| 1809 |
<para> |
| 1810 |
To attach a partition to a range-partitioned table: |
| 1811 |
<programlisting> |
| 1812 |
ALTER TABLE measurement |
| 1813 |
ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); |
| 1814 |
</programlisting></para> |
| 1815 |
|
| 1816 |
<para> |
| 1817 |
To attach a partition to a list-partitioned table: |
| 1818 |
<programlisting> |
| 1819 |
ALTER TABLE cities |
| 1820 |
ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b'); |
| 1821 |
</programlisting></para> |
| 1822 |
|
| 1823 |
<para> |
| 1824 |
To attach a partition to a hash-partitioned table: |
| 1825 |
<programlisting> |
| 1826 |
ALTER TABLE orders |
| 1827 |
ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3); |
| 1828 |
</programlisting></para> |
| 1829 |
|
| 1830 |
<para> |
| 1831 |
To attach a default partition to a partitioned table: |
| 1832 |
<programlisting> |
| 1833 |
ALTER TABLE cities |
| 1834 |
ATTACH PARTITION cities_partdef DEFAULT; |
| 1835 |
</programlisting></para> |
| 1836 |
|
| 1837 |
<para> |
| 1838 |
To detach a partition from a partitioned table: |
| 1839 |
<programlisting> |
| 1840 |
ALTER TABLE measurement |
| 1841 |
DETACH PARTITION measurement_y2015m12; |
| 1842 |
</programlisting></para> |
| 1843 |
|
| 1844 |
</refsect1> |
| 1845 |
|
| 1846 |
<refsect1> |
| 1847 |
<title>Compatibility</title> |
| 1848 |
|
| 1849 |
<para> |
| 1850 |
The forms <literal>ADD [COLUMN]</literal>, |
| 1851 |
<literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>, |
| 1852 |
<literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>), |
| 1853 |
<literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal> |
| 1854 |
conform with the SQL standard. |
| 1855 |
The form <literal>ADD <replaceable>table_constraint</replaceable></literal> |
| 1856 |
conforms with the SQL standard when the <literal>USING INDEX</literal> and |
| 1857 |
<literal>NOT VALID</literal> clauses are omitted and the constraint type is |
| 1858 |
one of <literal>CHECK</literal>, <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, |
| 1859 |
or <literal>REFERENCES</literal>. |
| 1860 |
The other forms are |
| 1861 |
<productname>PostgreSQL</productname> extensions of the SQL standard. |
| 1862 |
Also, the ability to specify more than one manipulation in a single |
| 1863 |
<command>ALTER TABLE</command> command is an extension. |
| 1864 |
</para> |
| 1865 |
|
| 1866 |
<para> |
| 1867 |
<command>ALTER TABLE DROP COLUMN</command> can be used to drop the only |
| 1868 |
column of a table, leaving a zero-column table. This is an |
| 1869 |
extension of SQL, which disallows zero-column tables. |
| 1870 |
</para> |
| 1871 |
</refsect1> |
| 1872 |
|
| 1873 |
<refsect1> |
| 1874 |
<title>See Also</title> |
| 1875 |
|
| 1876 |
<simplelist type="inline"> |
| 1877 |
<member><xref linkend="sql-createtable"/></member> |
| 1878 |
</simplelist> |
| 1879 |
</refsect1> |
| 1880 |
</refentry> |