On 20th of March 2026, Amit Kapila committed patch:
Add support for EXCEPT TABLE in ALTER PUBLICATION. Following commit fd366065e0, which added EXCEPT TABLE support to CREATE PUBLICATION, this commit extends ALTER PUBLICATION to allow modifying the exclusion list. New Syntax: ALTER PUBLICATION name SET publication_all_object [, ... ] where publication_all_object is one of: ALL TABLES [ EXCEPT TABLE ( except_table_object [, ... ] ) ] ALL SEQUENCES If the EXCEPT clause is provided, the existing exclusion list in pg_publication_rel is replaced with the specified relations. If the EXCEPT clause is omitted, any existing exclusions for the publication are cleared. Similarly, SET ALL SEQUENCES updates Note that because this is a SET command, specifying only one object type (e.g., SET ALL SEQUENCES) will reset the other unspecified flags (e.g., setting puballtables to false). Consistent with CREATE PUBLICATION, only root partitioned tables or standard tables can be specified in the EXCEPT list. Specifying a partition child will result in an error. Author: vignesh C <vignesh21@gmail.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Discussion: https://postgr.es/m/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
I just wrote about subscriptions for all tables, except some, and now we got improvement that I was missing then.
Background for what's what was provided there, so let's just quickly see the new syntax. To do this I need existing publication(s), so:
=$ create publication pub for all tables; CREATE PUBLICATION =$ select * from pg_publication_tables where pubname = 'pub'; pubname │ schemaname │ tablename │ attnames │ rowfilter ─────────┼────────────┼───────────┼──────────────┼─────────── pub │ s1 │ t1 │ {x,y} │ [null] pub │ s1 │ t2 │ {x,y} │ [null] pub │ s2 │ t2 │ {x,y} │ [null] pub │ s2 │ t1 │ {x,y} │ [null] pub │ public │ t5 │ {id,payload} │ [null] (5 rows) =$ create publication p1 for all tables except table (s1.t1, s2.t2); CREATE PUBLICATION =$ select * from pg_publication_tables where pubname = 'p1'; pubname │ schemaname │ tablename │ attnames │ rowfilter ─────────┼────────────┼───────────┼──────────────┼─────────── p1 │ s1 │ t2 │ {x,y} │ [null] p1 │ s2 │ t1 │ {x,y} │ [null] p1 │ public │ t5 │ {id,payload} │ [null] (3 rows)
and now let's exclude public.t5 from both of these:
=$ alter publication pub set ALL TABLES EXCEPT TABLE ( public.t5 ); ALTER PUBLICATION =$ alter publication p1 set ALL TABLES EXCEPT TABLE ( public.t5 ); ALTER PUBLICATION =$ select * from pg_publication_tables where pubname = 'pub'; pubname │ schemaname │ tablename │ attnames │ rowfilter ─────────┼────────────┼───────────┼──────────┼─────────── pub │ s1 │ t1 │ {x,y} │ [null] pub │ s1 │ t2 │ {x,y} │ [null] pub │ s2 │ t2 │ {x,y} │ [null] pub │ s2 │ t1 │ {x,y} │ [null] (4 rows) =$ select * from pg_publication_tables where pubname = 'p1'; pubname │ schemaname │ tablename │ attnames │ rowfilter ─────────┼────────────┼───────────┼──────────┼─────────── p1 │ s1 │ t1 │ {x,y} │ [null] p1 │ s1 │ t2 │ {x,y} │ [null] p1 │ s2 │ t2 │ {x,y} │ [null] p1 │ s2 │ t1 │ {x,y} │ [null] (4 rows)
While it worked as expected for pub, it didn't add public.t5 to exclude list in case of p1 – instead it just removed previous except list. This is fine. I can redo by specifying all that there is to exclude:
=$ alter publication p1 set ALL TABLES EXCEPT TABLE ( s1.t1, s2.t2, public.t5 ); ALTER PUBLICATION =$ select * from pg_publication_tables where pubname = 'p1'; pubname │ schemaname │ tablename │ attnames │ rowfilter ─────────┼────────────┼───────────┼──────────┼─────────── p1 │ s1 │ t2 │ {x,y} │ [null] p1 │ s2 │ t1 │ {x,y} │ [null] (2 rows)
Nice. All works well. Thanks again to everyone involved.