Debian Codegraph @

doc/src/sgml/ref/alter_table.sgml

raw
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>