Re: Support EXCEPT for TABLES IN SCHEMA publications

On Wed, Apr 15, 2026 at 2:22 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> On Tue, Apr 14, 2026 at 3:22 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Tue, Apr 14, 2026 at 3:15 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> > >
> > > 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.
> > > >
> > >
> > > 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.
>

It works for me Nisha. I also agree that the 'TABLE' keyword seems
redundant in the case you proposed:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);

But I am just worried this might confuse users, as one command
requires specifying the TABLE with EXCEPT, while another requires
omitting it. But we can first focus on implementation and come back to
it later when more people join and share their thoughts.

thanks
Shveta