Re: Support EXCEPT for TABLES IN SCHEMA publications

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.

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.

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.