public inbox for [email protected]
help / color / mirror / Atom feedRe: docs: clarify ALTER TABLE behavior on partitioned tables
19+ messages / 3 participants
[nested] [flat]
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
@ 2026-01-21 01:09 David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-21 01:09 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Mon, Jan 19, 2026 at 1:20 AM Chao Li <[email protected]> wrote:
>
> Thanks again for your review. I have integrated all your edits to v4. Once
> you consider the changes are okay, can you please mark the CF entry [1] as
> Ready for Committer?
>
>
v5 Attached with a delta.
There exists described behaviors in the notes that are being removed that
need to be retained; pertaining to inheritance setups. I put a note in
place of the removals pointing out that when inheritance is being used for
partitioning the rules for partitioned tables apply (implied 'unless
otherwise noted') and that local/independent objects can always be
addressed normally. I then incorporated the notes into the appropriate
sub-command sections. Please give it a sanity check since I'm still
getting my head around all this.
David J.
Attachments:
[text/x-patch] v5-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (32.7K, 3-v5-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From 262a5b3418174ffb7e2295523b1a9e2235ab53b6 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v5 1/2] docs: clarify ALTER TABLE behavior on partitioned
tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 298 ++++++++++++++++++++---------
doc/src/sgml/ref/create_table.sgml | 9 +
2 files changed, 214 insertions(+), 93 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..6131632d20b 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +223,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +230,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +249,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +263,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +275,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +299,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +307,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +323,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +355,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +379,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +409,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +447,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +486,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +520,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +537,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +552,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +562,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +582,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +589,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +596,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +608,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +632,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition is implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +656,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +679,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +697,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Individual partitions may drop constraints independently of the
+ partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +724,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +733,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +744,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +766,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +791,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +811,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +830,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +849,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +880,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +896,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +925,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +954,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +974,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +992,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1003,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1019,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1037,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1050,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1064,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1125,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1145,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ For partitioned tables, this form behaves the same as for regular
+ tables. Renaming a partitioned table, column, or constraint does not
+ propagate to partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1160,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1543,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1767,48 +1921,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the column.
</para>
- <para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
- </para>
-
<para>
Changing any part of a system catalog table is not permitted.
</para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..835f609e2a6 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a new partition is created, it generally inherits the current
+ definition-level properties of the parent partitioned table. However,
+ some table properties are not inherited automatically. In particular,
+ properties related to ownership, schema, replica identity, row-level
+ security configuration, per-attribute statistics targets, and
+ per-attribute options are not inherited unless explicitly specified
+ for the partition.
+ </para>
</listitem>
</varlistentry>
--
2.43.0
[text/x-patch] v5-0002-delta.patch (3.8K, 4-v5-0002-delta.patch)
download | inline diff:
From 895a3604430be79d2bf18c2d259a0ce49f3320e4 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
Date: Tue, 20 Jan 2026 18:01:46 -0700
Subject: [PATCH v5 2/2] 0002-delta
---
doc/src/sgml/ref/alter_table.sgml | 34 +++++++++++++++++++++++++++----
1 file changed, 30 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 6131632d20b..076da294ca1 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -202,6 +202,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
partitions. Specifying <literal>ONLY</literal> is not allowed, and
this command cannot be used on individual partitions.
</para>
+ <para>
+ For inheritance setups, a descendant column is removed only if both: this
+ is the only parent defining the column, and the column was never independently
+ defined.
+ </para>
</listitem>
</varlistentry>
@@ -571,6 +576,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
partitioned table and is implicitly added to all partitions.
Specifying <literal>ONLY</literal> is not allowed.
</para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables
+ unless it is a <literal>CHECK</literal> constraint with the (implicit)
+ <literal>INHERIT</literal> property.
+ </para>
</listitem>
</varlistentry>
@@ -683,7 +693,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
When applied to a partitioned table, the constraint is validated on the
partitioned table and on all partitions. Unlike most constraint-related
sub-commands, individual partitions may validate the constraint
- independently of the partitioned table.
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
</para>
</listitem>
</varlistentry>
@@ -749,6 +760,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
corresponding clone triggers are updated too, unless <literal>ONLY</literal>
is specified.
</para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
+ </para>
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -1146,9 +1161,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
There is no effect on the stored data.
</para>
<para>
- For partitioned tables, this form behaves the same as for regular
- tables. Renaming a partitioned table, column, or constraint does not
- propagate to partitions.
+ When applied to a partitioned table, partition columns constraints
+ are implicitly renamed and specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, index-based constraints are always considered
+ independent. Dependent columns and constraints are implicitly renamed
+ and specifying <literal>ONLY</literal> is not allowed.
</para>
</listitem>
</varlistentry>
@@ -1921,6 +1940,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the column.
</para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
+
<para>
Changing any part of a system catalog table is not permitted.
</para>
--
2.43.0
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-21 02:53 ` Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-21 02:53 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Jan 21, 2026, at 09:09, David G. Johnston <[email protected]> wrote:
>
> On Mon, Jan 19, 2026 at 1:20 AM Chao Li <[email protected]> wrote:
>
> Thanks again for your review. I have integrated all your edits to v4. Once you consider the changes are okay, can you please mark the CF entry [1] as Ready for Committer?
>
>
> v5 Attached with a delta.
>
> There exists described behaviors in the notes that are being removed that need to be retained; pertaining to inheritance setups. I put a note in place of the removals pointing out that when inheritance is being used for partitioning the rules for partitioned tables apply (implied 'unless otherwise noted') and that local/independent objects can always be addressed normally. I then incorporated the notes into the appropriate sub-command sections. Please give it a sanity check since I'm still getting my head around all this.
>
> David J.
>
> <v5-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch><v5-0002-delta.patch>
Hi David,
Thanks for your continuous review. I just checked the delta:
1 - DROP COLUMN
```
+ <para>
+ For inheritance setups, a descendant column is removed only if both: this
+ is the only parent defining the column, and the column was never independently
+ defined.
+ </para>
```
The meaning is basically correct. I dislike the statement of “only if both:”, can we rephrase like:
```
For inheritance setups, a descendant column is removed only if both of the
following are true: this is the only parent defining the column, and the column
was never independently defined in the descendant.
```
What do you think?
2 - ADD table_constain
```
+ <para>
+ For inheritance setups, the constraint is not added to child tables
+ unless it is a <literal>CHECK</literal> constraint with the (implicit)
+ <literal>INHERIT</literal> property.
+ </para>
```
The meaning is also correct. I want a slight tweak like:
```
For inheritance setups, the constraint is not added to child tables unless it is
a CHECK constraint that is inheritable (that is, not declared NO INHERIT).
```
What do you think?
3 - VALIDATE CONSTRAINT
```
- independently of the partitioned table.
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
```
Agreed.
4 - DISABLE/ENABLE TRIGGER
```
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
+ </para>
```
Agreed.
5 - RENAME
```
- For partitioned tables, this form behaves the same as for regular
- tables. Renaming a partitioned table, column, or constraint does not
- propagate to partitions.
+ When applied to a partitioned table, partition columns constraints
+ are implicitly renamed and specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, index-based constraints are always considered
+ independent. Dependent columns and constraints are implicitly renamed
+ and specifying <literal>ONLY</literal> is not allowed.
```
Here “specifying ONLY is not allowed” is wrong. ONLY can be specified, but has no effect. So I think we can use the statement you put to ENABLE/DISABLE TRIGGER: “ONLY is implied, though it may be specified”.
6 - Notes
```
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
```
Agreed.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-21 03:08 ` David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-21 03:08 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Tuesday, January 20, 2026, Chao Li <[email protected]> wrote:
>
>
> > On Jan 21, 2026, at 09:09, David G. Johnston <[email protected]>
> wrote:
> >
> > On Mon, Jan 19, 2026 at 1:20 AM Chao Li <[email protected]> wrote:
> >
> > Thanks again for your review. I have integrated all your edits to v4.
> Once you consider the changes are okay, can you please mark the CF entry
> [1] as Ready for Committer?
> >
> >
> > v5 Attached with a delta.
> >
> > There exists described behaviors in the notes that are being removed
> that need to be retained; pertaining to inheritance setups. I put a note
> in place of the removals pointing out that when inheritance is being used
> for partitioning the rules for partitioned tables apply (implied 'unless
> otherwise noted') and that local/independent objects can always be
> addressed normally. I then incorporated the notes into the appropriate
> sub-command sections. Please give it a sanity check since I'm still
> getting my head around all this.
> >
> > David J.
> >
> > <v5-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-
> .patch><v5-0002-delta.patch>
>
> Hi David,
>
> Thanks for your continuous review. I just checked the delta:
>
The tweaks all look good.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-21 05:29 ` Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-21 05:29 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Jan 21, 2026, at 11:08, David G. Johnston <[email protected]>
wrote:
On Tuesday, January 20, 2026, Chao Li <[email protected]> wrote:
> On Jan 21, 2026, at 09:09, David G. Johnston <[email protected]>
wrote:
>
> On Mon, Jan 19, 2026 at 1:20 AM Chao Li <[email protected]> wrote:
>
> Thanks again for your review. I have integrated all your edits to v4.
Once you consider the changes are okay, can you please mark the CF entry
[1] as Ready for Committer?
>
>
> v5 Attached with a delta.
>
> There exists described behaviors in the notes that are being removed that
need to be retained; pertaining to inheritance setups. I put a note in
place of the removals pointing out that when inheritance is being used for
partitioning the rules for partitioned tables apply (implied 'unless
otherwise noted') and that local/independent objects can always be
addressed normally. I then incorporated the notes into the appropriate
sub-command sections. Please give it a sanity check since I'm still
getting my head around all this.
>
> David J.
>
>
<v5-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch><v5-0002-delta.patch>
Hi David,
Thanks for your continuous review. I just checked the delta:
The tweaks all look good.
David J.
Cool! Your edits and my tweaks are integrated into v6.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v6-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (33.8K, 3-v6-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From 31776be34990b54d7134d0156e8c7cec684d5d8a Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v6] docs: clarify ALTER TABLE behavior on partitioned tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 318 +++++++++++++++++++++--------
doc/src/sgml/ref/create_table.sgml | 9 +
2 files changed, 237 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..080a10947cd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if both of the
+ following are true: this is the only parent defining the column, and the column
+ was never independently defined in the descendant.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +228,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +235,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +254,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +268,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +280,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +304,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +312,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +328,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +360,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +384,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +414,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +452,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +491,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +525,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +542,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +557,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +567,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +591,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +598,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +605,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +617,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +641,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition is implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +665,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +688,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +707,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Individual partitions may drop constraints independently of the
+ partitioned table.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +734,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +743,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +754,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +780,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +805,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +825,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +844,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +863,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +894,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +910,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +939,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +968,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +988,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1006,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1017,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1033,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1051,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1064,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1078,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1139,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1159,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table, partition columns constraints
+ are implicitly renamed and <literal>ONLY</literal> is implied, though
+ it may be specified.
+ </para>
+ <para>
+ For inheritance setups, index-based constraints are always considered
+ independent. Dependent columns and constraints are implicitly renamed
+ and <literal>ONLY</literal> is implied, though it may be specified.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1179,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1562,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1941,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..835f609e2a6 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a new partition is created, it generally inherits the current
+ definition-level properties of the parent partitioned table. However,
+ some table properties are not inherited automatically. In particular,
+ properties related to ownership, schema, replica identity, row-level
+ security configuration, per-attribute statistics targets, and
+ per-attribute options are not inherited unless explicitly specified
+ for the partition.
+ </para>
</listitem>
</varlistentry>
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-23 10:07 ` Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Zsolt Parragi @ 2026-01-23 10:07 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: David G. Johnston <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
Hello
Really good changes!
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition is implicitly applied to all partitions.
an "and" is missing here (definition and is)
+ When applied to a partitioned table, partition columns constraints
+ are implicitly renamed and specifying <literal>ONLY</literal>
is not allowed.
+ </para>
"partition columns constraints" - that seems like a strange/unclear
wording to me. maybe ", the partition's column constraints are ... " ?
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
"When applied to a partitioned table, <literal>ONLY</literal> is
implicit and ..." (at multiple places, this is an example)
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
"A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
never removes any descendant columns, but instead marks them as
independently defined rather than inherited."
This part is now undocumented, it was only mentioned in this paragraph.
> C2 - Sub-commands where using them with a partitioned table will automatically propagate to child partitions; ONLY prevents propagation; new partitions inherit the parent’s new setting; and child partitions can be set to different values than the parent.
The documentation of this group is inconsistent.
DROP CONSTRAINT mentions that individual partitions can be dropped separately:
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Individual partitions may drop constraints independently of the
+ partitioned table.
But most of the sub commands in the C2 group leave the last sentence
out, and also the C7 (ADD table_constraint)
Also, isn't DROP CONSTRAINT on a partition limited to constraints
defined on that partition? So it would be better to say "may drop
constraints defined directly on that individual partition
independently".
CREATE TABLE parent (id int, val int) PARTITION BY RANGE (id);
ALTER TABLE parent ADD CONSTRAINT val_positive CHECK (val > 0);
CREATE TABLE child PARTITION OF parent FOR VALUES FROM (1) TO (100);
ALTER TABLE child DROP CONSTRAINT val_positive;
-- ERROR: cannot drop inherited constraint "val_positive" of relation "child"
+ When a new partition is created, it generally inherits the current
+ definition-level properties of the parent partitioned table.
Maybe something like the following?
When a new partition is created, it generally inherits structural
properties of the parent partitioned table, such as column
definitions, constraints, and storage settings.
To be more explicit about what's inherited, and not only focus on
what's not. (The commit message also says that the change describes
both what's inherited and what's not inherited)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
@ 2026-01-24 00:57 ` David G. Johnston <[email protected]>
2026-01-24 01:16 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 2 replies; 19+ messages in thread
From: David G. Johnston @ 2026-01-24 00:57 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Chao Li <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
Thank you for the review!
On Fri, Jan 23, 2026 at 3:07 AM Zsolt Parragi <[email protected]>
wrote:
> + When applied to a partitioned table, the constraint is altered on the
> + partitioned table definition is implicitly applied to all partitions.
>
> an "and" is missing here (definition and is)
>
Correct. But I'd go with:
...the constraint is altered on the partitioned table and implicitly
applied to all partitions.
> + When applied to a partitioned table, partition columns constraints
> + are implicitly renamed and specifying <literal>ONLY</literal>
> is not allowed.
> + </para>
>
> "partition columns constraints" - that seems like a strange/unclear
> wording to me. maybe ", the partition's column constraints are ... " ?
>
This is just wrong - only is not permitted for either columns or
constraints. Only cannot be implicit if cascading is allowed.
The unclear wording noted is just missing an "and" - of the three things
that can be renamed (relation name, column name, constraint name) only
these two apply.
"the partition columns and constraints..."
<para>
When applied to a partitioned table, partition columns and constraints
are implicitly renamed.
Specifying <literal>ONLY</literal> is not allowed, and this command
cannot be used on individual partitions.
</para>
<para>
For inheritance setups, index-based constraints are always considered
independent. ~~Dependent columns and constraints are implicitly
renamed
and specifying <literal>ONLY</literal> is not allowed.~~
</para>
The last sentence is redundant with the notes though, I'd remove it as
noted above:
<para>
For inheritance setups, the behavior described for partitioned tables
applies
only to the dependent column(s) on the descendant table(s). It is
always
allowed to target a descendant table with column altering commands on
independent
columns.
</para>
But that note should have "dependent constraints" added to it.
>
> + <para>
> + When applied to a partitioned table <literal>ONLY</literal> is
> implicit,
> + these forms must be applied separately to the partitioned table
> and/or to
> + individual partitions.
> + </para>
>
> "When applied to a partitioned table, <literal>ONLY</literal> is
> implicit and ..." (at multiple places, this is an example)
>
I've grown unfond of my suggested wording here during reviews too. But
because it's too wordy and a bit redundant.
"When applied to a partitioned table ONLY is implicit, however, this
command can be used on individual partitions."
has a better symmetry with:
Specifying <literal>ONLY</literal> is not allowed, and this command cannot
be used on individual partitions.
> "A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
> never removes any descendant columns, but instead marks them as
> independently defined rather than inherited."
>
> This part is now undocumented, it was only mentioned in this paragraph.
>
True, it's left implied instead of explicitly stated. Any column that
exists on a child but not the parent is by definition "independently
defined". So if either ONLY is supplied or the rules for cascading delete
are not met the result is children with independently defined columns with
that name. The original note was wrong anyway for the two-parent case -
the second parent prevents the marking as independent when the first
parent's column is dropped.
> > C2 - Sub-commands where using them with a partitioned table will
> automatically propagate to child partitions; ONLY prevents propagation; new
> partitions inherit the parent’s new setting; and child partitions can be
> set to different values than the parent.
>
> The documentation of this group is inconsistent.
>
> DROP CONSTRAINT mentions that individual partitions can be dropped
> separately:
>
> + When applied to a partitioned table, the constraint is dropped from
> + all existing partitions unless <literal>ONLY</literal> is specified.
> + Individual partitions may drop constraints independently of the
> + partitioned table.
>
> But most of the sub commands in the C2 group leave the last sentence
> out, and also the C7 (ADD table_constraint)
>
I didn't try and verify this dynamic or keep to it - though am on board
with considering changes that do so and remain accurate.
> Also, isn't DROP CONSTRAINT on a partition limited to constraints
> defined on that partition? So it would be better to say "may drop
> constraints defined directly on that individual partition
> independently".
>
"When applied to a partitioned table, dependent constraints are dropped
from ... is specified." should suffice.
I'd be fine leaving out the entire "Individual partitions may drop..."
business with that wording. It implies partitions may have independent
constraints which by extension may be targeted.
For Add Constraint - mention dependent constraints
"When applied to a partitioned table, the constraint is added to
the partitioned table and dependent constraints are added to all
partitions."
Which implies independent ones may exist and the logic for drop constraint
then follows.
(We should explain what happens if a partition already has an independent
constraint of the given name as that would be relevant here.)
> CREATE TABLE parent (id int, val int) PARTITION BY RANGE (id);
> ALTER TABLE parent ADD CONSTRAINT val_positive CHECK (val > 0);
> CREATE TABLE child PARTITION OF parent FOR VALUES FROM (1) TO (100);
> ALTER TABLE child DROP CONSTRAINT val_positive;
> -- ERROR: cannot drop inherited constraint "val_positive" of relation
> "child"
>
> + When a new partition is created, it generally inherits the current
> + definition-level properties of the parent partitioned table.
>
> Maybe something like the following?
>
> When a new partition is created, it generally inherits structural
> properties of the parent partitioned table, such as column
> definitions, constraints, and storage settings.
>
> To be more explicit about what's inherited, and not only focus on
> what's not. (The commit message also says that the change describes
> both what's inherited and what's not inherited)
I concur with the premise but how about:
When a partition is created, it inherits many of the properties
of the parent table. However, properties related to ownership,
schema, replica identity, row-level security configuration,
per-attribute statistics targets, and per-attribute options
are not inherited.
"new" is superfluous on this page.
"definition-level" are the only kind of properties that exist - I'm not
being wordy thinking people might believe properties includes data.
"parent" suffices as well.
We did all the work to identify things - use "however" instead of "in
particular" to give us credit for the work.
Even if a property is explicitly set for the partition it isn't "inherited"
- the partition has its own independent value that in a rare case might
happen to match the parent at the time of creation. (i.e., remove
automatically and 'not inherited unless')
I'm not that inclined to mention the inclusion list. The general premise
of assuming inherited unless told otherwise works fine here; minimal
future-proofing.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-24 01:16 ` David G. Johnston <[email protected]>
2026-01-26 04:16 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
1 sibling, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-24 01:16 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Chao Li <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Fri, Jan 23, 2026 at 5:57 PM David G. Johnston <
[email protected]> wrote:
>
>> "A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
>> never removes any descendant columns, but instead marks them as
>> independently defined rather than inherited."
>>
>> This part is now undocumented, it was only mentioned in this paragraph.
>>
>
> True, it's left implied instead of explicitly stated. Any column that
> exists on a child but not the parent is by definition "independently
> defined". So if either ONLY is supplied or the rules for cascading delete
> are not met the result is children with independently defined columns with
> that name.
>
> The original note was wrong anyway for the two-parent case - the second
> parent prevents the marking as independent when the first parent's column
> is dropped.
>
Decided to test this one and I see the original wording was correct and we
will need to keep a note that in the two-parent ONLY case the un-dropped
children are marked both dependent and independent.
Change:
<para>
For inheritance setups, a descendant column is removed only if both
of the
following are true: this is the only parent defining the column, and
the column
was never independently defined in the descendant.
</para>
To:
"For inheritance setups, a descendant column is removed only if all the
following are true: ONLY is not specified, no other parent defines the
column, and the column is not marked as having been independent.
Otherwise, the descendant column is instead marked as having
been independent.
If we think that deserves a bit longer explanation about that/why/how a
column can be both dependent and "having been independent" we should
cross-reference to a more appropriate location. Here we just state this is
one way that condition can materialize.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-24 01:16 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-26 04:16 ` Chao Li <[email protected]>
0 siblings, 0 replies; 19+ messages in thread
From: Chao Li @ 2026-01-26 04:16 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Jan 24, 2026, at 09:16, David G. Johnston <[email protected]> wrote:
>
>
> On Fri, Jan 23, 2026 at 5:57 PM David G. Johnston <[email protected]> wrote:
>
> "A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
> never removes any descendant columns, but instead marks them as
> independently defined rather than inherited."
>
> This part is now undocumented, it was only mentioned in this paragraph.
>
> True, it's left implied instead of explicitly stated. Any column that exists on a child but not the parent is by definition "independently defined". So if either ONLY is supplied or the rules for cascading delete are not met the result is children with independently defined columns with that name.
>
> The original note was wrong anyway for the two-parent case - the second parent prevents the marking as independent when the first parent's column is dropped.
>
> Decided to test this one and I see the original wording was correct and we will need to keep a note that in the two-parent ONLY case the un-dropped children are marked both dependent and independent.
>
> Change:
>
> <para>
> For inheritance setups, a descendant column is removed only if both of the
> following are true: this is the only parent defining the column, and the column
> was never independently defined in the descendant.
> </para>
>
> To:
>
> "For inheritance setups, a descendant column is removed only if all the following are true: ONLY is not specified, no other parent defines the column, and the column is not marked as having been independent. Otherwise, the descendant column is instead marked as having been independent.
>
> If we think that deserves a bit longer explanation about that/why/how a column can be both dependent and "having been independent" we should cross-reference to a more appropriate location. Here we just state this is one way that condition can materialize.
>
> David J.
>
Thanks a lot for the test. Included in v7.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-26 04:15 ` Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
1 sibling, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-26 04:15 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; Postgres hackers <[email protected]>; Zsolt Parragi <[email protected]>; +Cc: Amit Kapila <[email protected]>
> On Jan 24, 2026, at 08:57, David G. Johnston <[email protected]> wrote:
>
> Thank you for the review!
>
> On Fri, Jan 23, 2026 at 3:07 AM Zsolt Parragi <[email protected]> wrote:
> + When applied to a partitioned table, the constraint is altered on the
> + partitioned table definition is implicitly applied to all partitions.
>
> an "and" is missing here (definition and is)
>
> Correct. But I'd go with:
>
> ...the constraint is altered on the partitioned table and implicitly applied to all partitions.
>
Fixed with David’s version.
> + When applied to a partitioned table, partition columns constraints
> + are implicitly renamed and specifying <literal>ONLY</literal>
> is not allowed.
> + </para>
>
> "partition columns constraints" - that seems like a strange/unclear
> wording to me. maybe ", the partition's column constraints are ... " ?
>
> This is just wrong - only is not permitted for either columns or constraints. Only cannot be implicit if cascading is allowed.
> The unclear wording noted is just missing an "and" - of the three things that can be renamed (relation name, column name, constraint name) only these two apply.
> "the partition columns and constraints..."
>
> <para>
> When applied to a partitioned table, partition columns and constraints
> are implicitly renamed.
> Specifying <literal>ONLY</literal> is not allowed, and this command
> cannot be used on individual partitions.
> </para>
> <para>
> For inheritance setups, index-based constraints are always considered
> independent. ~~Dependent columns and constraints are implicitly renamed
> and specifying <literal>ONLY</literal> is not allowed.~~
> </para>
>
> The last sentence is redundant with the notes though, I'd remove it as noted above:
>
> <para>
> For inheritance setups, the behavior described for partitioned tables applies
> only to the dependent column(s) on the descendant table(s). It is always
> allowed to target a descendant table with column altering commands on independent
> columns.
> </para>
>
> But that note should have "dependent constraints" added to it.
>
I added the missing “and”. And updated the “for inheritance” paragraph as suggested.
But for "Specifying <literal>ONLY</literal> is not allowed, and this command, cannot be used on individual partitions.”, that doesn’t seem correct. See my test:
```
evantest=# create table root (i int, j int) partition by list(i);
CREATE TABLE
evantest=# create table p1 partition of root for values in (1);
CREATE TABLE
evantest=# alter table p1 rename to pp1; <== Rename a partition is allowed.
ALTER TABLE
evantest=# alter table only pp1 rename to p1; <== ONLY can be used, but just no effect
ALTER TABLE
```
> + <para>
> + When applied to a partitioned table <literal>ONLY</literal> is implicit,
> + these forms must be applied separately to the partitioned table and/or to
> + individual partitions.
> + </para>
>
> "When applied to a partitioned table, <literal>ONLY</literal> is
> implicit and ..." (at multiple places, this is an example)
>
> I've grown unfond of my suggested wording here during reviews too. But because it's too wordy and a bit redundant.
>
> "When applied to a partitioned table ONLY is implicit, however, this command can be used on individual partitions."
>
> has a better symmetry with:
>
> Specifying <literal>ONLY</literal> is not allowed, and this command cannot be used on individual partitions.
>
>
> "A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)
> never removes any descendant columns, but instead marks them as
> independently defined rather than inherited."
>
> This part is now undocumented, it was only mentioned in this paragraph.
>
> True, it's left implied instead of explicitly stated. Any column that exists on a child but not the parent is by definition "independently defined". So if either ONLY is supplied or the rules for cascading delete are not met the result is children with independently defined columns with that name. The original note was wrong anyway for the two-parent case - the second parent prevents the marking as independent when the first parent's column is dropped.
>
>
> > C2 - Sub-commands where using them with a partitioned table will automatically propagate to child partitions; ONLY prevents propagation; new partitions inherit the parent’s new setting; and child partitions can be set to different values than the parent.
>
> The documentation of this group is inconsistent.
>
> DROP CONSTRAINT mentions that individual partitions can be dropped separately:
>
> + When applied to a partitioned table, the constraint is dropped from
> + all existing partitions unless <literal>ONLY</literal> is specified.
> + Individual partitions may drop constraints independently of the
> + partitioned table.
>
> But most of the sub commands in the C2 group leave the last sentence
> out, and also the C7 (ADD table_constraint)
>
> I didn't try and verify this dynamic or keep to it - though am on board with considering changes that do so and remain accurate.
>
>
> Also, isn't DROP CONSTRAINT on a partition limited to constraints
> defined on that partition? So it would be better to say "may drop
> constraints defined directly on that individual partition
> independently".
>
> "When applied to a partitioned table, dependent constraints are dropped from ... is specified." should suffice.
> I'd be fine leaving out the entire "Individual partitions may drop..." business with that wording. It implies partitions may have independent constraints which by extension may be targeted.
>
> For Add Constraint - mention dependent constraints
> "When applied to a partitioned table, the constraint is added to the partitioned table and dependent constraints are added to all partitions."
>
> Which implies independent ones may exist and the logic for drop constraint then follows.
> (We should explain what happens if a partition already has an independent constraint of the given name as that would be relevant here.)
>
>
> CREATE TABLE parent (id int, val int) PARTITION BY RANGE (id);
> ALTER TABLE parent ADD CONSTRAINT val_positive CHECK (val > 0);
> CREATE TABLE child PARTITION OF parent FOR VALUES FROM (1) TO (100);
> ALTER TABLE child DROP CONSTRAINT val_positive;
> -- ERROR: cannot drop inherited constraint "val_positive" of relation "child"
This is true. I updated the paragraph for DROP CONSTRAINT as:
```
<para>
When applied to a partitioned table, the constraint is dropped from
all existing partitions unless <literal>ONLY</literal> is specified.
Dropping an inherited constaint from an individual partition is not allowed.
</para>
```
>
> + When a new partition is created, it generally inherits the current
> + definition-level properties of the parent partitioned table.
>
> Maybe something like the following?
>
> When a new partition is created, it generally inherits structural
> properties of the parent partitioned table, such as column
> definitions, constraints, and storage settings.
>
> To be more explicit about what's inherited, and not only focus on
> what's not. (The commit message also says that the change describes
> both what's inherited and what's not inherited)
>
> I concur with the premise but how about:
>
> When a partition is created, it inherits many of the properties
> of the parent table. However, properties related to ownership,
> schema, replica identity, row-level security configuration,
> per-attribute statistics targets, and per-attribute options
> are not inherited.
>
> "new" is superfluous on this page.
> "definition-level" are the only kind of properties that exist - I'm not being wordy thinking people might believe properties includes data.
> "parent" suffices as well.
> We did all the work to identify things - use "however" instead of "in particular" to give us credit for the work.
> Even if a property is explicitly set for the partition it isn't "inherited" - the partition has its own independent value that in a rare case might happen to match the parent at the time of creation. (i.e., remove automatically and 'not inherited unless')
> I'm not that inclined to mention the inclusion list. The general premise of assuming inherited unless told otherwise works fine here; minimal future-proofing.
>
I’m also not included to mention the inclusion list. My other patch [1] is changing replica identity to be inherited. Go forward, the inclusion list should shrink.
So updated with David’s version.
PFA v7.
[1] https://postgr.es/m/CAEoWx2nJ71hy8R614HQr7vQhkBReO9AANPODPg0aSQs74eOdLQ@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v7-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (33.8K, 2-v7-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From ab1bf0fb9c433180ef9af253849be7d85ed2b3fb Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v7] docs: clarify ALTER TABLE behavior on partitioned tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 319 +++++++++++++++++++++--------
doc/src/sgml/ref/create_table.sgml | 7 +
2 files changed, 236 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..f72644a098d 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if all the
+ following are true: ONLY is not specified, no other parent defines the
+ column, and the column is not marked as having been independent.
+ Otherwise, the descendant column is instead marked as having been independent.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +229,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +236,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +255,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +269,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +281,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +305,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +313,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +361,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +385,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +415,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +453,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +492,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +526,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +543,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +558,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +568,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +592,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +599,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +606,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +618,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +642,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition and implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +666,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +689,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +708,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Dropping an inherited constaint from an individual partition is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +734,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +743,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +754,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +780,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +805,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +825,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +844,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +863,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +894,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +910,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +939,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +968,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +988,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1006,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1017,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1033,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1051,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1064,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1078,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1139,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1159,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table, partition columns and constraints
+ are implicitly renamed and <literal>ONLY</literal> is implied, though
+ it may be specified.
+ </para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1180,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1563,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1942,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..2fcab0d874e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a partition is created, it inherits many of the properties
+ of the parent table. However, properties related to ownership,
+ schema, replica identity, row-level security configuration,
+ per-attribute statistics targets, and per-attribute options
+ are not inherited.
+ </para>
</listitem>
</varlistentry>
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-26 04:36 ` David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-26 04:36 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Zsolt Parragi <[email protected]>; Amit Kapila <[email protected]>
On Sunday, January 25, 2026, Chao Li <[email protected]> wrote:
>
> > <para>
> > When applied to a partitioned table, partition columns and
> constraints
> > are implicitly renamed.
> > Specifying <literal>ONLY</literal> is not allowed, and this command
> > cannot be used on individual partitions.
> > </para>
>
> But for "Specifying <literal>ONLY</literal> is not allowed, and this
> command, cannot be used on individual partitions.”, that doesn’t seem
> correct. See my test:
> ```
> evantest=# create table root (i int, j int) partition by list(i);
> CREATE TABLE
> evantest=# create table p1 partition of root for values in (1);
> CREATE TABLE
> evantest=# alter table p1 rename to pp1; <== Rename a partition is allowed.
> ALTER TABLE
> evantest=# alter table only pp1 rename to p1; <== ONLY can be used, but
> just no effect
> ALTER TABLE
> ```
>
I was mentally restricting the second sentence about ONLY to the column and
constraints renaming action, which are called out by the first sentence.
It makes little sense to talk about renaming the table, parent or child,
here in the context of ONLY. It goes without mention that table renaming
never cascades. Only is implied for that action, even if only should just
be considered valid on a parent in any case.
But I’d accept a sentence like: “Table renames always only apply to the
named table.” Added to that paragraph; it’s a convoluted command.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-26 05:13 ` Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-26 05:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Postgres hackers <[email protected]>; Zsolt Parragi <[email protected]>; Amit Kapila <[email protected]>
> On Jan 26, 2026, at 12:36, David G. Johnston <[email protected]> wrote:
>
> On Sunday, January 25, 2026, Chao Li <[email protected]> wrote:
>
> > <para>
> > When applied to a partitioned table, partition columns and constraints
> > are implicitly renamed.
> > Specifying <literal>ONLY</literal> is not allowed, and this command
> > cannot be used on individual partitions.
> > </para>
>
> But for "Specifying <literal>ONLY</literal> is not allowed, and this command, cannot be used on individual partitions.”, that doesn’t seem correct. See my test:
> ```
> evantest=# create table root (i int, j int) partition by list(i);
> CREATE TABLE
> evantest=# create table p1 partition of root for values in (1);
> CREATE TABLE
> evantest=# alter table p1 rename to pp1; <== Rename a partition is allowed.
> ALTER TABLE
> evantest=# alter table only pp1 rename to p1; <== ONLY can be used, but just no effect
> ALTER TABLE
> ```
>
> I was mentally restricting the second sentence about ONLY to the column and constraints renaming action, which are called out by the first sentence. It makes little sense to talk about renaming the table, parent or child, here in the context of ONLY. It goes without mention that table renaming never cascades. Only is implied for that action, even if only should just be considered valid on a parent in any case.
>
> But I’d accept a sentence like: “Table renames always only apply to the named table.” Added to that paragraph; it’s a convoluted command.
>
> David J.
>
How about this:
```
<para>
When applied to a partitioned table to rename columns or constraints,
the corresponding partition columns and constraints are renamed
implicitly. <literal>ONLY</literal> is not allowed, and the command
cannot be used on individual partitions. When the rename target is the
table name, only the named table is renamed.
</para>
```
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-26 05:38 ` David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-26 05:38 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Zsolt Parragi <[email protected]>; Amit Kapila <[email protected]>
On Sunday, January 25, 2026, Chao Li <[email protected]> wrote:
>
> How about this:
> ```
> <para>
> When applied to a partitioned table to rename columns or constraints,
> the corresponding partition columns and constraints are renamed
> implicitly. <literal>ONLY</literal> is not allowed, and the command
> cannot be used on individual partitions. When the rename target is the
> table name, only the named table is renamed.
> </para>
> ```
>
…are renamed implicitly, ONLY is not allowed, and the command cannot…(and
drop the mention of table renaming).
Or:
“When applied to a partitioned table’s name only the parent is changed,
partitions must be renamed separately. However, column or constraint
renaming must be done on the parent without ONLY, the corresponding
partition columns or constraints will be renamed implicitly.”
I find the repeated use of “table” “name(d)” and “rename(d)” in one
sentence worth avoiding.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-26 07:20 ` Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-26 07:20 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Postgres hackers <[email protected]>; Zsolt Parragi <[email protected]>; Amit Kapila <[email protected]>
> On Jan 26, 2026, at 13:38, David G. Johnston <[email protected]> wrote:
>
> On Sunday, January 25, 2026, Chao Li <[email protected]> wrote:
>
> How about this:
> ```
> <para>
> When applied to a partitioned table to rename columns or constraints,
> the corresponding partition columns and constraints are renamed
> implicitly. <literal>ONLY</literal> is not allowed, and the command
> cannot be used on individual partitions. When the rename target is the
> table name, only the named table is renamed.
> </para>
> ```
>
> …are renamed implicitly, ONLY is not allowed, and the command cannot…(and drop the mention of table renaming).
>
> Or:
>
> “When applied to a partitioned table’s name only the parent is changed, partitions must be renamed separately. However, column or constraint renaming must be done on the parent without ONLY, the corresponding partition columns or constraints will be renamed implicitly.”
>
> I find the repeated use of “table” “name(d)” and “rename(d)” in one sentence worth avoiding.
Looks good. I just did a couple of tiny tweaks:
* Added a comma between “table’s name” and “only”: “when applied to a partitioned table’s name, only the parent …”
* Replaced “however” with “in contrast”.
PFA v8.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v8-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (33.9K, 2-v8-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From e815a3c24ba953f62108565e2ee6691d733b2d65 Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v8] docs: clarify ALTER TABLE behavior on partitioned tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 320 +++++++++++++++++++++--------
doc/src/sgml/ref/create_table.sgml | 7 +
2 files changed, 237 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..b3a4f33f446 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if all the
+ following are true: ONLY is not specified, no other parent defines the
+ column, and the column is not marked as having been independent.
+ Otherwise, the descendant column is instead marked as having been independent.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +229,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +236,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +255,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +269,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +281,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +305,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +313,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +361,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +385,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +415,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +453,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +492,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +526,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +543,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +558,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +568,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +592,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +599,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +606,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +618,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +642,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition and implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +666,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +689,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +708,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Dropping an inherited constaint from an individual partition is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +734,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +743,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +754,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +780,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +805,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +825,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +844,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +863,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +894,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +910,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +939,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +968,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +988,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1006,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1017,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1033,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1051,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1064,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1078,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1139,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1159,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table’s name, only the parent is changed,
+ partitions must be renamed separately. In contrast, column or constraint
+ renaming must be done on the parent without <literal>ONLY</literal>, the
+ corresponding partition columns or constraints will be renamed implicitly.
+ </para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1181,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1564,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1943,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..2fcab0d874e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a partition is created, it inherits many of the properties
+ of the parent table. However, properties related to ownership,
+ schema, replica identity, row-level security configuration,
+ per-attribute statistics targets, and per-attribute options
+ are not inherited.
+ </para>
</listitem>
</varlistentry>
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-26 14:47 ` Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Zsolt Parragi @ 2026-01-26 14:47 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: David G. Johnston <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
There's one typo (constaint => constraint), otherwise it looks good to me.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
@ 2026-01-26 22:41 ` Chao Li <[email protected]>
2026-01-28 22:21 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Chao Li @ 2026-01-26 22:41 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: David G. Johnston <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Jan 26, 2026, at 22:47, Zsolt Parragi <[email protected]> wrote:
>
> There's one typo (constaint => constraint), otherwise it looks good to me.
Good catch. Fixed.
PFA v8.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v8-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (33.9K, 2-v8-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From 486e2091b76194cfe559763c39b34e64fbf1174f Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v8] docs: clarify ALTER TABLE behavior on partitioned tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 320 +++++++++++++++++++++--------
doc/src/sgml/ref/create_table.sgml | 7 +
2 files changed, 237 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..b19d30761d1 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if all the
+ following are true: ONLY is not specified, no other parent defines the
+ column, and the column is not marked as having been independent.
+ Otherwise, the descendant column is instead marked as having been independent.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +229,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +236,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +255,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +269,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +281,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +305,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +313,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +361,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +385,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +415,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +453,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +492,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +526,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +543,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +558,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +568,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +592,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +599,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +606,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +618,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +642,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition and implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +666,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +689,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +708,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Dropping an inherited constraint from an individual partition is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +734,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +743,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +754,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +780,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +805,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +825,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +844,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +863,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +894,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +910,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +939,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +968,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +988,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1006,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1017,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1033,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1051,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1064,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1078,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1139,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1159,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table’s name, only the parent is changed,
+ partitions must be renamed separately. In contrast, column or constraint
+ renaming must be done on the parent without <literal>ONLY</literal>, the
+ corresponding partition columns or constraints will be renamed implicitly.
+ </para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1181,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1564,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1943,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..2fcab0d874e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a partition is created, it inherits many of the properties
+ of the parent table. However, properties related to ownership,
+ schema, replica identity, row-level security configuration,
+ per-attribute statistics targets, and per-attribute options
+ are not inherited.
+ </para>
</listitem>
</varlistentry>
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-28 22:21 ` David G. Johnston <[email protected]>
2026-01-29 01:43 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: David G. Johnston @ 2026-01-28 22:21 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Mon, Jan 26, 2026 at 3:42 PM Chao Li <[email protected]> wrote:
>
> PFA v8.
>
>
Delta:
Added missing literal element for ONLY
Expanded usage of "propagates" wording and made it active voice
Fixed wording of "partitioned table and/or partitions" to be more correct
and succinct: "parent and individual partitions"
Modified the 'sequences' wording to try and make it more obvious that every
partition has its own sequence that is affected rather than a single
sequence for the entire partitioned table.
Note, I think the base patch should have all the isolated
newlines-between-para changes removed from it. If anything, the more
common, and for me more readable, flow is to have those line breaks. In any
case, the file is not consistent on one or the other before or after these
unrelated fixes so we shouldn't be making them. For new paragraphs,
matching the block they are added in works - and if adding the second
paragraph I'd include a newline.
Note, the convention is to use two spaces after a full stop (period). Not
too worried about this one here, just an FYI.
David J.
Attachments:
[text/x-patch] v9-0002-diff-over-v8.patch (11.2K, 3-v9-0002-diff-over-v8.patch)
download | inline diff:
From 975d3a6fc08d58538f8160c33ce8cc3ed01e062e Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
Date: Wed, 28 Jan 2026 15:07:47 -0700
Subject: [PATCH v9 2/2] diff over v8
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index b19d30761d1..f5ce556dafd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -204,8 +204,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
For inheritance setups, a descendant column is removed only if all the
- following are true: ONLY is not specified, no other parent defines the
- column, and the column is not marked as having been independent.
+ following are true: <literal>ONLY</literal> is not specified, no other
+ parent defines the column, and the column is not marked as having been independent.
Otherwise, the descendant column is instead marked as having been independent.
</para>
</listitem>
@@ -256,7 +256,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in the table to change.
</para>
<para>
- When applied to a partitioned table, the default value is propagated
+ When applied to a partitioned table, the default value propagates
to all partitions unless <literal>ONLY</literal> is specified.
</para>
</listitem>
@@ -314,8 +314,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is not necessary because such columns never have statistics.
</para>
<para>
- When applied to a partitioned table, the generation expression is
- propagated to all partitions unless <literal>ONLY</literal>
+ When applied to a partitioned table, the generation expression
+ propagates to all partitions unless <literal>ONLY</literal>
is specified.
</para>
</listitem>
@@ -340,9 +340,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the generation expression is
- dropped from the partitioned table definition and from all
- partitions. Specifying <literal>ONLY</literal> is not allowed, and
- this command cannot be used on individual partitions.
+ removed from all partitions. Specifying <literal>ONLY</literal> is not
+ allowed, and this command cannot be used on individual partitions.
</para>
</listitem>
</varlistentry>
@@ -367,8 +366,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
notice is issued instead.
</para>
<para>
- When applied to a partitioned table, the identity property is defined
- on the partitioned table and is applied to all partitions.
+ When applied to a partitioned table, the action propagates to all partitions.
Specifying <literal>ONLY</literal> is not allowed, and these forms
cannot be used on individual partitions.
</para>
@@ -386,9 +384,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
as <literal>INCREMENT BY</literal>.
</para>
<para>
- When applied to a partitioned table, these forms alter the sequence
+ When applied to a partitioned table, these forms alter the sequences
associated with the identity column on the partitioned table
- and all individual partitions. Specifying <literal>ONLY</literal>
+ and those on individual partitions. Specifying <literal>ONLY</literal>
is not allowed, and these forms cannot be used on individual
partitions.
</para>
@@ -416,8 +414,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
<para>
- For partitioned tables, this action applies to the partitioned table
- and all of its partitions unless <literal>ONLY</literal> is specified.
+ For partitioned tables, this action propagates to all partitions
+ unless <literal>ONLY</literal> is specified.
</para>
</listitem>
</varlistentry>
@@ -455,8 +453,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- these forms must be applied separately to the partitioned table and/or to
- individual partitions.
+ these forms must be applied separately to the parent table and partitions.
</para>
</listitem>
</varlistentry>
@@ -493,7 +490,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="storage-toast"/> for more information.
</para>
<para>
- When applied to a partitioned table, the storage setting is propagated
+ When applied to a partitioned table, the storage setting propagates
to all existing partitions unless <literal>ONLY</literal> is specified.
</para>
</listitem>
@@ -528,8 +525,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- the compression method must be applied separately to the partitioned table
- and/or to individual partitions.
+ the compression method must be applied separately to the parent table
+ and partitions.
</para>
</listitem>
</varlistentry>
@@ -643,8 +640,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
this fashion, but see below.
</para>
<para>
- When applied to a partitioned table, the constraint is altered on the
- partitioned table definition and implicitly applied to all partitions.
+ When applied to a partitioned table, the constraint alterations
+ propagate to all partitions.
Specifying <literal>ONLY</literal> is not allowed, and this command
cannot be used on individual partitions.
</para>
@@ -690,11 +687,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
<para>
- When applied to a partitioned table, the constraint is validated on the
- partitioned table and on all partitions. Unlike most constraint-related
- sub-commands, individual partitions may validate the constraint
- independently of the partitioned table, so long as the partitioned
- table itself is not yet validated.
+ When applied to a partitioned table, validation propogates to all partitions.
+ Unlike most constraint-related sub-commands, individual partitions may
+ validate the constraint independently of the partitioned table,
+ so long as the partitioned table itself is not yet validated.
</para>
</listitem>
</varlistentry>
@@ -710,7 +706,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, the constraint is dropped from
- all existing partitions unless <literal>ONLY</literal> is specified.
+ all partitions unless <literal>ONLY</literal> is specified.
Dropping an inherited constraint from an individual partition is not allowed.
</para>
</listitem>
@@ -787,8 +783,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- these forms must be applied separately to the partitioned table
- and/or to individual partitions.
+ these forms must be applied separately to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -807,8 +802,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- these forms must be applied separately to the partitioned table and/or to
- individual partitions.
+ these forms must be applied separately to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -827,8 +821,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- this form must be applied separately to the partitioned table and/or to
- individual partitions.
+ this form must be applied separately to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -897,7 +890,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
only the default for newly created partitions is changed.
Specifying <literal>DEFAULT</literal> removes a previously set access method,
causing future partitions to use <varname>default_table_access_method</varname>.
- Individual partitions are affected just like any other regular table.
+ Individual partitions are affected just like a regular table.
</para>
</listitem>
</varlistentry>
@@ -1080,8 +1073,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- this form must be applied separately to the partitioned table and/or to
- individual partitions.
+ this form must be applied separately to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -1142,8 +1134,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- this form must be applied independently to the partitioned table and/or
- to individual partitions.
+ this form must be applied independently to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -1183,8 +1174,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, <literal>ONLY</literal> is implicit,
- this form must be applied independently to the partitioned table and/or
- to individual partitions.
+ this form must be applied independently to the parent table and individual partitions.
</para>
</listitem>
</varlistentry>
@@ -1575,7 +1565,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Note that for partitioned tables, <literal>ONLY</literal> does not control
recursion for all <command>ALTER TABLE</command> actions. Some actions
apply only to the partitioned table itself, regardless of whether
- <literal>ONLY</literal> is specified, while others are propagated to
+ <literal>ONLY</literal> is specified, while others propagate to
partitions. The exact behavior depends on the specific action being
performed.
</para>
--
2.43.0
[text/x-patch] v9-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch (33.8K, 4-v9-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned-.patch)
download | inline diff:
From 0d02cb5783c39c511bc03f4fc1f99c2ec1d6d4c7 Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v9 1/2] docs: clarify ALTER TABLE behavior on partitioned
tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..b19d30761d1 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if all the
+ following are true: ONLY is not specified, no other parent defines the
+ column, and the column is not marked as having been independent.
+ Otherwise, the descendant column is instead marked as having been independent.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +229,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +236,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +255,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value is propagated
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +269,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +281,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +305,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +313,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ propagated to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ dropped from the partitioned table definition and from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +361,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the identity property is defined
+ on the partitioned table and is applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +385,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequence
+ associated with the identity column on the partitioned table
+ and all individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +415,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action applies to the partitioned table
+ and all of its partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +453,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +492,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting is propagated
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +526,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +543,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +558,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +568,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +592,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +599,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +606,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +618,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +642,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is altered on the
+ partitioned table definition and implicitly applied to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +666,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +689,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is validated on the
+ partitioned table and on all partitions. Unlike most constraint-related
+ sub-commands, individual partitions may validate the constraint
+ independently of the partitioned table, so long as the partitioned
+ table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +708,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all existing partitions unless <literal>ONLY</literal> is specified.
+ Dropping an inherited constraint from an individual partition is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +734,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +743,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +754,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +780,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table
+ and/or to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +805,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +825,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +844,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +863,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +894,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like any other regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +910,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +939,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +968,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +988,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +1006,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1017,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1033,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1051,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1064,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1078,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the partitioned table and/or to
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1139,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1159,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table’s name, only the parent is changed,
+ partitions must be renamed separately. In contrast, column or constraint
+ renaming must be done on the parent without <literal>ONLY</literal>, the
+ corresponding partition columns or constraints will be renamed implicitly.
+ </para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1181,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the partitioned table and/or
+ to individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1564,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others are propagated to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1943,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..2fcab0d874e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a partition is created, it inherits many of the properties
+ of the parent table. However, properties related to ownership,
+ schema, replica identity, row-level security configuration,
+ per-attribute statistics targets, and per-attribute options
+ are not inherited.
+ </para>
</listitem>
</varlistentry>
--
2.43.0
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-28 22:21 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
@ 2026-01-29 01:43 ` Chao Li <[email protected]>
2026-01-29 01:59 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-03-30 03:06 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
0 siblings, 2 replies; 19+ messages in thread
From: Chao Li @ 2026-01-29 01:43 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Jan 29, 2026, at 06:21, David G. Johnston <[email protected]> wrote:
>
> On Mon, Jan 26, 2026 at 3:42 PM Chao Li <[email protected]> wrote:
>
> PFA v8.
>
>
> Delta:
>
> Added missing literal element for ONLY
Accepted.
> Expanded usage of "propagates" wording and made it active voice
I doubt this change. But as a non-English speaker, I don’t want to argue about English, so, I will just accept the change.
> Fixed wording of "partitioned table and/or partitions" to be more correct and succinct: "parent and individual partitions"
Accepted.
> Modified the 'sequences' wording to try and make it more obvious that every partition has its own sequence that is affected rather than a single sequence for the entire partitioned table.
Accepted.
>
> Note, I think the base patch should have all the isolated newlines-between-para changes removed from it. If anything, the more common, and for me more readable, flow is to have those line breaks. In any case, the file is not consistent on one or the other before or after these unrelated fixes so we shouldn't be making them. For new paragraphs, matching the block they are added in works - and if adding the second paragraph I'd include a newline.
Yeah, I actually inclined to have a newline between paragraphs, but the doc already contains inconsistent styles, so I just tried to follow surrounding paragraphs’ style to decide if a new line needed.
>
> Note, the convention is to use two spaces after a full stop (period). Not too worried about this one here, just an FYI.
>
Thanks for the information.
PFA v10 - just integrated v9-0002-diff into the patch.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v10-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned.patch (33.6K, 2-v10-0001-docs-clarify-ALTER-TABLE-behavior-on-partitioned.patch)
download | inline diff:
From 28e1e3c09d835777baaba85f6059b93ea677e3b2 Mon Sep 17 00:00:00 2001
From: Chao Li <[email protected]>
Date: Wed, 7 Jan 2026 14:50:36 -0700
Subject: [PATCH v10] docs: clarify ALTER TABLE behavior on partitioned tables
Document how individual ALTER TABLE sub-commands behave when applied to
partitioned tables.
The existing ALTER TABLE documentation provides limited or incomplete
information about how commands interact with partitioned tables, leaving
users to infer behavior from experimentation. This patch systematically
clarifies, for each relevant sub-command, whether changes propagate to
existing partitions, can be applied independently to partitions, and
how the ONLY keyword behaves.
In addition, clarify in CREATE TABLE documentation which parent table
settings are inherited by newly created partitions and which are not.
This makes explicit that while most definition-level properties are
inherited from the partitioned table, certain per-relation settings
(such as ownership, schema, replica identity, row-level security, and
per-attribute statistics/options) are not, unless explicitly specified.
No behavior is changed by this patch; it is purely a documentation update
intended to make existing semantics explicit and easier to understand.
Author: Chao Li <[email protected]>
Author: David G. Johnston <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=mYhCfsnHaN96Qqwq5b0GVS2YgO3zpVqPPRd_iO52wRw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 310 ++++++++++++++++++++---------
doc/src/sgml/ref/create_table.sgml | 7 +
2 files changed, 227 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1bd479c917a..f5ce556dafd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -171,6 +171,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
is specified and a column already exists with this name,
no error is thrown.
</para>
+ <para>
+ When applied to a partitioned table, the column is added to the
+ partitioned table definition and is implicitly added to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -190,6 +196,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
does not exist, no error is thrown. In this case a notice
is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the column is removed from the
+ partitioned table definition and is implicitly removed from all
+ partitions. Specifying <literal>ONLY</literal> is not allowed, and
+ this command cannot be used on individual partitions.
+ </para>
+ <para>
+ For inheritance setups, a descendant column is removed only if all the
+ following are true: <literal>ONLY</literal> is not specified, no other
+ parent defines the column, and the column is not marked as having been independent.
+ Otherwise, the descendant column is instead marked as having been independent.
+ </para>
</listitem>
</varlistentry>
@@ -211,7 +229,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
clause must be provided if there is no implicit or assignment
cast from old to new type.
</para>
-
<para>
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
@@ -219,6 +236,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the data type change is applied
+ to the partitioned table definition and is implicitly applied to all
+ partitions. Specifying <literal>ONLY</literal> is not allowed,
+ and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -232,6 +255,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or <command>UPDATE</command> commands; it does not cause rows already
in the table to change.
</para>
+ <para>
+ When applied to a partitioned table, the default value propagates
+ to all partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -242,7 +269,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
-
<para>
<literal>SET NOT NULL</literal> may only be applied to a column
provided none of the records in the table contain a
@@ -255,18 +281,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If a column has an invalid not-null constraint,
<literal>SET NOT NULL</literal> validates it.
</para>
-
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
- on a column if it is marked <literal>NOT NULL</literal> in the parent
- table. To drop the <literal>NOT NULL</literal> constraint from all the
- partitions, perform <literal>DROP NOT NULL</literal> on the parent
- table. Even if there is no <literal>NOT NULL</literal> constraint on the
- parent, such a constraint can still be added to individual partitions,
- if desired; that is, the children can disallow nulls even if the parent
- allows them, but not the other way around. It is also possible to drop
- the <literal>NOT NULL</literal> constraint from <literal>ONLY</literal>
- the parent table, which does not remove it from the children.
+ If this table is a partition, one cannot perform
+ <literal>DROP NOT NULL</literal> on a column if it is marked
+ <literal>NOT NULL</literal> in the parent table. To drop the
+ <literal>NOT NULL</literal> constraint from all partitions, perform
+ <literal>DROP NOT NULL</literal> on the parent table. Even if there is
+ no <literal>NOT NULL</literal> constraint on the parent, such a
+ constraint can still be added to individual partitions; that is, the
+ children can disallow nulls even if the parent allows them, but not
+ the other way around. It is also possible to apply
+ <literal>SET NOT NULL</literal> or <literal>DROP NOT NULL</literal> to
+ <literal>ONLY</literal> the parent table.
</para>
</listitem>
</varlistentry>
@@ -279,7 +305,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in a stored generated column is rewritten and all the future changes
will apply the new generation expression.
</para>
-
<para>
When this form is used on a stored generated column, its statistics
are removed, so running
@@ -288,6 +313,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
For a virtual generated column, <command>ANALYZE</command>
is not necessary because such columns never have statistics.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression
+ propagates to all partitions unless <literal>ONLY</literal>
+ is specified.
+ </para>
</listitem>
</varlistentry>
@@ -299,17 +329,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Existing data in the columns is retained, but future changes will no
longer apply the generation expression.
</para>
-
<para>
This form is currently only supported for stored generated columns (not
virtual ones).
</para>
-
<para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
column is not a generated column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the generation expression is
+ removed from all partitions. Specifying <literal>ONLY</literal> is not
+ allowed, and this command cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -327,12 +360,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
and <command>UPDATE</command> commands; they do not cause rows
already in the table to change.
</para>
-
<para>
If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the
column is not an identity column, no error is thrown. In this case a
notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the action propagates to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and these forms
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -346,6 +383,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
supported by <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link> such
as <literal>INCREMENT BY</literal>.
</para>
+ <para>
+ When applied to a partitioned table, these forms alter the sequences
+ associated with the identity column on the partitioned table
+ and those on individual partitions. Specifying <literal>ONLY</literal>
+ is not allowed, and these forms cannot be used on individual
+ partitions.
+ </para>
</listitem>
</varlistentry>
@@ -369,6 +413,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>SET STATISTICS</literal> acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ For partitioned tables, this action propagates to all partitions
+ unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -403,6 +451,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Changing per-attribute options acquires a
<literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the parent table and partitions.
+ </para>
</listitem>
</varlistentry>
@@ -437,6 +489,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
during future table updates.
See <xref linkend="storage-toast"/> for more information.
</para>
+ <para>
+ When applied to a partitioned table, the storage setting propagates
+ to all existing partitions unless <literal>ONLY</literal> is specified.
+ </para>
</listitem>
</varlistentry>
@@ -467,6 +523,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
consulting the <xref linkend="guc-default-toast-compression"/> setting
at the time of data insertion to determine the method to use.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ the compression method must be applied separately to the parent table
+ and partitions.
+ </para>
</listitem>
</varlistentry>
@@ -479,7 +540,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
VALID</literal>, which is currently only allowed for foreign-key,
<literal>CHECK</literal>, and not-null constraints.
</para>
-
<para>
Normally, this form will cause a scan of the table to verify that all
existing rows in the table satisfy the new constraint. But if
@@ -495,7 +555,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See <xref linkend="sql-altertable-notes"/> below for more information
about using the <literal>NOT VALID</literal> option.
</para>
-
<para>
Although most forms of <literal>ADD
<replaceable class="parameter">table_constraint</replaceable></literal>
@@ -506,12 +565,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
referenced table, in addition to the lock on the table on which the
constraint is declared.
</para>
-
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
</para>
-
+ <para>
+ When applied to a partitioned table, the constraint is added to the
+ partitioned table and is implicitly added to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed.
+ </para>
+ <para>
+ For inheritance setups, the constraint is not added to child tables unless it is
+ a <literal>CHECK</literal> constraint that is inheritable (that is, not declared <literal>NO INHERIT</literal>).
+ </para>
</listitem>
</varlistentry>
@@ -523,7 +589,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
constraint to a table based on an existing unique index. All the
columns of the index will be included in the constraint.
</para>
-
<para>
The index cannot have expression columns nor be a partial index.
Also, it must be a b-tree index with default sort ordering. These
@@ -531,7 +596,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal>
command.
</para>
-
<para>
If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not
already marked <literal>NOT NULL</literal>, then this command will attempt to
@@ -539,13 +603,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
That requires a full table scan to verify the column(s) contain no
nulls. In all other cases, this is a fast operation.
</para>
-
<para>
If a constraint name is provided then the index will be renamed to match
the constraint name. Otherwise the constraint will be named the same as
the index.
</para>
-
<para>
After this command is executed, the index is <quote>owned</quote> by the
constraint, in the same way as if the index had been built by
@@ -553,11 +615,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
command. In particular, dropping the constraint will make the index
disappear too.
</para>
-
<para>
- This form is not currently supported on partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
-
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -578,6 +639,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
created. Currently only foreign key constraints may be altered in
this fashion, but see below.
</para>
+ <para>
+ When applied to a partitioned table, the constraint alterations
+ propagate to all partitions.
+ Specifying <literal>ONLY</literal> is not allowed, and this command
+ cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -596,7 +663,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-inheritable on a table with children, then the corresponding
constraint on children will be marked as no longer inherited,
but not removed.
- </para>
+ </para>
+ <para>
+ These forms are not supported on partitioned tables, but can be used
+ on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -615,6 +686,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ When applied to a partitioned table, validation propogates to all partitions.
+ Unlike most constraint-related sub-commands, individual partitions may
+ validate the constraint independently of the partitioned table,
+ so long as the partitioned table itself is not yet validated.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +704,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
+ <para>
+ When applied to a partitioned table, the constraint is dropped from
+ all partitions unless <literal>ONLY</literal> is specified.
+ Dropping an inherited constraint from an individual partition is not allowed.
+ </para>
</listitem>
</varlistentry>
@@ -648,7 +730,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
</para>
-
<para>
The trigger firing mechanism is also affected by the configuration
variable <xref linkend="guc-session-replication-role"/>. Simply enabled
@@ -658,7 +739,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will
fire regardless of the current replication role.
</para>
-
<para>
The effect of this mechanism is that in the default configuration,
triggers do not fire on replicas. This is useful because if a trigger
@@ -670,13 +750,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
replicas.
</para>
-
<para>
When this command is applied to a partitioned table, the states of
- corresponding clone triggers in the partitions are updated too,
- unless <literal>ONLY</literal> is specified.
+ corresponding clone triggers are updated too, unless <literal>ONLY</literal>
+ is specified.
+ </para>
+ <para>
+ For inheritance setups <literal>ONLY</literal> is implied, though it may
+ be specified.
</para>
-
<para>
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
</para>
@@ -694,12 +776,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
are always applied in order to keep views working even if the current
session is in a non-default replication role.
</para>
-
<para>
The rule firing mechanism is also affected by the configuration variable
<xref linkend="guc-session-replication-role"/>, analogous to triggers as
described above.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -715,6 +800,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ these forms must be applied separately to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -730,6 +819,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
See also
<link linkend="sql-createpolicy"><command>CREATE POLICY</command></link>.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -744,6 +837,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -759,6 +856,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -786,11 +887,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
When applied to a partitioned table, there is no data to rewrite,
- but partitions created afterwards will default to the given access
- method unless overridden by a <literal>USING</literal> clause.
- Specifying <varname>DEFAULT</varname> removes a previous value,
- causing future partitions to default to
- <varname>default_table_access_method</varname>.
+ only the default for newly created partitions is changed.
+ Specifying <literal>DEFAULT</literal> removes a previously set access method,
+ causing future partitions to use <varname>default_table_access_method</varname>.
+ Individual partitions are affected just like a regular table.
</para>
</listitem>
</varlistentry>
@@ -803,12 +903,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
- When applied to a partitioned table, nothing is moved, but any
- partitions created afterwards with
- <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
- unless overridden by a <literal>TABLESPACE</literal> clause.
+ When applied to a partitioned table, no data is moved, only the default
+ for newly created partitions is changed.
</para>
-
<para>
All tables in the current database in a tablespace can be moved by using
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
@@ -835,15 +932,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
to a temporary table.
</para>
-
<para>
This also changes the persistence of any sequences linked to the table
(for identity or serial columns). However, it is also possible to
change the persistence of such sequences separately.
</para>
-
<para>
- This form is not supported for partitioned tables.
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
</para>
</listitem>
</varlistentry>
@@ -865,12 +961,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
time the table is locked so currently executing queries will not be
affected.
</para>
-
<para>
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
fillfactor, TOAST and autovacuum storage parameters, as well as the
planner parameter <varname>parallel_workers</varname>.
</para>
+ <para>
+ This form is not supported on partitioned tables, but can be used on
+ individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -882,6 +981,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
defaults. As with <literal>SET</literal>, a table rewrite might be
needed to update the table entirely.
</para>
+ <para>
+ When applied to a partitioned table, this form is accepted but has no
+ practical effect. It can be used on individual partitions, where it
+ behaves as for a regular table.
+ </para>
</listitem>
</varlistentry>
@@ -895,7 +999,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types.
</para>
-
<para>
In addition, all <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints on the parent must also exist on the child, except those
@@ -907,6 +1010,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>FOREIGN KEY</literal> constraints are not considered, but
this might change in the future.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -919,6 +1026,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Queries against the parent table will no longer include records drawn
from the target table.
</para>
+ <para>
+ This form does not support partitioned tables; it cannot be applied to
+ either a partitioned table or its partitions.
+ </para>
</listitem>
</varlistentry>
@@ -933,6 +1044,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
that <command>CREATE TABLE OF</command> would permit an equivalent table
definition.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -942,6 +1057,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form dissociates a typed table from its type.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ and this form cannot be used on individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -952,6 +1071,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form changes the owner of the table, sequence, view, materialized view,
or foreign table to the specified user.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied separately to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1008,6 +1131,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
</variablelist></para>
+
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1022,6 +1150,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the index is renamed as well.
There is no effect on the stored data.
</para>
+ <para>
+ When applied to a partitioned table’s name, only the parent is changed,
+ partitions must be renamed separately. In contrast, column or constraint
+ renaming must be done on the parent without <literal>ONLY</literal>, the
+ corresponding partition columns or constraints will be renamed implicitly.
+ </para>
+ <para>
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
+ </para>
</listitem>
</varlistentry>
@@ -1032,6 +1172,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
</para>
+ <para>
+ When applied to a partitioned table, <literal>ONLY</literal> is implicit,
+ this form must be applied independently to the parent table and individual partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1410,6 +1554,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>*</literal> can be specified after the table name to explicitly
indicate that descendant tables are included.
</para>
+
+ <para>
+ For a table that uses table inheritance, descendant tables are those
+ that inherit from the named table, directly or indirectly.
+ For a partitioned table, descendant tables are its partitions.
+ </para>
+
+ <para>
+ Note that for partitioned tables, <literal>ONLY</literal> does not control
+ recursion for all <command>ALTER TABLE</command> actions. Some actions
+ apply only to the partitioned table itself, regardless of whether
+ <literal>ONLY</literal> is specified, while others propagate to
+ partitions. The exact behavior depends on the specific action being
+ performed.
+ </para>
+
+ <para>
+ Users should consult the documentation of each individual
+ <command>ALTER TABLE</command> action to determine whether and how it
+ affects partitions.
+ </para>
</listitem>
</varlistentry>
@@ -1768,45 +1933,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
- the same to the descendants. This ensures that the descendants always
- have columns matching the parent. Similarly, a <literal>CHECK</literal>
- constraint cannot be renamed in the parent without also renaming it in
- all descendants, so that <literal>CHECK</literal> constraints also match
- between the parent and its descendants. (That restriction does not apply
- to index-based constraints, however.)
- Also, because selecting from the parent also selects from its descendants,
- a constraint on the parent cannot be marked valid unless it is also marked
- valid for those descendants. In all of these cases, <command>ALTER TABLE
- ONLY</command> will be rejected.
- </para>
-
- <para>
- A recursive <literal>DROP COLUMN</literal> operation will remove a
- descendant table's column only if the descendant does not inherit
- that column from any other parents and never had an independent
- definition of the column. A nonrecursive <literal>DROP
- COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
- COLUMN</command>) never removes any descendant columns, but
- instead marks them as independently defined rather than inherited.
- A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
- partitioned table, because all partitions of a table must have the same
- columns as the partitioning root.
- </para>
-
- <para>
- The actions for identity columns (<literal>ADD
- GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
- IDENTITY</literal>), as well as the actions
- <literal>CLUSTER</literal>, <literal>OWNER</literal>,
- and <literal>TABLESPACE</literal> never recurse to descendant tables;
- that is, they always act as though <literal>ONLY</literal> were specified.
- Actions affecting trigger states recurse to partitions of partitioned
- tables (unless <literal>ONLY</literal> is specified), but never to
- traditional-inheritance descendants.
- Adding a constraint recurses only for <literal>CHECK</literal> constraints
- that are not marked <literal>NO INHERIT</literal>.
+ For inheritance setups, the behavior described for partitioned tables applies
+ only to the dependent column(s) on the descendant table(s). It is always
+ allowed to target a descendant table with column altering commands on independent
+ columns.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c5a763d45..2fcab0d874e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -633,6 +633,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
concurrent operations on the partitioned table.
</para>
+ <para>
+ When a partition is created, it inherits many of the properties
+ of the parent table. However, properties related to ownership,
+ schema, replica identity, row-level security configuration,
+ per-attribute statistics targets, and per-attribute options
+ are not inherited.
+ </para>
</listitem>
</varlistentry>
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-28 22:21 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-29 01:43 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-01-29 01:59 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: David G. Johnston @ 2026-01-29 01:59 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Wed, Jan 28, 2026 at 6:44 PM Chao Li <[email protected]> wrote:
>
> PFA v10 - just integrated v9-0002-diff into the patch.
>
>
I've marked it Ready for Committer since we are on the same page.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: docs: clarify ALTER TABLE behavior on partitioned tables
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-21 03:08 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 05:29 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-23 10:07 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 04:15 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 04:36 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 05:13 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 05:38 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-26 07:20 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-26 14:47 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
2026-01-28 22:21 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-29 01:43 ` Re: docs: clarify ALTER TABLE behavior on partitioned tables Chao Li <[email protected]>
@ 2026-03-30 03:06 ` Chao Li <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: Chao Li @ 2026-03-30 03:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Zsolt Parragi <[email protected]>; Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Mar 30, 2026, at 03:01, Tom Lane <[email protected]> wrote:
>
> Chao Li <[email protected]> writes:
>> PFA v10 - just integrated v9-0002-diff into the patch.
>
> I do not like this patch *at all*. It removes the documentation
> that enunciates the general principles ALTER is following, such as
>
> - If a table has any descendant tables, it is not permitted to add,
> - rename, or change the type of a column in the parent table without doing
> - the same to the descendants. This ensures that the descendants always
> - have columns matching the parent. Similarly, a <literal>CHECK</literal>
>
> and replaces that with highly repetitive, explanation-free
> statements like "Specifying <literal>ONLY</literal> is not allowed"
> for each option. I don't see how this set of changes is an improvement.
> It's also close to unreviewable, since it's so hard to see whether
> those statements have been attached to all and only the proper
> options.
>
> If there's mistakes in the existing text, by all means let's fix
> them. But this doesn't seem like the way to go about it.
>
> By the by, I believe that our general project style is to leave a
> blank line between <para> units. So I also don't approve of the
> patch making a concerted effort to remove a lot of those blank
> lines, especially when it's doing that to just one file.
>
> regards, tom lane
Hi Tom,
Thanks for your comments.
The main goal of this patch is to clarify how ALTER TABLE sub-commands behave on partitioned tables, since the current behavior is not always consistent or easy to predict. Because of that, some per-subcommand verification is probably unavoidable. But I agree the current patch may make that harder than it should be.
I also was not aware of the blank-line style convention. In any case, that part is easy to fix.
I’ll rework the patch to better address your concerns.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2026-03-30 03:06 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-21 01:09 Re: docs: clarify ALTER TABLE behavior on partitioned tables David G. Johnston <[email protected]>
2026-01-21 02:53 ` Chao Li <[email protected]>
2026-01-21 03:08 ` David G. Johnston <[email protected]>
2026-01-21 05:29 ` Chao Li <[email protected]>
2026-01-23 10:07 ` Zsolt Parragi <[email protected]>
2026-01-24 00:57 ` David G. Johnston <[email protected]>
2026-01-24 01:16 ` David G. Johnston <[email protected]>
2026-01-26 04:16 ` Chao Li <[email protected]>
2026-01-26 04:15 ` Chao Li <[email protected]>
2026-01-26 04:36 ` David G. Johnston <[email protected]>
2026-01-26 05:13 ` Chao Li <[email protected]>
2026-01-26 05:38 ` David G. Johnston <[email protected]>
2026-01-26 07:20 ` Chao Li <[email protected]>
2026-01-26 14:47 ` Zsolt Parragi <[email protected]>
2026-01-26 22:41 ` Chao Li <[email protected]>
2026-01-28 22:21 ` David G. Johnston <[email protected]>
2026-01-29 01:43 ` Chao Li <[email protected]>
2026-01-29 01:59 ` David G. Johnston <[email protected]>
2026-03-30 03:06 ` Chao Li <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox