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