postgrespro.git / summary / log / commit / refs
commit 1300fa66b2f3d0dcd2eed7a5eff9e3fc22807f7c
Author: Michael Paquier <michael@paquier.xyz>
Date: Fri Jul 19 02:42:33 2019 +0000
Doc: clarify when table rewrites happen with column addition and DEFAULT
16828d5 has improved ALTER TABLE so as a column addition does not
require a rewrite for a non-NULL default with constant expressions, but
one spot in the documentation did not get updated consistently.
The documentation also now clarifies the fact that this does not apply
if the expression is volatile, where a table rewrite is still required.
Reported-by: Daniel Westermann
Author: Ian Barwick
Reviewed-by: Michael Paquier, Daniel Westermann
Discussion: https://postgr.es/m/DB6PR0902MB2184C7D5645CF15D75EB7957D2CF0@DB6PR0902MB2184.eurprd09.prod.outlook.com
Backpatch-through: 11
doc/src/sgml/ddl.sgml | 34 +++++++++++++++++++++++-----------
1 file changed, 23 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9301f0227d..b2bab81840 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1301,6 +1301,29 @@ ALTER TABLE products ADD COLUMN description text;
value is given (null if you don't specify a <literal>DEFAULT</literal> clause).
</para>
+ <tip>
+ <para>
+ From <productname>PostgreSQL</productname> 11, adding a column with
+ a constant default value no longer means that each row of the table
+ needs to be updated when the <command>ALTER TABLE</command> statement
+ is executed. Instead, the default value will be returned the next time
+ the row is accessed, and applied when the table is rewritten, making
+ the <command>ALTER TABLE</command> very fast even on large tables.
+ </para>
+
+ <para>
+ However, if the default value is volatile (e.g.
+ <function>clock_timestamp()</function>)
+ each row will need to be updated with the value calculated at the time
+ <command>ALTER TABLE</command> is executed. To avoid a potentially
+ lengthy update operation, particularly if you intend to fill the column
+ with mostly nondefault values anyway, it may be preferable to add the
+ column with no default, insert the correct values using
+ <command>UPDATE</command>, and then add any desired default as described
+ below.
+ </para>
+ </tip>
+
<para>
You can also define constraints on the column at the same time,
using the usual syntax:
@@ -1315,17 +1338,6 @@ ALTER TABLE products ADD COLUMN description text CHECK (description <> '')
correctly.
</para>
- <tip>
- <para>
- Adding a column with a default requires updating each row of the
- table (to store the new column value). However, if no default is
- specified, <productname>PostgreSQL</productname> is able to avoid
- the physical update. So if you intend to fill the column with
- mostly nondefault values, it's best to add the column with no default,
- insert the correct values using <command>UPDATE</command>, and then add any
- desired default as described below.
- </para>
- </tip>
</sect2>
<sect2 id="ddl-alter-removing-a-column">
[parent: 5f3840370b63]