On Mon, Mar 2, 2026 at 9:18 PM shveta malik <[email protected]> wrote: >
After a couple of weeks away, I have been trying to catch up on what became of this feature. I was surprised by the late syntax change to make the TABLE keyword now mandatory in EXCEPT TABLE. My initial impression is that it forces the user to write an unnecessarily verbose command like: CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (tab1,tab2); vs CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (tab1,tab2); And the examples to justify making this mandatory [1] are not quite correct, AFAICT. Let's look at those in more detail: > > Currently, the syntax we plan to support in patch001 is: > > CREATE PUBLICATION pub for ALL TABLES EXCEPT TABLE (tab1, tab2, ...) > > That is: > > a) The TABLE keyword is mandatory after EXCEPT. > b) Parentheses around the table list are mandatory. > > There has been some confusion regarding the EXCEPT syntax. Below are > the points that help justify the above syntax. > > 1) TABLE keyword: > > Consider the command: > CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (tab1, tab2); > > At first glance, this appears acceptable even without the TABLE > keyword after EXCEPT. However, in the future, if we support excluding > a particular schema, for example: > > CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT SCHEMA s1, s2; > > then it becomes necessary to explicitly specify whether we are > excluding a TABLE, SCHEMA, or SEQUENCE after EXCEPT. SEQUENCE? -- Hm, this example is "FOR ALL TABLES" -- emphasis on *tables* -- sequences have nothing to do with "FOR ALL TABLES": they would be handled by a "FOR ALL SEQUENCES" command. TABLE v SCHEMA? -- I agree that if there will be some future way for excluding all tables from certain schemas, then you will need to be able to distinguish excluded schemas from excluded tables. However, the command in this example is "FOR ALL TABLES" -- emphasis on *tables* -- so IMO omission of TABLE keyword can still be understood to mean the default behaviour because the type is very clear. e.g. all these are unambiguous to me CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (tab1,tab2); CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (tab1,tab2); CREATE PUBLICATION pub FOR ALL TABLES EXCEPT SCHEMA (sch1,sch2); and combinations too: CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (tab1,tab2) SCHEMA (sch1,sch2); CREATE PUBLICATION pub FOR ALL TABLES EXCEPT TABLE (tab1,tab2) SCHEMA (sch1 sch2); > > Similarly, if we extend CREATE PUBLICATION to support something like: > CREATE PUBLICATION pub > FOR TABLES, SEQUENCES IN SCHEMA sch1 > EXCEPT TABLE (t1), EXCEPT SEQUENCE (s1); > > then also, the TABLE or SEQUENCE keyword after EXCEPT becomes > mandatory for clarity. > That example seemed bogus to me. If in future we allow to publish sequences by schema, then IMO that future syntax would be like: CREATE PUBLICATION pub1 FOR SEQUENCES IN SCHEMA sch1; Then, exclusions would look like: CREATE PUBLICATION pub1 FOR SEQUENCES IN SCHEMA sch1 EXCEPT SEQUENCE (seq1,seq2); But here the SEQUENCE keyword is also optional because the type (sequences) is already clear from the command! So above is the same as: CREATE PUBLICATION pub1 FOR SEQUENCES IN SCHEMA sch1 EXCEPT (seq1,seq2); Therefore, the equivalent of your example would be: CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1 EXCEPT TABLE (tab1), FOR SEQUENCES IN SCHEMA sch1 EXCEPT SEQUENCE (seq1); or (omitting optional keywords) CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1 EXCEPT (tab1), FOR SEQUENCES IN SCHEMA sch1 EXCEPT (seq1); ~~~ Summary. * Must the TABLE (or other type) keyword be mandatory to clarify what the excluded type is? No, I don't think so; the default type the command refers to is unambiguous already. * Does making the TABLE (or other type) mandatory make it easier for the end-user? I'm not so sure. Saying just EXCEPT (tab1) instead of EXCEPT TABLE (tab1,tab2) seemed more natural to me. * Was it just easier to implement when mandatory? Probably true. * Isn't this analogous to "ALTER TABLE ADD/DROP/ALTER [COLUMN]" commands [2]? PostgreSQL doesn't force the user to type the unnecessary COLUMN keyword. IMO, none of the examples/reasons given justified why the TABLE needed to be made mandatory. Certainly, the CREATE PUBLICATION command is already long enough without enforcing redundant keywords. ====== [1] https://www.postgresql.org/message-id/CAJpy0uB%3DJxTYXOB7VmrhVLR%2B1PG0%3DTtHuGekaqibOPpo2UBLiQ%40mail.gmail.com [2] https://www.postgresql.org/docs/current/sql-altertable.html Kind Regards, Peter Smith. Fujitsu Australia
