Re: Support EXCEPT for TABLES IN SCHEMA publications - Mailing list pgsql-hackers

On Tue, Apr 14, 2026 at 3:22 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Tue, Apr 14, 2026 at 3:15 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> >
> > On Tue, Apr 14, 2026 at 1:03 PM Peter Smith <smithpb2250@gmail.com> wrote:
> > >
> > > On Tue, Apr 14, 2026 at 4:30 PM Nisha Moond <nisha.moond412@gmail.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.
> > >
> >
> > I intentionally didn’t use the TABLE keyword inside EXCEPT.
> > IIUC, for FOR ALL TABLES EXCEPT, there may be a future need to
> > distinguish object types (e.g., tables vs schemas), which is why
> > specifying TABLE makes sense there.
> >
> > However, for TABLES IN SCHEMA, only tables can be specified, so
> > omitting TABLE keeps the syntax simpler and more intuitive.
> >
> > Let's hear others’ opinions too on this and I'll adjust if there’s a
> > preference for including it.
> >
>
> Nisha, please see pt 1 in [1]. If we plan to support that, then we
> need TABLE keyword, otherwise we are fine.
>
> [1]: https://www.postgresql.org/message-id/CAJpy0uB%3DJxTYXOB7VmrhVLR%2B1PG0%3DTtHuGekaqibOPpo2UBLiQ%40mail.gmail.com
>

Thank you Shveta for pointing to the discussion.
So, if we were to extend CREATE PUBLICATION to support something like:
 "CREATE PUBLICATION pub FOR TABLES, SEQUENCES IN SCHEMA sch1 EXCEPT
(TABLE t1,  SEQUENCE s1);"
then, introducing TABLE inside EXCEPT could make sense, but at this
stage we’re not sure how the syntax for supporting SEQUENCES IN SCHEMA
will evolve. The overall grammar may require broader changes rather
than a simple extension. So, we can always introduce keywords like
TABLE or SEQUENCE later, if and when they are actually needed. Just my
thoughts.

--
Thanks,
Nisha