Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit80aa663

Browse files
committed
Doc: clarify when table rewrites happen with column addition and DEFAULT
16828d5 has improved ALTER TABLE so as a column addition does notrequire a rewrite for a non-NULL default with constant expressions, butone spot in the documentation did not get updated consistently.The documentation also now clarifies the fact that this does not applyif the expression is volatile, where a table rewrite is still required.Reported-by: Daniel WestermannAuthor: Ian BarwickReviewed-by: Michael Paquier, Daniel WestermannDiscussion:https://postgr.es/m/DB6PR0902MB2184C7D5645CF15D75EB7957D2CF0@DB6PR0902MB2184.eurprd09.prod.outlook.comBackpatch-through: 11
1 parent477ebfc commit80aa663

File tree

1 file changed

+23
-11
lines changed

1 file changed

+23
-11
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 23 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1189,6 +1189,29 @@ ALTER TABLE products ADD COLUMN description text;
11891189
value is given (null if you don't specify a <literal>DEFAULT</literal> clause).
11901190
</para>
11911191

1192+
<tip>
1193+
<para>
1194+
From <productname>PostgreSQL</productname> 11, adding a column with
1195+
a constant default value no longer means that each row of the table
1196+
needs to be updated when the <command>ALTER TABLE</command> statement
1197+
is executed. Instead, the default value will be returned the next time
1198+
the row is accessed, and applied when the table is rewritten, making
1199+
the <command>ALTER TABLE</command> very fast even on large tables.
1200+
</para>
1201+
1202+
<para>
1203+
However, if the default value is volatile (e.g.
1204+
<function>clock_timestamp()</function>)
1205+
each row will need to be updated with the value calculated at the time
1206+
<command>ALTER TABLE</command> is executed. To avoid a potentially
1207+
lengthy update operation, particularly if you intend to fill the column
1208+
with mostly nondefault values anyway, it may be preferable to add the
1209+
column with no default, insert the correct values using
1210+
<command>UPDATE</command>, and then add any desired default as described
1211+
below.
1212+
</para>
1213+
</tip>
1214+
11921215
<para>
11931216
You can also define constraints on the column at the same time,
11941217
using the usual syntax:
@@ -1203,17 +1226,6 @@ ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '')
12031226
correctly.
12041227
</para>
12051228

1206-
<tip>
1207-
<para>
1208-
Adding a column with a default requires updating each row of the
1209-
table (to store the new column value). However, if no default is
1210-
specified, <productname>PostgreSQL</productname> is able to avoid
1211-
the physical update. So if you intend to fill the column with
1212-
mostly nondefault values, it's best to add the column with no default,
1213-
insert the correct values using <command>UPDATE</command>, and then add any
1214-
desired default as described below.
1215-
</para>
1216-
</tip>
12171229
</sect2>
12181230

12191231
<sect2 id="ddl-alter-removing-a-column">

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp