Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Apr 14, 2026 at 1:03 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Tue, Apr 14, 2026 at 4:30 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> >
> > Hi hackers,
> >
> > Following earlier work to support EXCEPT for FOR ALL TABLES [1]
> > publications, starting this thread to extend the same capability to
> > schema-level publications (TABLES IN SCHEMA).
>
> Hi Nisha.
>
> +1 for adding this new kind of exclusion clause to CREATE PUBLICATION command.
>
> >
> > Currently, TABLES IN SCHEMA publishes all tables in a schema with no
> > way to exclude a subset. Users who want to skip a few tables must
> > switch to an explicit FOR TABLE list, which loses the convenience of
> > schema-level publishing and requires ongoing maintenance as tables are
> > added.
> >
> > Proposed syntax:
> > ------------------------
> > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);
> > ALTER PUBLICATION pub ADD TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> > ALTER PUBLICATION pub SET TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> >
> > Note: Tables in the EXCEPT clause must be schema-qualified to avoid
> > ambiguity and must belong to the published schema; otherwise, an error
> > is raised.
> >
>
> The proposed syntax is almost, but not quite, what I was anticipating.
> IMO the syntax shouldn't just be similar to the FOR ALL TABLES EXCEPT;
> It can be *identical* to it. e.g., your examples are missing the
> 'TABLE' keyword necessary to achieve the same command flexibility.
> Furthermore, what is the ambiguity referred to? An excluded table is
> clearly associated with the preceding schema. Can't the code infer the
> schema internally even when it is not provided by the user? Of course,
> the user *can* specify a schema-qualified name if they want to, but I
> didn't see why we are forcing that rule upon them.

+1. I also feel specifying only the table name is clear enough. Or are
we referring to implementation complexity here?

> e.g.
>
> -- Syntax can be *identical* to the "EXCEPT (TABLE ...)" clause already pushed.
> -- Both of these below are equivalent.
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2, t3);
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
> t2, TABLE t3);
>
> -- Below is an example of multiple schemas and multiple except clauses:
> -- publish all tables of schema s1 except s1.t1 and s1.t2
> -- publish all tables of schema s2
> -- publish all tables of schema s3 except table s3.t2 (how is this
> ambiguous with the excluded s1.t2?)
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2),
> s2, s3 EXCEPT (TABLE t2);
>
> ======
> Kind Regards,
> Peter Smith.
> Fujitsu Australia.
>
>