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

Commit1300fa6

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 parent5f38403 commit1300fa6

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
@@ -1301,6 +1301,29 @@ ALTER TABLE products ADD COLUMN description text;
13011301
value is given (null if you don't specify a <literal>DEFAULT</literal> clause).
13021302
</para>
13031303

1304+
<tip>
1305+
<para>
1306+
From <productname>PostgreSQL</productname> 11, adding a column with
1307+
a constant default value no longer means that each row of the table
1308+
needs to be updated when the <command>ALTER TABLE</command> statement
1309+
is executed. Instead, the default value will be returned the next time
1310+
the row is accessed, and applied when the table is rewritten, making
1311+
the <command>ALTER TABLE</command> very fast even on large tables.
1312+
</para>
1313+
1314+
<para>
1315+
However, if the default value is volatile (e.g.
1316+
<function>clock_timestamp()</function>)
1317+
each row will need to be updated with the value calculated at the time
1318+
<command>ALTER TABLE</command> is executed. To avoid a potentially
1319+
lengthy update operation, particularly if you intend to fill the column
1320+
with mostly nondefault values anyway, it may be preferable to add the
1321+
column with no default, insert the correct values using
1322+
<command>UPDATE</command>, and then add any desired default as described
1323+
below.
1324+
</para>
1325+
</tip>
1326+
13041327
<para>
13051328
You can also define constraints on the column at the same time,
13061329
using the usual syntax:
@@ -1315,17 +1338,6 @@ ALTER TABLE products ADD COLUMN description text CHECK (description &lt;&gt; '')
13151338
correctly.
13161339
</para>
13171340

1318-
<tip>
1319-
<para>
1320-
Adding a column with a default requires updating each row of the
1321-
table (to store the new column value). However, if no default is
1322-
specified, <productname>PostgreSQL</productname> is able to avoid
1323-
the physical update. So if you intend to fill the column with
1324-
mostly nondefault values, it's best to add the column with no default,
1325-
insert the correct values using <command>UPDATE</command>, and then add any
1326-
desired default as described below.
1327-
</para>
1328-
</tip>
13291341
</sect2>
13301342

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

0 commit comments

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