Hi,
I would like feedback on the proposed feature: SELECT * EXCLUDE.
The idea is to allow excluding a small number of columns from * without
having to list all remaining columns explicitly. This is mainly intended for
wide tables, columns with metadata, long text fields, or other columns that
may not be needed, making SELECT * more practical while omitting unnecessary data.
Similar functionality exists in other databases such as DuckDB, Snowflake,
and BigQuery. I haven't tried BigQuery and Snowflake seems to only support
unqualified column names as mentioned in its documentation [1]. My implementation
is more like DuckDB, which supports both qualified or unqualified column names.
Qualified stars are supported, and excluded column names may be qualified or
unqualified. If an excluded name matches multiple columns from the * expansion,
all matching columns are excluded. If no column matches, an error is raised.
EXCLUDE is only allowed with *. Using it on non-star expressions results in error.
I have attached an initial draft patch that includes code changes, documentation
updates, and a fairly good amount of test cases. The tests are meant to clearly
describe the intended behavior and scope, including qualified and unqualified
column names, table aliases, joins, multiple tables, and expected error cases.
The patch also allows EXCLUDE to be used with SELECT INTO,
INSERT INTO ... SELECT ..., and RETURNING clauses, similar to how DuckDB
supports it, but the details of those cases can be discussed later.
A few examples:
SELECT * EXCLUDE (email, created_at) FROM users;
SELECT * EXCLUDE (does_not_exist) FROM users; -- error
SELECT * EXCLUDE (users.created_at)
FROM users
JOIN orders ON orders.user_id =
users.id;
SELECT * EXCLUDE (users.created_at, orders.amount)
FROM users
JOIN orders ON orders.user_id =
users.id;
SELECT users.* EXCLUDE (email), orders.* EXCLUDE (user_id)
FROM users
JOIN orders ON orders.user_id =
users.id;
Looking forward to the feedback. Thanks!
Regards,
Hunaid Sohail
