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 [1] https://docs.snowflake.com/en/sql-reference/sql/select
From 09b13785fa4bd79bad819c533b442e9573939b0d Mon Sep 17 00:00:00 2001 From: Hunaid2000 <[email protected]> Date: Thu, 8 Jan 2026 14:51:24 +0500 Subject: [PATCH v1] Implement SELECT * EXCLUDE ... command --- doc/src/sgml/ref/select.sgml | 55 +++- src/backend/nodes/makefuncs.c | 1 + src/backend/parser/analyze.c | 2 +- src/backend/parser/gram.y | 25 +- src/backend/parser/parse_relation.c | 69 +++- src/backend/parser/parse_target.c | 36 ++- src/include/nodes/parsenodes.h | 1 + src/include/nodes/primnodes.h | 3 + src/include/parser/parse_relation.h | 2 +- src/test/regress/expected/select_exclude.out | 312 +++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/select_exclude.sql | 164 ++++++++++ 12 files changed, 656 insertions(+), 16 deletions(-) create mode 100644 src/test/regress/expected/select_exclude.out create mode 100644 src/test/regress/sql/select_exclude.sql diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index ca5dd14d627..4639a4dc186 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -34,7 +34,7 @@ PostgreSQL documentation <synopsis> [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ] - [ { * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] + [ { * [ EXCLUDE ( <replaceable>column_name</replaceable> [, ...] ) ] | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ] [ WHERE <replaceable class="parameter">condition</replaceable> ] [ GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } ] @@ -103,6 +103,14 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] </para> </listitem> + <listitem> + <para> + If the <literal>EXCLUDE</literal> clause is specified, all + columns listed there are excluded from the output when + <literal>*</literal> is used in the <literal>SELECT</literal> list. + </para> + </listitem> + <listitem> <para> All elements in the <literal>FROM</literal> list are computed. @@ -382,6 +390,51 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] </para> </refsect2> + <refsect2 id="sql-exclude" xreflabel="EXCLUDE Clause"> + <title><literal>EXCLUDE</literal> Clause</title> + + <para> + The <literal>EXCLUDE</literal> clause allows you to exclude specific + columns from the output when using <literal>*</literal> in the + <literal>SELECT</literal> list. This is useful when selecting from + tables that have many columns, but only a few of them need to be + omitted from the output. + </para> + + <para> + The column names listed in the <literal>EXCLUDE</literal> clause + may be qualified or unqualified. Qualification uses the table name + or table alias as specified in the <literal>FROM</literal> clause. + </para> + + <para> + If a column name listed in the <literal>EXCLUDE</literal> clause + matches multiple columns produced by the <literal>*</literal> + expansion, all matching columns are excluded from the output. + This can happen when selecting from multiple tables that have + columns with the same name. Column name ambiguity can be resolved + by qualifying the column names with a table name or alias. + </para> + + <para> + The following examples illustrate different ways of excluding + columns from the output, producing the same result: +<programlisting> +-- Exclude specific columns without table qualification +SELECT * EXCLUDE (tableA.col_tableA, tableB.col_tableB) ... + +-- Exclude specific columns with table qualification +SELECT tableA.* EXCLUDE (col_tableA), tableB.* EXCLUDE (col_tableB) ... +</programlisting> + </para> + + <para> + If a column name listed in the <literal>EXCLUDE</literal> clause + does not match any column produced by the <literal>*</literal> + expansion, an error is raised. + </para> + </refsect2> + <refsect2 id="sql-from" xreflabel="FROM Clause"> <title><literal>FROM</literal> Clause</title> diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 2caec621d73..13c0ab7fc50 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -481,6 +481,7 @@ makeRangeVar(char *schemaname, char *relname, int location) r->relpersistence = RELPERSISTENCE_PERMANENT; r->alias = NULL; r->location = location; + r->exclude_exist = false; return r; } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 029ca3b68c3..06f4f35f98a 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1719,7 +1719,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt) * Generate a targetlist as though expanding "*" */ Assert(pstate->p_next_resno == 1); - qry->targetList = expandNSItemAttrs(pstate, nsitem, 0, true, -1); + qry->targetList = expandNSItemAttrs(pstate, nsitem, 0, true, -1, NULL); /* * The grammar allows attaching ORDER BY, LIMIT, and FOR UPDATE to a diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 713ee5c10a2..b9ed84b2c8a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -455,6 +455,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); TriggerTransitions TriggerReferencing vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list pub_all_obj_type_list + opt_exclude %type <retclause> returning_clause %type <node> returning_option @@ -17474,15 +17475,28 @@ target_el: a_expr AS ColLabel $$->val = (Node *) $1; $$->location = @1; } - | a_expr + | a_expr opt_exclude { $$ = makeNode(ResTarget); $$->name = NULL; $$->indirection = NIL; $$->val = (Node *) $1; $$->location = @1; + + if ($2 != NIL && IsA($1, ColumnRef)) + { + ColumnRef *n = (ColumnRef *) $1; + + if (!IsA(llast(n->fields), A_Star)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("EXCLUDE clause can only be used with \"*\""), + parser_errposition(@2))); + + n->exclude_list = $2; + } } - | '*' + | '*' opt_exclude { ColumnRef *n = makeNode(ColumnRef); @@ -17494,9 +17508,16 @@ target_el: a_expr AS ColLabel $$->indirection = NIL; $$->val = (Node *) n; $$->location = @1; + + n->exclude_list = $2; } ; +opt_exclude: + EXCLUDE '(' qualified_name_list ')' { $$ = $3; } + | /* EMPTY */ { $$ = NIL; } + ; + /***************************************************************************** * diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 3ec8d8de011..e12824d22e6 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -3283,10 +3283,14 @@ expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, * pstate->p_next_resno determines the resnos assigned to the TLEs. * The referenced columns are marked as requiring SELECT access, if * caller requests that. + * + * If an EXCLUDE list is provided, columns listed there are not + * included in the output TargetEntry list. */ List * expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, - int sublevels_up, bool require_col_privs, int location) + int sublevels_up, bool require_col_privs, int location, + List *exclude_list) { RangeTblEntry *rte = nsitem->p_rte; RTEPermissionInfo *perminfo = nsitem->p_perminfo; @@ -3296,6 +3300,63 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, *var; List *te_list = NIL; + /* + * With an EXCLUDE list, mark columns that should not be expanded. + * + * For qualified column names, only columns belonging to the specified + * relation are excluded. We determine this by comparing the qualifier + * against the RTE's alias. This ensures that multiple qualified "*" + * expansions work correctly when there are several tables with different + * names in the FROM list. The nsitem->p_rte would not be sufficient here, + * as it may represent a join of several relations with all columns merged + * and alias set to some arbitrary name (e.g. "unnamed_join"). So we check + * each column's actual RTE's alias against the column qualifier. + * + * For unqualified column names in the EXCLUDE list, all columns with the + * matching name are excluded, regardless of which relation they come + * from. + */ + if (exclude_list) + { + int colindex = 0; + + foreach(name, nsitem->p_names->colnames) + { + ParseNamespaceColumn *nscol = nsitem->p_nscolumns + colindex; + RangeTblEntry *c_rte = rt_fetch(nscol->p_varno, pstate->p_rtable); + char *colname = strVal(lfirst(name)); + ListCell *elc; + + foreach(elc, exclude_list) + { + RangeVar *rv = (RangeVar *) lfirst(elc); + char *excl_col = rv->relname; + char *schema = rv->schemaname; + + /* + * Skip columns whose RTE alias doesn't match the exclude + * relation/schema qualifier, if any. + */ + if (schema && strcmp(schema, c_rte->eref->aliasname) != 0) + continue; /* not for this RTE */ + + /* + * Matching columns are marked with p_dontexpand so they are + * skipped during expandNSItemVars, and we record whether each + * EXCLUDE entry matched at least one column so unmatched + * exclusions can be reported as errors later. + */ + if (strcmp(colname, excl_col) == 0) + { + nscol->p_dontexpand = true; + rv->exclude_exist = true; + break; + } + } + colindex++; + } + } + vars = expandNSItemVars(pstate, nsitem, sublevels_up, location, &names); /* @@ -3333,6 +3394,12 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, Assert(name == NULL && var == NULL); /* lists not the same length? */ + /* In case all columns were excluded, throw an error */ + if (exclude_list && list_length(te_list) == 0) + ereport(ERROR, + (errcode(ERRCODE_NO_DATA), + errmsg("SELECT list is empty after excluding all columns"))); + return te_list; } diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index b5a2f915b67..a00d627a508 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -46,12 +46,12 @@ static Node *transformAssignmentSubscripts(ParseState *pstate, int location); static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, bool make_target_entry); -static List *ExpandAllTables(ParseState *pstate, int location); +static List *ExpandAllTables(ParseState *pstate, int location, List *exclude_list); static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, bool make_target_entry, ParseExprKind exprKind); static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location, - bool make_target_entry); + bool make_target_entry, List *exclude_list); static List *ExpandRowReference(ParseState *pstate, Node *expr, bool make_target_entry); static int FigureColnameInternal(Node *node, char **name); @@ -152,6 +152,22 @@ transformTargetList(ParseState *pstate, List *targetlist, ExpandColumnRefStar(pstate, cref, true)); + if (cref->exclude_list) + { + ListCell *elc; + + /* Check that excluded columns actually exist */ + foreach(elc, cref->exclude_list) + { + RangeVar *rv = (RangeVar *) lfirst(elc); + + if (!rv->exclude_exist) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" does not exist", + rv->relname))); + } + } continue; } } @@ -1123,6 +1139,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, bool make_target_entry) { List *fields = cref->fields; + List *exclude_list = cref->exclude_list; int numnames = list_length(fields); if (numnames == 1) @@ -1136,7 +1153,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, * need not handle the make_target_entry==false case here. */ Assert(make_target_entry); - return ExpandAllTables(pstate, cref->location); + return ExpandAllTables(pstate, cref->location, exclude_list); } else { @@ -1276,7 +1293,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, * OK, expand the nsitem into fields. */ return ExpandSingleTable(pstate, nsitem, levels_up, cref->location, - make_target_entry); + make_target_entry, exclude_list); } } @@ -1292,7 +1309,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, * The referenced relations/columns are marked as requiring SELECT access. */ static List * -ExpandAllTables(ParseState *pstate, int location) +ExpandAllTables(ParseState *pstate, int location, List *exclude_list) { List *target = NIL; bool found_table = false; @@ -1315,7 +1332,8 @@ ExpandAllTables(ParseState *pstate, int location) nsitem, 0, true, - location)); + location, + exclude_list)); } /* @@ -1372,12 +1390,12 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, */ static List * ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem, - int sublevels_up, int location, bool make_target_entry) + int sublevels_up, int location, bool make_target_entry, List *exclude_list) { if (make_target_entry) { /* expandNSItemAttrs handles permissions marking */ - return expandNSItemAttrs(pstate, nsitem, sublevels_up, true, location); + return expandNSItemAttrs(pstate, nsitem, sublevels_up, true, location, exclude_list); } else { @@ -1446,7 +1464,7 @@ ExpandRowReference(ParseState *pstate, Node *expr, ParseNamespaceItem *nsitem; nsitem = GetNSItemByRangeTablePosn(pstate, var->varno, var->varlevelsup); - return ExpandSingleTable(pstate, nsitem, var->varlevelsup, var->location, make_target_entry); + return ExpandSingleTable(pstate, nsitem, var->varlevelsup, var->location, make_target_entry, NULL); } /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index aac4bfc70d9..78974de2c60 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -310,6 +310,7 @@ typedef struct ColumnRef NodeTag type; List *fields; /* field names (String nodes) or A_Star */ ParseLoc location; /* token location, or -1 if unknown */ + List *exclude_list; /* column names (RangeVar nodes) to exclude */ } ColumnRef; /* diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 5211cadc258..0827d98ba52 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -93,6 +93,9 @@ typedef struct RangeVar /* token location, or -1 if unknown */ ParseLoc location; + + /* column existence flag for SELECT * EXCLUDE(...) */ + bool exclude_exist; } RangeVar; typedef enum TableFuncType diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index 5379330a9bf..de66b720a82 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -122,7 +122,7 @@ extern List *expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, List **colnames); extern List *expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, bool require_col_privs, - int location); + int location, List *exclude_list); extern int attnameAttNum(Relation rd, const char *attname, bool sysColOK); extern const NameData *attnumAttName(Relation rd, int attid); extern Oid attnumTypeId(Relation rd, int attid); diff --git a/src/test/regress/expected/select_exclude.out b/src/test/regress/expected/select_exclude.out new file mode 100644 index 00000000000..807ccf51c18 --- /dev/null +++ b/src/test/regress/expected/select_exclude.out @@ -0,0 +1,312 @@ +-- +-- SELECT_EXCLUDE +-- +SET client_min_messages TO 'warning'; +DROP TABLE IF EXISTS users; +DROP TABLE IF EXISTS orders; +RESET client_min_messages; +CREATE TABLE users ( + id INT PRIMARY KEY, + email TEXT, + name TEXT, + created_at TIMESTAMP, + updated_at TIMESTAMP +); +CREATE TABLE orders ( + id INT PRIMARY KEY, + user_id INT REFERENCES users(id), + amount NUMERIC(10,2), + status TEXT, + created_at TIMESTAMP +); +-- Insert sample data +INSERT INTO users (id, email, name, created_at, updated_at) VALUES +(1,'[email protected]','Alice','2026-01-01 10:00:00','2026-01-05 09:00:00'), +(2,'[email protected]','Bob','2026-01-02 11:00:00','2026-01-06 10:00:00'), +(3,'[email protected]','Carol','2026-01-03 12:00:00',NULL), +(4,NULL,'Dave','2026-01-04 13:00:00','2026-01-07 11:00:00'); +INSERT INTO orders (id, user_id, amount, status, created_at) VALUES +(101,1,50.00,'paid','2026-02-01 09:00:00'), +(102,1,75.50,'shipped','2026-02-02 10:00:00'), +(103,2,20.00,'cancelled','2026-02-03 11:00:00'), +(104,3,100.00,'paid','2026-02-04 12:00:00'); +-- Basic SELECT with EXCLUDE condition +-- Single column +SELECT * EXCLUDE (updated_at) +FROM users +ORDER BY id; + id | email | name | created_at +----+-------------------+-------+-------------------------- + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 + 2 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 + 3 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 + 4 | | Dave | Sun Jan 04 13:00:00 2026 +(4 rows) + +-- Multiple columns +SELECT * EXCLUDE (email, created_at) +FROM users +ORDER BY id; + id | name | updated_at +----+-------+-------------------------- + 1 | Alice | Mon Jan 05 09:00:00 2026 + 2 | Bob | Tue Jan 06 10:00:00 2026 + 3 | Carol | + 4 | Dave | Wed Jan 07 11:00:00 2026 +(4 rows) + +-- Exclude all but one column +SELECT * EXCLUDE (email, name, created_at, updated_at) +FROM users +ORDER BY id; + id +---- + 1 + 2 + 3 + 4 +(4 rows) + +-- Non-existent column, unqualified star (error case) +SELECT * EXCLUDE (does_not_exist) +FROM users; +ERROR: column "does_not_exist" does not exist +-- Non-existent column, qualified star (error case) +SELECT users.* EXCLUDE (does_not_exist) +FROM users; +ERROR: column "does_not_exist" does not exist +-- Aliasing with EXCLUDE +SELECT * EXCLUDE (u.email) +FROM users AS u +ORDER BY u.id; + id | name | created_at | updated_at +----+-------+--------------------------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 +(4 rows) + +-- Expressions with EXCLUDE +SELECT * EXCLUDE (updated_at), 1 + 1 AS two +FROM users +ORDER BY id; + id | email | name | created_at | two +----+-------------------+-------+--------------------------+----- + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 | 2 + 2 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 | 2 + 3 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 | 2 + 4 | | Dave | Sun Jan 04 13:00:00 2026 | 2 +(4 rows) + +-- JOINs with EXCLUDE +-- Join, unqualified EXCLUDE +SELECT * EXCLUDE (created_at) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | email | name | updated_at | id | user_id | amount | status +----+-------------------+-------+--------------------------+-----+---------+--------+----------- + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | [email protected] | Bob | Tue Jan 06 10:00:00 2026 | 103 | 2 | 20.00 | cancelled + 3 | [email protected] | Carol | | 104 | 3 | 100.00 | paid +(4 rows) + +-- Join, qualified EXCLUDE, one table +SELECT * EXCLUDE (users.created_at) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | email | name | updated_at | id | user_id | amount | status | created_at +----+-------------------+-------+--------------------------+-----+---------+--------+-----------+-------------------------- + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 101 | 1 | 50.00 | paid | Sun Feb 01 09:00:00 2026 + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 102 | 1 | 75.50 | shipped | Mon Feb 02 10:00:00 2026 + 2 | [email protected] | Bob | Tue Jan 06 10:00:00 2026 | 103 | 2 | 20.00 | cancelled | Tue Feb 03 11:00:00 2026 + 3 | [email protected] | Carol | | 104 | 3 | 100.00 | paid | Wed Feb 04 12:00:00 2026 +(4 rows) + +-- Join, qualified EXCLUDE, both tables +SELECT * EXCLUDE (users.created_at, orders.amount) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | email | name | updated_at | id | user_id | status | created_at +----+-------------------+-------+--------------------------+-----+---------+-----------+-------------------------- + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 101 | 1 | paid | Sun Feb 01 09:00:00 2026 + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 102 | 1 | shipped | Mon Feb 02 10:00:00 2026 + 2 | [email protected] | Bob | Tue Jan 06 10:00:00 2026 | 103 | 2 | cancelled | Tue Feb 03 11:00:00 2026 + 3 | [email protected] | Carol | | 104 | 3 | paid | Wed Feb 04 12:00:00 2026 +(4 rows) + +-- Join, aliased tables with EXCLUDE +SELECT * EXCLUDE (u.created_at, o.amount) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + id | email | name | updated_at | id | user_id | status | created_at +----+-------------------+-------+--------------------------+-----+---------+-----------+-------------------------- + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 101 | 1 | paid | Sun Feb 01 09:00:00 2026 + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 | 102 | 1 | shipped | Mon Feb 02 10:00:00 2026 + 2 | [email protected] | Bob | Tue Jan 06 10:00:00 2026 | 103 | 2 | cancelled | Tue Feb 03 11:00:00 2026 + 3 | [email protected] | Carol | | 104 | 3 | paid | Wed Feb 04 12:00:00 2026 +(4 rows) + +-- Qualified stars +SELECT users.* EXCLUDE (email), orders.* EXCLUDE (user_id) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | created_at | updated_at | id | amount | status | created_at +----+-------+--------------------------+--------------------------+-----+--------+-----------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 101 | 50.00 | paid | Sun Feb 01 09:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 102 | 75.50 | shipped | Mon Feb 02 10:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 103 | 20.00 | cancelled | Tue Feb 03 11:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 104 | 100.00 | paid | Wed Feb 04 12:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | | | | +(5 rows) + +-- Name collision +SELECT * EXCLUDE (id) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + email | name | created_at | updated_at | user_id | amount | status | created_at +-------------------+-------+--------------------------+--------------------------+---------+--------+-----------+-------------------------- + [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 1 | 50.00 | paid | Sun Feb 01 09:00:00 2026 + [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 1 | 75.50 | shipped | Mon Feb 02 10:00:00 2026 + [email protected] | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 2 | 20.00 | cancelled | Tue Feb 03 11:00:00 2026 + [email protected] | Carol | Sat Jan 03 12:00:00 2026 | | 3 | 100.00 | paid | Wed Feb 04 12:00:00 2026 +(4 rows) + +-- Subqueries with EXCLUDE +SELECT * EXCLUDE (u.created_at) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + id | email | name | updated_at +----+-------------------+-------+-------------------------- + 1 | [email protected] | Alice | Mon Jan 05 09:00:00 2026 + 2 | [email protected] | Bob | Tue Jan 06 10:00:00 2026 + 3 | [email protected] | Carol | + 4 | | Dave | Wed Jan 07 11:00:00 2026 +(4 rows) + +-- CTEs with EXCLUDE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * EXCLUDE (base_users.updated_at) +FROM base_users +ORDER BY id; + id | email | name | created_at +----+-------------------+-------+-------------------------- + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 + 2 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 + 3 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 + 4 | | Dave | Sun Jan 04 13:00:00 2026 +(4 rows) + +-- WHERE clause with EXCLUDE +SELECT * EXCLUDE (email) +FROM users +WHERE email IS NOT NULL +ORDER BY created_at; + id | name | created_at | updated_at +----+-------+--------------------------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | +(3 rows) + +-- DISTINCT with EXCLUDE +SELECT DISTINCT * EXCLUDE (updated_at) +FROM users; + id | email | name | created_at +----+-------------------+-------+-------------------------- + 2 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 + 3 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 + 4 | | Dave | Sun Jan 04 13:00:00 2026 + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 +(4 rows) + +-- Error cases +-- Empty EXCLUDE list (error case) +SELECT * EXCLUDE () +FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * EXCLUDE () + ^ +-- Exclude all columns (error case) +SELECT * EXCLUDE (id, email, name, created_at, updated_at) +FROM users; +ERROR: SELECT list is empty after excluding all columns +-- Exclude without star (error case) +SELECT id, email EXCLUDE (email) +FROM users; +ERROR: EXCLUDE clause can only be used with "*" +LINE 1: SELECT id, email EXCLUDE (email) + ^ +-- Multiple stars with EXCLUDE +SELECT +*, +users.* EXCLUDE (id), +orders.* EXCLUDE (user_id) +FROM users +LEFT JOIN orders ON orders.user_id = users.id; + id | email | name | created_at | updated_at | id | user_id | amount | status | created_at | email | name | created_at | updated_at | id | amount | status | created_at +----+-------------------+-------+--------------------------+--------------------------+-----+---------+--------+-----------+--------------------------+-------------------+-------+--------------------------+--------------------------+-----+--------+-----------+-------------------------- + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 101 | 1 | 50.00 | paid | Sun Feb 01 09:00:00 2026 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 101 | 50.00 | paid | Sun Feb 01 09:00:00 2026 + 1 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 102 | 1 | 75.50 | shipped | Mon Feb 02 10:00:00 2026 | [email protected] | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 102 | 75.50 | shipped | Mon Feb 02 10:00:00 2026 + 2 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 103 | 2 | 20.00 | cancelled | Tue Feb 03 11:00:00 2026 | [email protected] | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 103 | 20.00 | cancelled | Tue Feb 03 11:00:00 2026 + 3 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 | | 104 | 3 | 100.00 | paid | Wed Feb 04 12:00:00 2026 | [email protected] | Carol | Sat Jan 03 12:00:00 2026 | | 104 | 100.00 | paid | Wed Feb 04 12:00:00 2026 + 4 | | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | | | | | | | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | | | | +(5 rows) + +-- CROSS JOIN with EXCLUDE +SELECT * EXCLUDE (email, status) FROM users, orders ORDER BY users.id, orders.id; + id | name | created_at | updated_at | id | user_id | amount | created_at +----+-------+--------------------------+--------------------------+-----+---------+--------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 +(16 rows) + +SELECT * EXCLUDE (email, status) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + id | name | created_at | updated_at | id | user_id | amount | created_at +----+-------+--------------------------+--------------------------+-----+---------+--------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 1 | Alice | Thu Jan 01 10:00:00 2026 | Mon Jan 05 09:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 | Tue Jan 06 10:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 | | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 101 | 1 | 50.00 | Sun Feb 01 09:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 102 | 1 | 75.50 | Mon Feb 02 10:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 103 | 2 | 20.00 | Tue Feb 03 11:00:00 2026 + 4 | Dave | Sun Jan 04 13:00:00 2026 | Wed Jan 07 11:00:00 2026 | 104 | 3 | 100.00 | Wed Feb 04 12:00:00 2026 +(16 rows) + +-- clean up +DROP TABLE IF EXISTS orders; +DROP TABLE IF EXISTS users; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 905f9bca959..614b6702262 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -102,7 +102,7 @@ test: publication subscription # Another group of parallel tests # select_views depends on create_view # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite +test: select_views select_exclude portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/select_exclude.sql b/src/test/regress/sql/select_exclude.sql new file mode 100644 index 00000000000..479472788b1 --- /dev/null +++ b/src/test/regress/sql/select_exclude.sql @@ -0,0 +1,164 @@ +-- +-- SELECT_EXCLUDE +-- + +SET client_min_messages TO 'warning'; + +DROP TABLE IF EXISTS users; +DROP TABLE IF EXISTS orders; + +RESET client_min_messages; + +CREATE TABLE users ( + id INT PRIMARY KEY, + email TEXT, + name TEXT, + created_at TIMESTAMP, + updated_at TIMESTAMP +); + +CREATE TABLE orders ( + id INT PRIMARY KEY, + user_id INT REFERENCES users(id), + amount NUMERIC(10,2), + status TEXT, + created_at TIMESTAMP +); + +-- Insert sample data +INSERT INTO users (id, email, name, created_at, updated_at) VALUES +(1,'[email protected]','Alice','2026-01-01 10:00:00','2026-01-05 09:00:00'), +(2,'[email protected]','Bob','2026-01-02 11:00:00','2026-01-06 10:00:00'), +(3,'[email protected]','Carol','2026-01-03 12:00:00',NULL), +(4,NULL,'Dave','2026-01-04 13:00:00','2026-01-07 11:00:00'); + +INSERT INTO orders (id, user_id, amount, status, created_at) VALUES +(101,1,50.00,'paid','2026-02-01 09:00:00'), +(102,1,75.50,'shipped','2026-02-02 10:00:00'), +(103,2,20.00,'cancelled','2026-02-03 11:00:00'), +(104,3,100.00,'paid','2026-02-04 12:00:00'); + +-- Basic SELECT with EXCLUDE condition +-- Single column +SELECT * EXCLUDE (updated_at) +FROM users +ORDER BY id; + +-- Multiple columns +SELECT * EXCLUDE (email, created_at) +FROM users +ORDER BY id; + +-- Exclude all but one column +SELECT * EXCLUDE (email, name, created_at, updated_at) +FROM users +ORDER BY id; + +-- Non-existent column, unqualified star (error case) +SELECT * EXCLUDE (does_not_exist) +FROM users; + +-- Non-existent column, qualified star (error case) +SELECT users.* EXCLUDE (does_not_exist) +FROM users; + +-- Aliasing with EXCLUDE +SELECT * EXCLUDE (u.email) +FROM users AS u +ORDER BY u.id; + +-- Expressions with EXCLUDE +SELECT * EXCLUDE (updated_at), 1 + 1 AS two +FROM users +ORDER BY id; + +-- JOINs with EXCLUDE +-- Join, unqualified EXCLUDE +SELECT * EXCLUDE (created_at) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, qualified EXCLUDE, one table +SELECT * EXCLUDE (users.created_at) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, qualified EXCLUDE, both tables +SELECT * EXCLUDE (users.created_at, orders.amount) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, aliased tables with EXCLUDE +SELECT * EXCLUDE (u.created_at, o.amount) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + +-- Qualified stars +SELECT users.* EXCLUDE (email), orders.* EXCLUDE (user_id) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Name collision +SELECT * EXCLUDE (id) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Subqueries with EXCLUDE +SELECT * EXCLUDE (u.created_at) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + +-- CTEs with EXCLUDE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * EXCLUDE (base_users.updated_at) +FROM base_users +ORDER BY id; + +-- WHERE clause with EXCLUDE +SELECT * EXCLUDE (email) +FROM users +WHERE email IS NOT NULL +ORDER BY created_at; + +-- DISTINCT with EXCLUDE +SELECT DISTINCT * EXCLUDE (updated_at) +FROM users; + +-- Error cases +-- Empty EXCLUDE list (error case) +SELECT * EXCLUDE () +FROM users; + +-- Exclude all columns (error case) +SELECT * EXCLUDE (id, email, name, created_at, updated_at) +FROM users; + +-- Exclude without star (error case) +SELECT id, email EXCLUDE (email) +FROM users; + +-- Multiple stars with EXCLUDE +SELECT +*, +users.* EXCLUDE (id), +orders.* EXCLUDE (user_id) +FROM users +LEFT JOIN orders ON orders.user_id = users.id; + +-- CROSS JOIN with EXCLUDE +SELECT * EXCLUDE (email, status) FROM users, orders ORDER BY users.id, orders.id; +SELECT * EXCLUDE (email, status) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + +-- clean up +DROP TABLE IF EXISTS orders; +DROP TABLE IF EXISTS users; -- 2.43.0
