On Tue, Mar 10, 2026 at 8:24 PM shveta malik <[email protected]> wrote:
>
> On Wed, Mar 11, 2026 at 2:25 AM Masahiko Sawada <[email protected]> wrote:
> >
> > On Wed, Mar 4, 2026 at 3:01 AM Amit Kapila <[email protected]> wrote:
> > >
> > > On Wed, Mar 4, 2026 at 2:49 PM shveta malik <[email protected]>
> > > wrote:
> > > >
> > > > On Wed, Mar 4, 2026 at 1:54 PM vignesh C <[email protected]> wrote:
> > > > >
> > > > > Here is an updated version with a couple of typos fixed and separated
> > > > > the describe table query based on versions which make it more easier
> > > > > to read.
> > > > >
> > > >
> > > > v56 LGTM.
> > > >
> > >
> > > Pushed.
> >
> > Sorry for joining the discussion late.
> >
> > I've tried with the new EXCEPT clause and I have some questions about
> > the newly introduced syntax. With this feature, we can now write:
> >
> > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (a, b, c);
> >
> > The exclusion table list is written in the form of TABLE (a, b, c) but
> > it's quite different from the inclusion table list we can specify
> > (e.g., TABLE a, TABLE b, TABLE c). I'm concerned that it could confuse
> > users. Have these points already been discussed? Also, isn't the TABLE
> > after EXCEPT redundant?
> >
>
> Thanks for the question.
>
> Yes, these concerns were discussed earlier when the EXCEPT syntax was
> introduced. The current syntax:
>
> CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (a, b, c);
>
> was chosen intentionally, mainly for extensibility and to avoid
> ambiguity in future enhancements.
>
> 1. TABLE keyword after EXCEPT
>
> At first glance, the TABLE keyword might appear redundant. However, it
> becomes important if we later extend the syntax to allow excluding
> other object types, such as schemas or sequences. For example:
>
> CREATE PUBLICATION pub FOR ALL TABLES
> EXCEPT SCHEMA (s1, s2), EXCEPT TABLE (t1, t2);
>
> or potentially more complex combinations such as:
>
> CREATE PUBLICATION pub
> FOR TABLES, SEQUENCES IN SCHEMA sch1
> EXCEPT TABLE (t1), EXCEPT SEQUENCE (s1);
>
> In such cases, explicitly specifying TABLE (or SCHEMA, SEQUENCE, etc.)
> after EXCEPT avoids ambiguity and keeps the syntax consistent.
I understand that it's for future extension but I'm still unsure that
users would not be confused by the fact that the syntaxes of inclusion
list and exclusion list are different; TABLES IN SCHEMA s1 vs. SCHEMA
(s1).
>
> 2. Parentheses around the exclusion list
>
> Parentheses are required to clearly separate the excluded objects from
> other elements in the publication definition, especially when mixed
> with inclusion clauses. For example, consider a future case like:
>
> CREATE PUBLICATION pub
> FOR TABLES IN SCHEMA s1
> EXCEPT TABLE (t1, t2), TABLE t3;
>
> Here it is clear that t1 and t2 are excluded, while t3 is explicitly
> included. Without parentheses, a statement like:
>
> CREATE PUBLICATION pub
> FOR TABLES IN SCHEMA s1
> EXCEPT TABLE t1, t2, TABLE t3;
>
> would be harder to interpret and potentially ambiguous.
>
> So although the syntax differs slightly from the existing inclusion
> list (e.g., TABLE a, b), requiring both the TABLE keyword and
> parentheses after EXCEPT helps keep the grammar unambiguous and makes
> the syntax easier to extend in the future.
I'm still unsure that the syntax like TABLE (t1, t2) for the exclusion
list is syntactically correct. The syntax of TABLE (...) is already
used in a quite different way as follows (borrowed an example from
stats_import.sql):
CREATE FUNCTION stats_import.pg_statistic_get_difference(a text, b text)
RETURNS TABLE (relname text, stats stats_import.pg_statistic_flat_t)
BEGIN ATOMIC
WITH aset AS (SELECT * FROM stats_import.pg_statistic_flat(a)),
bset AS (SELECT * FROM stats_import.pg_statistic_flat(b))
SELECT a AS relname, a_minus_b::stats_import.pg_statistic_flat_t
FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b
UNION ALL
SELECT b AS relname, b_minus_a::stats_import.pg_statistic_flat_t
FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
END;
Wouldn't it be more appropriate to use a plural form or the IN
keyword, such as EXCEPT TABLES (t1, t2) or EXCEPT TABLES IN (t1, t2)?
Or if we might want to add multiple items in the EXCEPT clause in the
future we can have parentheses around all exclusion items as follow:
CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, TABLE t2,
TABLES IN SCHEMA s1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
t2), TABLE t3;
> Original discussion is at [1].
Thanks. I'll read the discussion.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
