Skip to content

Commit 3c64342

Browse files
tglsfdctglsfdc
committed
Provide adequate documentation of the "table_name *" notation.
Somewhere along the line, somebody decided to remove all trace of this notation from the documentation text. It was still in the command syntax synopses, or at least some of them, but with no indication what it meant. This will not do, as evidenced by the confusion apparent in bug #7543; even if the notation is now unnecessary, people will find it in legacy SQL code and need to know what it does.
1 parent b2e3bea commit 3c64342

File tree

9 files changed

+104
-73
lines changed

9 files changed

+104
-73
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5761,11 +5761,23 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
57615761
<indexterm><primary>inheritance</></>
57625762
<listitem>
57635763
<para>
5764-
This controls the inheritance semantics. If turned <literal>off</>,
5765-
subtables are not accessed by various commands by default; basically
5766-
an implied <literal>ONLY</literal> key word. This was added for
5767-
compatibility with releases prior to 7.1. See
5768-
<xref linkend="ddl-inherit"> for more information.
5764+
This setting controls whether undecorated table references are
5765+
considered to include inheritance child tables. The default is
5766+
<literal>on</>, which means child tables are included (thus,
5767+
a <literal>*</> suffix is assumed by default). If turned
5768+
<literal>off</>, child tables are not included (thus, an
5769+
<literal>ONLY</literal> prefix is assumed). The SQL standard
5770+
requires child tables to be included, so the <literal>off</> setting
5771+
is not spec-compliant, but it is provided for compatibility with
5772+
<productname>PostgreSQL</> releases prior to 7.1.
5773+
See <xref linkend="ddl-inherit"> for more information.
5774+
</para>
5775+
5776+
<para>
5777+
Turning <varname>sql_inheritance</> off is deprecated, because that
5778+
behavior has been found to be error-prone as well as contrary to SQL
5779+
standard. Discussions of inheritance behavior elsewhere in this
5780+
manual generally assume that it is <literal>on</>.
57695781
</para>
57705782
</listitem>
57715783
</varlistentry>

‎doc/src/sgml/ddl.sgml

Lines changed: 21 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2073,6 +2073,23 @@ SELECT name, altitude
20732073
<literal>ONLY</literal> keyword.
20742074
</para>
20752075

2076+
<para>
2077+
You can also write the table name with a trailing <literal>*</>
2078+
to explicitly specify that descendant tables are included:
2079+
2080+
<programlisting>
2081+
SELECT name, altitude
2082+
FROM cities*
2083+
WHERE altitude &gt; 500;
2084+
</programlisting>
2085+
2086+
Writing <literal>*</> is not necessary, since this behavior is
2087+
the default (unless you have changed the setting of the
2088+
<xref linkend="guc-sql-inheritance"> configuration option).
2089+
However writing <literal>*</> might be useful to emphasize that
2090+
additional tables will be searched.
2091+
</para>
2092+
20762093
<para>
20772094
In some cases you might wish to know which table a particular row
20782095
originated from. There is a system column called
@@ -2220,15 +2237,15 @@ VALUES ('New York', NULL, NULL, 'NY');
22202237
data modification, or schema modification
22212238
(e.g., <literal>SELECT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
22222239
most variants of <literal>ALTER TABLE</literal>, but
2223-
not <literal>INSERT</literal> and <literal>ALTER TABLE ...
2240+
not <literal>INSERT</literal> or <literal>ALTER TABLE ...
22242241
RENAME</literal>) typically default to including child tables and
22252242
support the <literal>ONLY</literal> notation to exclude them.
22262243
Commands that do database maintenance and tuning
22272244
(e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
2228-
typically only work on individual, physical tables and do no
2245+
typically only work on individual, physical tables and do not
22292246
support recursing over inheritance hierarchies. The respective
2230-
behavior of each individual command is documented in the reference
2231-
part (<xref linkend="sql-commands">).
2247+
behavior of each individual command is documented in its reference
2248+
page (<xref linkend="sql-commands">).
22322249
</para>
22332250

22342251
<para>
@@ -2278,18 +2295,6 @@ VALUES ('New York', NULL, NULL, 'NY');
22782295
inheritance is useful for your application.
22792296
</para>
22802297

2281-
<note>
2282-
<title>Deprecated</title>
2283-
<para>
2284-
In releases of <productname>PostgreSQL</productname> prior to 7.1, the
2285-
default behavior was not to include child tables in queries. This was
2286-
found to be error prone and also in violation of the SQL
2287-
standard. You can get the pre-7.1 behavior by turning off the
2288-
<xref linkend="guc-sql-inheritance"> configuration
2289-
option.
2290-
</para>
2291-
</note>
2292-
22932298
</sect2>
22942299
</sect1>
22952300

‎doc/src/sgml/queries.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,16 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
140140
&mdash; any columns added in subtables are ignored.
141141
</para>
142142

143+
<para>
144+
Instead of writing <literal>ONLY</> before the table name, you can write
145+
<literal>*</> after the table name to explicitly specify that descendant
146+
tables are included. Writing <literal>*</> is not necessary since that
147+
behavior is the default (unless you have changed the setting of the <xref
148+
linkend="guc-sql-inheritance"> configuration option). However writing
149+
<literal>*</> might be useful to emphasize that additional tables will be
150+
searched.
151+
</para>
152+
143153
<sect3 id="queries-join">
144154
<title>Joined Tables</title>
145155

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -625,10 +625,12 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
625625
<term><replaceable class="PARAMETER">name</replaceable></term>
626626
<listitem>
627627
<para>
628-
The name (possibly schema-qualified) of an existing table to
629-
alter. If <literal>ONLY</> is specified, only that table is
630-
altered. If <literal>ONLY</> is not specified, the table and any
631-
descendant tables are altered.
628+
The name (optionally schema-qualified) of an existing table to
629+
alter. If <literal>ONLY</> is specified before the table name, only
630+
that table is altered. If <literal>ONLY</> is not specified, the table
631+
and all its descendant tables (if any) are altered. Optionally,
632+
<literal>*</> can be specified after the table name to explicitly
633+
indicate that descendant tables are included.
632634
</para>
633635
</listitem>
634636
</varlistentry>
@@ -1026,7 +1028,7 @@ ALTER TABLE distributors DROP CONSTRAINT zipchk;
10261028
</para>
10271029

10281030
<para>
1029-
To remove a check constraint from a table only:
1031+
To remove a check constraint from one table only:
10301032
<programlisting>
10311033
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
10321034
</programlisting>

‎doc/src/sgml/ref/delete.sgml

Lines changed: 8 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
25-
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
25+
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2626
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
2727
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
2828
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -47,13 +47,6 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [
4747
</para>
4848
</tip>
4949

50-
<para>
51-
By default, <command>DELETE</command> will delete rows in the
52-
specified table and all its child tables. If you wish to delete only
53-
from the specific table mentioned, you must use the
54-
<literal>ONLY</literal> clause.
55-
</para>
56-
5750
<para>
5851
There are two ways to delete rows in a table using information
5952
contained in other tables in the database: using sub-selects, or
@@ -96,21 +89,17 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [
9689
</listitem>
9790
</varlistentry>
9891

99-
<varlistentry>
100-
<term><literal>ONLY</></term>
101-
<listitem>
102-
<para>
103-
If specified, delete rows from the named table only. When not
104-
specified, any tables inheriting from the named table are also processed.
105-
</para>
106-
</listitem>
107-
</varlistentry>
108-
10992
<varlistentry>
11093
<term><replaceable class="parameter">table_name</replaceable></term>
11194
<listitem>
11295
<para>
113-
The name (optionally schema-qualified) of an existing table.
96+
The name (optionally schema-qualified) of the table to delete rows
97+
from. If <literal>ONLY</> is specified before the table name,
98+
matching rows are deleted from the named table only. If
99+
<literal>ONLY</> is not specified, matching rows are also deleted
100+
from any tables inheriting from the named table. Optionally,
101+
<literal>*</> can be specified after the table name to explicitly
102+
indicate that descendant tables are included.
114103
</para>
115104
</listitem>
116105
</varlistentry>

‎doc/src/sgml/ref/lock.sgml

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
24+
LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
2525

2626
<phrase>where <replaceable class="PARAMETER">lockmode</replaceable> is one of:</phrase>
2727

@@ -111,9 +111,11 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...
111111
<listitem>
112112
<para>
113113
The name (optionally schema-qualified) of an existing table to
114-
lock. If <literal>ONLY</> is specified, only that table is
115-
locked. If <literal>ONLY</> is not specified, the table and all
116-
its descendant tables (if any) are locked.
114+
lock. If <literal>ONLY</> is specified before the table name, only that
115+
table is locked. If <literal>ONLY</> is not specified, the table and all
116+
its descendant tables (if any) are locked. Optionally, <literal>*</>
117+
can be specified after the table name to explicitly indicate that
118+
descendant tables are included.
117119
</para>
118120

119121
<para>

‎doc/src/sgml/ref/select.sgml

Lines changed: 22 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -298,10 +298,12 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
298298
<term><replaceable class="parameter">table_name</replaceable></term>
299299
<listitem>
300300
<para>
301-
The name (optionally schema-qualified) of an existing table or
302-
view. If <literal>ONLY</> is specified, only that table is
303-
scanned. If <literal>ONLY</> is not specified, the table and
304-
any descendant tables are scanned.
301+
The name (optionally schema-qualified) of an existing table or view.
302+
If <literal>ONLY</> is specified before the table name, only that
303+
table is scanned. If <literal>ONLY</> is not specified, the table
304+
and all its descendant tables (if any) are scanned. Optionally,
305+
<literal>*</> can be specified after the table name to explicitly
306+
indicate that descendant tables are included.
305307
</para>
306308
</listitem>
307309
</varlistentry>
@@ -1661,15 +1663,24 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
16611663
</refsect2>
16621664

16631665
<refsect2>
1664-
<title><literal>ONLY</literal> and Parentheses</title>
1666+
<title><literal>ONLY</literal> and Inheritance</title>
16651667

16661668
<para>
1667-
The SQL standard requires parentheses around the table name
1668-
after <literal>ONLY</literal>, as in <literal>SELECT * FROM ONLY
1669-
(tab1), ONLY (tab2) WHERE ...</literal>. PostgreSQL supports that
1670-
as well, but the parentheses are optional. (This point applies
1671-
equally to all SQL commands supporting the <literal>ONLY</literal>
1672-
option.)
1669+
The SQL standard requires parentheses around the table name when
1670+
writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY
1671+
(tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</>
1672+
considers these parentheses to be optional.
1673+
</para>
1674+
1675+
<para>
1676+
<productname>PostgreSQL</> allows a trailing <literal>*</> to be written to
1677+
explicitly specify the non-<literal>ONLY</literal> behavior of including
1678+
child tables. The standard does not allow this.
1679+
</para>
1680+
1681+
<para>
1682+
(These points apply equally to all SQL commands supporting the
1683+
<literal>ONLY</literal> option.)
16731684
</para>
16741685
</refsect2>
16751686

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
24+
TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ... ]
2525
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
2626
</synopsis>
2727
</refsynopsisdiv>
@@ -47,10 +47,12 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
4747
<term><replaceable class="PARAMETER">name</replaceable></term>
4848
<listitem>
4949
<para>
50-
The name (optionally schema-qualified) of a table to be
51-
truncated. If <literal>ONLY</> is specified, only that table is
52-
truncated. If <literal>ONLY</> is not specified, the table and
53-
all its descendant tables (if any) are truncated.
50+
The name (optionally schema-qualified) of a table to truncate.
51+
If <literal>ONLY</> is specified before the table name, only that table
52+
is truncated. If <literal>ONLY</> is not specified, the table and all
53+
its descendant tables (if any) are truncated. Optionally, <literal>*</>
54+
can be specified after the table name to explicitly indicate that
55+
descendant tables are included.
5456
</para>
5557
</listitem>
5658
</varlistentry>

‎doc/src/sgml/ref/update.sgml

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
25-
UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
25+
UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2626
SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
2727
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
2828
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
@@ -41,13 +41,6 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [ AS ]
4141
columns not explicitly modified retain their previous values.
4242
</para>
4343

44-
<para>
45-
By default, <command>UPDATE</command> will update rows in the
46-
specified table and all its subtables. If you wish to only update
47-
the specific table mentioned, you must use the <literal>ONLY</>
48-
clause.
49-
</para>
50-
5144
<para>
5245
There are two ways to modify a table using information contained in
5346
other tables in the database: using sub-selects, or specifying
@@ -97,6 +90,11 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ [ AS ]
9790
<listitem>
9891
<para>
9992
The name (optionally schema-qualified) of the table to update.
93+
If <literal>ONLY</> is specified before the table name, matching rows
94+
are updated in the named table only. If <literal>ONLY</> is not
95+
specified, matching rows are also updated in any tables inheriting from
96+
the named table. Optionally, <literal>*</> can be specified after the
97+
table name to explicitly indicate that descendant tables are included.
10098
</para>
10199
</listitem>
102100
</varlistentry>

0 commit comments

Comments
 (0)