Hi, PFA v2 of the patch fixing behavior described in examples 2, 3, 5, 8, and 11 from the proposal paper.
I have also expanded the test cases to cover the relevant behaviors described in the paper. The ambiguous column behavior described in example 6 is not changed in this patch, but it can be easily updated with a simple if condition if required. Regards, Hunaid Sohail >
From c6d566f050a21319750755211e0fc8cd4c57ef3f Mon Sep 17 00:00:00 2001 From: Hunaid2000 <[email protected]> Date: Mon, 16 Feb 2026 13:13:46 +0500 Subject: [PATCH v2] Implement SELECT * EXCLUDE ... command --- doc/src/sgml/ref/select.sgml | 56 +++- src/backend/nodes/makefuncs.c | 1 + src/backend/parser/analyze.c | 2 +- src/backend/parser/gram.y | 25 +- src/backend/parser/parse_relation.c | 71 +++- src/backend/parser/parse_target.c | 37 ++- 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 | 327 +++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/select_exclude.sql | 168 ++++++++++ 12 files changed, 679 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..4af60e1b517 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,52 @@ 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. Duplicate column names in the + <literal>EXCLUDE</literal> clause are not allowed. + </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 539c16c4f79..fda5e6ea2be 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1729,7 +1729,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 c567252acc4..e2cd0db250a 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 @@ -17492,15 +17493,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); @@ -17512,9 +17526,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..e5e757c5b24 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,71 @@ 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; + bool exclude_col_seen = false; + + 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) + { + /* This column was already seen in the EXCLUDE list */ + if (exclude_col_seen) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("duplicate column \"%s\" in EXCLUDE list", + excl_col))); + + nscol->p_dontexpand = true; + rv->exclude_exist = true; + exclude_col_seen = true; + } + } + colindex++; + } + } + vars = expandNSItemVars(pstate, nsitem, sublevels_up, location, &names); /* diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index dbf5b2b5c01..083d6cc7314 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,23 @@ 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), + parser_errposition(pstate, rv->location))); + } + } continue; } } @@ -1125,6 +1142,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) @@ -1138,7 +1156,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 { @@ -1278,7 +1296,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); } } @@ -1294,7 +1312,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; @@ -1317,7 +1335,8 @@ ExpandAllTables(ParseState *pstate, int location) nsitem, 0, true, - location)); + location, + exclude_list)); } /* @@ -1374,12 +1393,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 { @@ -1448,7 +1467,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 0aec49bdd22..cf13a9ae7b5 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 384df50c80a..9ef6dace7a1 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -94,6 +94,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..1da7024247d --- /dev/null +++ b/src/test/regress/expected/select_exclude.out @@ -0,0 +1,327 @@ +-- +-- 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) + +-- Exclude all columns +SELECT * EXCLUDE (id, email, name, created_at, updated_at) +FROM users; +-- +(4 rows) + +-- EXCLUDE all using exclude list but overall SELECT list is not empty +SELECT id, users.* EXCLUDE (id, email, name, created_at, updated_at) +FROM users; + id +---- + 1 + 2 + 3 + 4 +(4 rows) + +-- 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) + +-- 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) + +-- Error cases +-- Non-existent column in EXCLUDE list (error case) +SELECT * EXCLUDE (does_not_exist) +FROM users; +ERROR: column "does_not_exist" does not exist +LINE 1: SELECT * EXCLUDE (does_not_exist) + ^ +-- Empty EXCLUDE list (error case) +SELECT * EXCLUDE () +FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * EXCLUDE () + ^ +-- 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) + ^ +-- Exclude with duplicate column names (error case) +SELECT * EXCLUDE (id, id) +FROM users; +ERROR: duplicate column "id" in EXCLUDE list +-- 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 549e9b2d7be..bc847ac9ca7 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..bdf6ab95752 --- /dev/null +++ b/src/test/regress/sql/select_exclude.sql @@ -0,0 +1,168 @@ +-- +-- 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; + +-- Exclude all columns +SELECT * EXCLUDE (id, email, name, created_at, updated_at) +FROM users; + +-- EXCLUDE all using exclude list but overall SELECT list is not empty +SELECT id, users.* EXCLUDE (id, email, name, created_at, updated_at) +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; + +-- 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; + +-- Error cases +-- Non-existent column in EXCLUDE list (error case) +SELECT * EXCLUDE (does_not_exist) +FROM users; + +-- Empty EXCLUDE list (error case) +SELECT * EXCLUDE () +FROM users; + +-- Exclude without star (error case) +SELECT id, email EXCLUDE (email) +FROM users; + +-- Exclude with duplicate column names (error case) +SELECT * EXCLUDE (id, id) +FROM users; + +-- clean up +DROP TABLE IF EXISTS orders; +DROP TABLE IF EXISTS users; -- 2.43.0
