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

On Wed, Apr 15, 2026 at 8:15 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Wed, Apr 15, 2026 at 2:22 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> >
> > 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.
> >
>
> 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.
>

I agree with Shveta that inconsistency will confuse.

We already have FOR ALL TABLES EXCEPT (TABLE ...) [1]
And another place is currently introducing FOR ALL SEQUENCES EXCEPT
(SEQUENCE ...) [2]
IMO, this schema thread should match [1].

I think syntax *consistency* should take priority here. While some
keywords may end up being unnecessary in certain cases, having
different rules for each EXCEPT variation seems harder to maintain and
reason about.

I also agree with you that we are not sure how the command syntax
might evolve in future. That is another reason why requiring these
keywords (like 'TABLE' and 'SEQUENCE') is the best/safest first
approach, IMO, because it will be backward-compatible. For example, if
it turns out we can make (some of) these keywords optional in future,
then user code will still be fine, but if they are absent now, then we
cannot make them mandatory in future PostgreSQL versions without
breaking user code.

======
[1] https://www.postgresql.org/docs/devel/sql-createpublication.html
[2] https://www.postgresql.org/message-id/CANhcyEVSXyQkvmrsOWPdQqnm2J3GMyQQrKhyCJiBQzqs6AvSow%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia