On Fri, Jan 16, 2026 at 4:59 PM Amit Kapila <[email protected]> wrote:
>
> On Tue, Jan 6, 2026 at 6:13 PM Shlok Kyal <[email protected]> wrote:
> >
> > Also addressed the remaining comments. I have also addressed the
> > comments by Peter in [1]. I have also done some minor cosmetic
> > changes.
> >
>
> CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (t1,t2);
>
> Did we consider using EXCLUDE instead of EXCEPT? In another similar
> feature being discussed, the community is proposing to use EXCLUDE to
> SQL Standard, so won't it be better to use EXCLUDE here as well?
>
> [1] -
> https://www.postgresql.org/message-id/63e1587b-4258-41de-b823-948f8cc692d9%40eisentraut.org
>
I am listing all current usages of EXCEPT and EXCLUDE to decide us better:
EXCEPT:
1. Set Operator (EXCEPT / EXCEPT ALL) (docs at [1])
Syntax: query1 EXCEPT [ALL] query2.
EXCEPT returns all rows that are in the result of query1 but not in
the result of query2.
Example: SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors;
2. IMPORT FOREIGN SCHEMA … EXCEPT (docs at [2])
Syntax:
IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_list ) ]
FROM SERVER server_name INTO local_schema
[ OPTIONS (...) ];
LIMIT TO (table_list) → import only the listed tables.
EXCEPT (table_list) → import all tables except the listed ones.
Example:
IMPORT FOREIGN SCHEMA public EXCEPT (audit_log)
FROM SERVER remote_pg INTO remote_import;
~~
EXCLUDE:
1. Exclusion Constraints (docs at [3])
Syntax:
CREATE TABLE table_name
( column_name data_type,
EXCLUDE USING index_method ( column_name WITH operator [, ...] ) );
Prevents rows from violating a condition defined by operators.
Example:
CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );
Or
CREATE TABLE foo (x int, EXCLUDE (x WITH =)); --This is essentially
like a UNIQUE constraint but defined via EXCLUDE.
2.
In Window Function Calls (docs at [4])
We use EXCLUDE in the window frame exclusion clause.
In SQL, when we use OVER (...) with ROWS or RANGE, we can also specify
EXCLUDE to control which rows are considered in the window frame.
Options explained:
EXCLUDE CURRENT ROW → exclude the current row from the frame.
EXCLUDE GROUP → excludes the current row and its ordering peers from the frame.
EXCLUDE TIES → excludes any peers of the current row from the frame,
but not the current row itself
EXCLUDE NO OTHERS → include everything (default).
Example:
SELECT id, value, SUM(value)
OVER ( ORDER BY id ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
EXCLUDE CURRENT ROW )
AS running_sum_excluding_current FROM test;
~~
IMO, we use EXCEPT in postgres when we want to filter a set of objects
from all selected objects and we use EXCLUDE mostly in terms of rows
based constraints/exclusion.
And our example of :
Create publication pub1 for all tables EXCEPT/EXCLUDE tab1,tab2;
has more resemblance to:
IMPORT FOREIGN SCHEMA public EXCEPT (audit_log) FROM SERVER remote_pg
INTO remote_import;
So based on above, +1 for EXCEPT for our case.
~~
[1]: https://www.postgresql.org/docs/current/queries-union.html
[2]: https://www.postgresql.org/docs/current/sql-importforeignschema.html
[3]:
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION
[4]:
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
thanks
Shveta
