Hello, PFA v4 of the patch with following changes:
Staroptions scope is limited now by throwing errors from C functions (instead of gram.y) if used with expressions or other clauses like where/order by etc as reported above by Kacper. Since there were no other opinions (or objections) so RETURNING is now allowed with staroptions. Apart from code refactoring, a few more regression tests are added regarding RETURNING clause and nested queries. It would be nice to get some kind of update regarding this feature in SQL Standard to see if we are going in the right direction. Regards, Hunaid Sohail
From 86d1a20eb20189c481fb8cec5672d45ec78e7083 Mon Sep 17 00:00:00 2001 From: Hunaid2000 <[email protected]> Date: Wed, 28 Oct 2026 18:39:45 +0500 Subject: [PATCH v4] Implement SELECT * EXCLUDE/REPLACE/RENAME clauses --- doc/src/sgml/ref/select.sgml | 147 ++- src/backend/parser/analyze.c | 2 +- src/backend/parser/gram.y | 129 +- src/backend/parser/parse_expr.c | 12 + src/backend/parser/parse_relation.c | 250 +++- src/backend/parser/parse_target.c | 111 +- src/include/nodes/parsenodes.h | 37 + src/include/parser/parse_relation.h | 2 +- .../regress/expected/select_staroptions.out | 1068 +++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/select_staroptions.sql | 547 +++++++++ src/tools/pgindent/typedefs.list | 4 + 12 files changed, 2293 insertions(+), 18 deletions(-) create mode 100644 src/test/regress/expected/select_staroptions.out create mode 100644 src/test/regress/sql/select_staroptions.sql diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 18392f7caec..72bdbc94e0c 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> ] } [, ...] ] + [ { * [ ( <replaceable class="parameter">staroption</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 | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] ] @@ -78,6 +78,15 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac [ SEARCH { BREADTH | DEPTH } FIRST BY <replaceable>column_name</replaceable> [, ...] SET <replaceable>search_seq_col_name</replaceable> ] [ CYCLE <replaceable>column_name</replaceable> [, ...] SET <replaceable>cycle_mark_col_name</replaceable> [ TO <replaceable>cycle_mark_value</replaceable> DEFAULT <replaceable>cycle_mark_default</replaceable> ] USING <replaceable>cycle_path_col_name</replaceable> ] +<phrase>and <replaceable class="parameter">staroption</replaceable> is:</phrase> + + EXCLUDE ( <replaceable>column_name</replaceable> [, ...] ) + [ REPLACE ( <replaceable class="parameter">column_name</replaceable> WITH <replaceable class="parameter">expression</replaceable> [, ...] ) ] + [ RENAME ( <replaceable>column_name</replaceable> AS <replaceable class="parameter">output_name</replaceable> [, ...] ) ] + | REPLACE ( <replaceable class="parameter">column_name</replaceable> WITH <replaceable class="parameter">expression</replaceable> [, ...] ) + [ RENAME ( <replaceable>column_name</replaceable> AS <replaceable class="parameter">output_name</replaceable> [, ...] ) ] + | RENAME ( <replaceable>column_name</replaceable> AS <replaceable class="parameter">output_name</replaceable> [, ...] ) + TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] </synopsis> @@ -103,6 +112,30 @@ 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> + If the <literal>REPLACE</literal> clause is specified, all columns + listed there are replaced with the corresponding expressions when + <literal>*</literal> is used in the <literal>SELECT</literal> list. + </para> + </listitem> + + <listitem> + <para> + If the <literal>RENAME</literal> clause is specified, all columns + listed there are renamed to the corresponding output names 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 +415,118 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] </para> </refsect2> + <refsect2 id="sql-staroptions" xreflabel="Star Options"> + <title>Star Options</title> + + <para> + Star options allow you to exclude columns, replace their values, or + rename them when using <literal>*</literal> or a qualified + <literal>*</literal> in the <literal>SELECT</literal> list. + The options are specified in parentheses following the asterisk. + The sequence of the options is important, <literal>EXCLUDE</literal> + is applied first, then <literal>REPLACE</literal>, and finally + <literal>RENAME</literal>. At least one option must be specified + in the parentheses. + </para> + + <para> + Column names specified in star options may be qualified or + unqualified. Qualification uses the table name or alias specified + in the <literal>FROM</literal> clause. An unqualified column name + matches all columns with that name produced by the asterisk + expansion. The specified operation is applied to all matching + columns and no ambiguity error is raised. A qualified column name + matches only columns belonging to the specified table or alias. + </para> + + <para> + Each column name specified in a star option must match at least + one column produced by the asterisk expansion. Otherwise, an error + is raised. Duplicate column names within an individual option are + not allowed. + </para> + + <para> + A column specified in <literal>EXCLUDE</literal> cannot also be + specified in <literal>REPLACE</literal> or + <literal>RENAME</literal>. A column may, however, be specified + in both <literal>REPLACE</literal> and <literal>RENAME</literal>. + </para> + + <para> + The columns in the <literal>REPLACE</literal> and <literal>RENAME</literal> + clauses are marked for <literal>SELECT</literal> privilege checks, + whereas the columns in the <literal>EXCLUDE</literal> clause are not. + </para> + + <para> + For example, the following query excludes columns + <literal>a</literal> and <literal>b</literal>, + replaces the value of column <literal>c</literal>, + and renames column <literal>e</literal>: +<programlisting> +SELECT * (EXCLUDE (a, b) + REPLACE (c WITH x + y) + RENAME (e AS ee)) +FROM t; +</programlisting> + </para> + + <para> + The qualified-asterisk variant is also supported: +<programlisting> +SELECT t.* (EXCLUDE ...) FROM t ... +</programlisting> + </para> + </refsect2> + + <refsect2 id="sql-exclude" xreflabel="EXCLUDE Clause"> + <title><literal>EXCLUDE</literal> Clause</title> + + <para> + The <literal>EXCLUDE</literal> clause removes specified columns + from the output of an asterisk expansion. This is useful when + selecting from tables with many columns when only a few columns + need to be omitted. + </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 (ta.c_ta, tb.c_tb) ... + +-- Exclude specific columns with table qualification +SELECT ta.* EXCLUDE (c_ta), tb.* EXCLUDE (c_tb) ... +</programlisting> + </para> + </refsect2> + + <refsect2 id="sql-replace" xreflabel="REPLACE Clause"> + <title><literal>REPLACE</literal> Clause</title> + + <para> + The <literal>REPLACE</literal> clause replaces the values of + specified columns in the output of an asterisk expansion with + the results of the specified expressions. The replacement + expressions are specified using the + <literal>column_name WITH expression</literal> syntax. + Replacement does not change the names of the columns. + </para> + </refsect2> + + <refsect2 id="sql-rename" xreflabel="RENAME Clause"> + <title><literal>RENAME</literal> Clause</title> + + <para> + The <literal>RENAME</literal> clause changes the output names + of specified columns in an asterisk expansion. The new names + are specified using the <literal>column_name AS output_name</literal> + syntax. Renaming a column does not change its value. + </para> + </refsect2> + <refsect2 id="sql-from" xreflabel="FROM Clause"> <title><literal>FROM</literal> Clause</title> diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 2dc81a8d3b2..5bea0119f95 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1739,7 +1739,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 0563453fe24..e7b75192a62 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -156,6 +156,8 @@ static RawStmt *makeRawStmt(Node *stmt, int stmt_location); static void updateRawStmtEnd(RawStmt *rs, int end_location); static Node *makeColumnRef(char *colname, List *indirection, int location, core_yyscan_t yyscanner); +static Node *makeStarOptions(List *exclude_list, List *replace_list, + List *rename_list); static Node *makeTypeCast(Node *arg, TypeName *typename, int location); static Node *makeStringConstCast(char *str, int location, TypeName *typename); static Node *makeIntConst(int val, int location); @@ -455,6 +457,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list pub_all_obj_type_list pub_except_obj_list opt_pub_except_clause + exclude_list opt_replace replace_list opt_rename rename_list + +%type <node> staropt_column exclude_item replace_item rename_item opt_star_options star_options %type <retclause> returning_clause %type <node> returning_option @@ -17331,6 +17336,13 @@ indirection_el: { $$ = (Node *) makeNode(A_Star); } + | '.' '*' star_options + { + A_Star *s = makeNode(A_Star); + + s->staroptions = (StarOptions *) $3; + $$ = (Node *) s; + } | '[' a_expr ']' { A_Indices *ai = makeNode(A_Indices); @@ -17659,11 +17671,13 @@ target_el: a_expr AS ColLabel $$->val = (Node *) $1; $$->location = @1; } - | '*' + | '*' opt_star_options { ColumnRef *n = makeNode(ColumnRef); + A_Star *s = makeNode(A_Star); + s->staroptions = (StarOptions *) $2; - n->fields = list_make1(makeNode(A_Star)); + n->fields = list_make1(s); n->location = @1; $$ = makeNode(ResTarget); @@ -17674,6 +17688,104 @@ target_el: a_expr AS ColLabel } ; +star_options: + '(' EXCLUDE '(' exclude_list ')' opt_replace opt_rename ')' + { + $$ = makeStarOptions($4, $6, $7); + } + | '(' REPLACE '(' replace_list ')' opt_rename ')' + { + $$ = makeStarOptions(NIL, $4, $6); + } + | '(' RENAME '(' rename_list ')' ')' + { + $$ = makeStarOptions(NIL, NIL, $4); + } + ; + +opt_star_options: + star_options { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +staropt_column: + columnref + { + ColumnRef *c = (ColumnRef *) $1; + + c->staroptcol_match = false; + check_qualified_name(c->fields, yyscanner); + + $$ = (Node *) c; + } + ; + +exclude_list: + exclude_item + { $$ = list_make1($1); } + | exclude_list ',' exclude_item + { $$ = lappend($1, $3); } + ; + +exclude_item: + staropt_column + { + StarExcludeItem *n = makeNode(StarExcludeItem); + + n->name = (ColumnRef *) $1; + $$ = (Node *) n; + } + ; + +replace_list: + replace_item + { $$ = list_make1($1); } + | replace_list ',' replace_item + { $$ = lappend($1, $3); } + ; + +replace_item: + staropt_column WITH a_expr + { + StarReplaceItem *n = makeNode(StarReplaceItem); + + n->name = (ColumnRef *) $1; + n->expr = (Node *) $3; + $$ = (Node *) n; + } + ; + +opt_replace: + REPLACE '(' replace_list ')' + { $$ = $3; } + | /* EMPTY */ + { $$ = NIL; } + ; + +rename_list: + rename_item + { $$ = list_make1($1); } + | rename_list ',' rename_item + { $$ = lappend($1, $3); } + ; + +rename_item: + staropt_column AS ColId + { + StarRenameItem *n = makeNode(StarRenameItem); + + n->name = (ColumnRef *) $1; + n->aliasname = $3; + $$ = (Node *) n; + } + ; + +opt_rename: + RENAME '(' rename_list ')' + { $$ = $3; } + | /* EMPTY */ + { $$ = NIL; } + ; /***************************************************************************** * @@ -19168,6 +19280,19 @@ makeColumnRef(char *colname, List *indirection, return (Node *) c; } +static Node * +makeStarOptions(List *exclude_list, List *replace_list, + List *rename_list) +{ + StarOptions *n = makeNode(StarOptions); + + n->exclude_list = exclude_list; + n->replace_list = replace_list; + n->rename_list = rename_list; + + return (Node *) n; +} + static Node * makeTypeCast(Node *arg, TypeName *typename, int location) { diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 728392f436f..416f370ace8 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -524,6 +524,18 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) } crerr = CRERR_NO_COLUMN; const char *err; + /* Reject any star options for expressions */ + if (IsA(llast(cref->fields), A_Star)) + { + A_Star *star = (A_Star *) llast(cref->fields); + + if (star->staroptions) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("star options are not supported here"), + parser_errposition(pstate, cref->location))); + } + /* * Check to see if the column reference is in an invalid place within the * query. We allow column references in most places, except in default diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 8aeb3e219ac..dd4379d930b 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -22,9 +22,11 @@ #include "catalog/heap.h" #include "catalog/namespace.h" #include "funcapi.h" +#include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/parse_enr.h" +#include "parser/parse_expr.h" #include "parser/parse_relation.h" #include "parser/parse_type.h" #include "parser/parsetree.h" @@ -3299,6 +3301,236 @@ expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, return result; } +/* + * Check whether a column name matches any column in the + * EXCLUDE, REPLACE, or RENAME lists. + * + * For unqualified column names, match by column name regardless + * of which relation the column comes from. + * + * For qualified column names, resolve the relation qualifier using + * PostgreSQL's namespace lookup rules and compare the resulting RTE + * with the syntactic RTE of the current column (varnosyn). This avoids + * scanning the namespace item's columns and also handles aliases and + * join RTEs correctly. + */ +static bool +starOptionMatchesColumn(ParseState *pstate, + ColumnRef *cref, + Var *varnode, + char *label) +{ + ParseNamespaceItem *refnsitem = NULL; + char *schemaname = NULL; + char *relname = NULL; + char *colname = NULL; + + switch (list_length(cref->fields)) + { + case 1: + + /* + * Unqualified column name matches regardless of which RTE it came + * from. + */ + colname = strVal(linitial(cref->fields)); + return strcmp(colname, label) == 0; + case 2: + /* relation.column */ + relname = strVal(linitial(cref->fields)); + colname = strVal(lsecond(cref->fields)); + break; + case 3: + /* schema.relation.column */ + schemaname = strVal(linitial(cref->fields)); + relname = strVal(lsecond(cref->fields)); + colname = strVal(lthird(cref->fields)); + break; + case 4: + { + /* catalog.schema.relation.column */ + char *catalogname = strVal(linitial(cref->fields)); + + schemaname = strVal(lsecond(cref->fields)); + relname = strVal(lthird(cref->fields)); + colname = strVal(lfourth(cref->fields)); + + if (strcmp(catalogname, get_database_name(MyDatabaseId)) != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cross-database references are not implemented: %s", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location))); + break; + } + default: + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("improper qualified name (too many dotted names): %s", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location))); + } + + /* Locate the referenced nsitem */ + refnsitem = refnameNamespaceItem(pstate, schemaname, relname, + cref->location, + 0); + + if (refnsitem == NULL) + errorMissingRTE(pstate, makeRangeVar(schemaname, relname, + cref->location)); + + /* + * For a qualified name, make sure the column belongs to the RTE + * identified by the qualifier. Compare the RTE identified by the + * qualifier with the RTE from which the current expanded column + * originated. + * + * For RETURNING clause, check returning type as well to see if column is + * from correct OLD/NEW source. + */ + return strcmp(colname, label) == 0 && + refnsitem->p_rtindex == varnode->varnosyn && + refnsitem->p_returning_type == varnode->varreturningtype; +} + +/* + * Apply the EXCLUDE, REPLACE, and RENAME options from staroptions to a + * column being expanded. The expression and output name are updated as + * appropriate. Also, check for conflicts between the options. + * + * Returns true if the column is excluded, false otherwise. + */ +static bool +applyStarOptions(ParseState *pstate, + Var *varnode, + char *label, + Node **expr, + char **output_name, + StarOptions *staroptions) +{ + List *exclude_list = staroptions->exclude_list; + List *replace_list = staroptions->replace_list; + List *rename_list = staroptions->rename_list; + ListCell *lc; + bool excluded = false; + + /* EXCLUDE */ + if (exclude_list) + { + bool exclude_col_seen = false; + + foreach(lc, exclude_list) + { + StarExcludeItem *item = (StarExcludeItem *) lfirst(lc); + ColumnRef *cref = item->name; + + if (starOptionMatchesColumn(pstate, cref, varnode, label)) + { + if (exclude_col_seen) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("duplicate column \"%s\" in EXCLUDE list", + NameListToString(cref->fields)))); + + exclude_col_seen = true; + excluded = true; + cref->staroptcol_match = true; + } + } + } + + /* An excluded column cannot also occur in REPLACE or RENAME lists */ + if (excluded) + { + if (replace_list) + { + foreach(lc, replace_list) + { + StarReplaceItem *item = (StarReplaceItem *) lfirst(lc); + ColumnRef *cref = item->name; + + if (starOptionMatchesColumn(pstate, cref, varnode, label)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" cannot occur in both EXCLUDE and REPLACE list", + NameListToString(cref->fields)))); + } + } + + if (rename_list) + { + foreach(lc, rename_list) + { + StarRenameItem *item = (StarRenameItem *) lfirst(lc); + ColumnRef *cref = item->name; + + if (starOptionMatchesColumn(pstate, cref, varnode, label)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" cannot occur in both EXCLUDE and RENAME list", + NameListToString(cref->fields)))); + } + } + + return true; + } + + /* REPLACE */ + if (replace_list) + { + bool replace_col_seen = false; + + foreach(lc, replace_list) + { + StarReplaceItem *item = (StarReplaceItem *) lfirst(lc); + ColumnRef *cref = (ColumnRef *) item->name; + + if (starOptionMatchesColumn(pstate, cref, varnode, label)) + { + if (replace_col_seen) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("duplicate column \"%s\" in REPLACE list", + NameListToString(cref->fields)))); + + *expr = transformExpr(pstate, + (Node *) item->expr, + EXPR_KIND_SELECT_TARGET); + cref->staroptcol_match = true; + replace_col_seen = true; + } + } + } + + /* RENAME */ + if (rename_list) + { + bool rename_col_seen = false; + + foreach(lc, rename_list) + { + StarRenameItem *item = (StarRenameItem *) lfirst(lc); + ColumnRef *cref = (ColumnRef *) item->name; + + if (starOptionMatchesColumn(pstate, cref, varnode, label)) + { + if (rename_col_seen) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("duplicate column \"%s\" in RENAME list", + NameListToString(cref->fields)))); + + *output_name = item->aliasname; + cref->staroptcol_match = true; + rename_col_seen = true; + } + } + } + + return false; +} + /* * expandNSItemAttrs - * Workhorse for "*" expansion: produce a list of targetentries @@ -3310,7 +3542,8 @@ expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, */ List * expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, - int sublevels_up, bool require_col_privs, int location) + int sublevels_up, bool require_col_privs, int location, + StarOptions *staroptions) { RangeTblEntry *rte = nsitem->p_rte; RTEPermissionInfo *perminfo = nsitem->p_perminfo; @@ -3340,11 +3573,22 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem, { char *label = strVal(lfirst(name)); Var *varnode = (Var *) lfirst(var); + char *output_name = label; + Node *output_expr = (Node *) varnode; TargetEntry *te; - te = makeTargetEntry((Expr *) varnode, + /* Handle EXCLUDE, REPLACE, and RENAME lists */ + if (staroptions && applyStarOptions(pstate, varnode, label, + &output_expr, &output_name, + staroptions)) + { + /* column is excluded, so skip it */ + continue; + } + + te = makeTargetEntry((Expr *) output_expr, (AttrNumber) pstate->p_next_resno++, - label, + output_name, false); te_list = lappend(te_list, te); diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 728936b502e..78b7827e944 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, StarOptions *staroptions); 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, StarOptions *staroptions); static List *ExpandRowReference(ParseState *pstate, Node *expr, bool make_target_entry); static int FigureColnameInternal(Node *node, char **name); @@ -107,6 +107,68 @@ transformTargetEntry(ParseState *pstate, resjunk); } +/* + * checkStarOptionList() + * Check that all columns mentioned in the list actually exist. + * + * Extract the relation and column names from the ColumnRef. + * We do not care about other qualification errors here, + * since they were already checked during column matching. + */ +static void +checkStarOptionList(ParseState *pstate, List *list) +{ + ListCell *lc; + + foreach(lc, list) + { + Node *node = (Node *) lfirst(lc); + ColumnRef *cref; + char *relname = NULL; + char *colname = NULL; + + /* Check that the node is one of the expected star option types */ + Assert(IsA(node, StarExcludeItem) || + IsA(node, StarReplaceItem) || + IsA(node, StarRenameItem)); + + switch (nodeTag(node)) + { + case T_StarExcludeItem: + cref = ((StarExcludeItem *) node)->name; + break; + case T_StarReplaceItem: + cref = ((StarReplaceItem *) node)->name; + break; + case T_StarRenameItem: + cref = ((StarRenameItem *) node)->name; + break; + default: + elog(ERROR, "unexpected node type in staroptions list: %d", + (int) nodeTag(node)); + } + + colname = strVal(llast(cref->fields)); + if (list_length(cref->fields) > 1) + relname = strVal(list_nth(cref->fields, list_length(cref->fields) - 2)); + + if (!cref->staroptcol_match) + errorMissingColumn(pstate, relname, colname, cref->location); + } +} + +/* + * checkStarOptions() + * Check each star option list for missing columns. + */ +static void +checkStarOptions(ParseState *pstate, StarOptions *staroptions) +{ + checkStarOptionList(pstate, staroptions->exclude_list); + checkStarOptionList(pstate, staroptions->replace_list); + checkStarOptionList(pstate, staroptions->rename_list); +} + /* * transformTargetList() @@ -147,11 +209,15 @@ transformTargetList(ParseState *pstate, List *targetlist, if (IsA(llast(cref->fields), A_Star)) { + A_Star *star = (A_Star *) llast(cref->fields); + /* It is something.*, expand into multiple items */ p_target = list_concat(p_target, ExpandColumnRefStar(pstate, cref, true)); + if (star->staroptions) + checkStarOptions(pstate, star->staroptions); continue; } } @@ -1126,6 +1192,20 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, { List *fields = cref->fields; int numnames = list_length(fields); + StarOptions *staroptions = NULL; + + /* + * This function should only be called when the last field is a '*' + */ + Assert(IsA(llast(fields), A_Star)); + staroptions = ((A_Star *) llast(fields))->staroptions; + + /* Reject any star options for expressions */ + if (staroptions && !make_target_entry) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("star options are not supported here"), + parser_errposition(pstate, cref->location))); if (numnames == 1) { @@ -1138,7 +1218,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, staroptions); } else { @@ -1278,7 +1358,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, staroptions); } } @@ -1294,7 +1374,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, StarOptions *staroptions) { List *target = NIL; bool found_table = false; @@ -1317,7 +1397,8 @@ ExpandAllTables(ParseState *pstate, int location) nsitem, 0, true, - location)); + location, + staroptions)); } /* @@ -1351,6 +1432,18 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, { Node *expr; + /* Reject any star options here */ + if (IsA(llast(ind->indirection), A_Star)) + { + A_Star *star = (A_Star *) llast(ind->indirection); + + if (star->staroptions) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("star options are not supported here"), + parser_errposition(pstate, exprLocation(ind->arg)))); + } + /* Strip off the '*' to create a reference to the rowtype object */ ind = copyObject(ind); ind->indirection = list_truncate(ind->indirection, @@ -1374,12 +1467,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, StarOptions *staroptions) { if (make_target_entry) { /* expandNSItemAttrs handles permissions marking */ - return expandNSItemAttrs(pstate, nsitem, sublevels_up, true, location); + return expandNSItemAttrs(pstate, nsitem, sublevels_up, true, location, staroptions); } else { @@ -1448,7 +1541,7 @@ ExpandRowReference(ParseState *pstate, Node *expr, ParseNamespaceItem *nsitem; nsitem = GetNSItemByVar(pstate, var); - 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 0debcd193ab..d416a1b7af7 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -310,9 +310,45 @@ typedef struct ColumnRef { NodeTag type; List *fields; /* field names (String nodes) or A_Star */ + + /* + * These fields are only used with star option columns. It would be + * redundant to store them in each Star*Item, so we store them here + * instead. + */ + bool staroptcol_match; /* true if this star option column is + * matched; for reporting errors */ ParseLoc location; /* token location, or -1 if unknown */ } ColumnRef; +typedef struct StarExcludeItem +{ + NodeTag type; + ColumnRef *name; /* possibly qualified column name */ +} StarExcludeItem; + +typedef struct StarReplaceItem +{ + NodeTag type; + ColumnRef *name; /* possibly qualified column name */ + Node *expr; /* expression to replace the column with */ +} StarReplaceItem; + +typedef struct StarRenameItem +{ + NodeTag type; + ColumnRef *name; /* possibly qualified column name */ + char *aliasname; /* new output name for the column */ +} StarRenameItem; + +typedef struct StarOptions +{ + NodeTag type; + List *exclude_list; /* List of StarExcludeItem */ + List *replace_list; /* List of StarReplaceItem */ + List *rename_list; /* List of StarRenameItem */ +} StarOptions; + /* * ParamRef - specifies a $n parameter reference */ @@ -472,6 +508,7 @@ typedef struct FuncCall typedef struct A_Star { NodeTag type; + StarOptions *staroptions; /* options for '*' in fields, or NULL if none */ } A_Star; /* diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index ecfb4c047c0..96803aed764 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -124,7 +124,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, StarOptions *staroptions); 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_staroptions.out b/src/test/regress/expected/select_staroptions.out new file mode 100644 index 00000000000..f022ad0acda --- /dev/null +++ b/src/test/regress/expected/select_staroptions.out @@ -0,0 +1,1068 @@ +-- +-- SELECT EXCLUDE/REPLACE/RENAME +-- +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, + name TEXT, + email TEXT, + created_at TIMESTAMP +); +CREATE TABLE orders ( + id INT PRIMARY KEY, + user_id INT REFERENCES users(id), + amount NUMERIC(10,2), + status TEXT +); +CREATE SCHEMA s1; +CREATE SCHEMA s2; +CREATE TABLE s1.products ( + id INT PRIMARY KEY, + name TEXT, + price NUMERIC(10,2) +); +CREATE TABLE s2.products ( + id INT PRIMARY KEY, + name TEXT, + price NUMERIC(10,2), + description TEXT +); +-- Insert sample data +INSERT INTO users (id, name, email, created_at) VALUES +(1, 'Alice', '[email protected]', '2026-01-01 10:00:00'), +(2, 'Bob', '[email protected]', '2026-01-02 11:00:00'), +(3, 'Carol', NULL, '2026-01-03 12:00:00'); +INSERT INTO orders (id, user_id, amount, status) VALUES +(101, 1, 50.00, 'paid'), +(102, 1, 75.50, 'shipped'), +(103, 2, 20.00, 'cancelled'); +INSERT INTO s1.products (id, name, price) VALUES +(1, 'Product A', 10.00), +(2, 'Product B', 15.50); +INSERT INTO s2.products (id, name, price, description) VALUES +(1, 'Product X', 20.00, 'Description for X'), +(2, 'Product Y', 25.75, 'Description for Y'); +-- +-- EXCLUDE clause +-- +-- Simple EXCLUDE +SELECT * (EXCLUDE (email, created_at)) +FROM users +ORDER BY id; + id | name +----+------- + 1 | Alice + 2 | Bob + 3 | Carol +(3 rows) + +-- Exclude all but one column +SELECT * (EXCLUDE (name, email, created_at)) +FROM users +ORDER BY id; + id +---- + 1 + 2 + 3 +(3 rows) + +-- Exclude all columns +SELECT * (EXCLUDE (id, name, email, created_at)) +FROM users; +-- +(3 rows) + +-- EXCLUDE all using exclude list but overall SELECT list is not empty +SELECT id, users.* (EXCLUDE (id, name, email, created_at)) +FROM users +ORDER BY id; + id +---- + 1 + 2 + 3 +(3 rows) + +-- Aliasing with EXCLUDE +SELECT * (EXCLUDE (u.email)) +FROM users AS u +ORDER BY u.id; + id | name | created_at +----+-------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- Expressions with EXCLUDE +SELECT * (EXCLUDE (name)), 1 + 1 AS two +FROM users +ORDER BY id; + id | email | created_at | two +----+-------------------+--------------------------+----- + 1 | [email protected] | Thu Jan 01 10:00:00 2026 | 2 + 2 | [email protected] | Fri Jan 02 11:00:00 2026 | 2 + 3 | | Sat Jan 03 12:00:00 2026 | 2 +(3 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 | name | email | id | user_id | amount | status +----+-------+-------------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Join, qualified EXCLUDE +SELECT * (EXCLUDE (users.created_at, orders.amount)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | id | user_id | status +----+-------+-------------------+-----+---------+----------- + 1 | Alice | [email protected] | 101 | 1 | paid + 1 | Alice | [email protected] | 102 | 1 | shipped + 2 | Bob | [email protected] | 103 | 2 | cancelled +(3 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 | name | email | id | user_id | status +----+-------+-------------------+-----+---------+----------- + 1 | Alice | [email protected] | 101 | 1 | paid + 1 | Alice | [email protected] | 102 | 1 | shipped + 2 | Bob | [email protected] | 103 | 2 | cancelled +(3 rows) + +-- Qualified stars; EXCLUDE is applied to each star separately +SELECT +users.* (EXCLUDE (id)), +orders.* (EXCLUDE (user_id)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + name | email | created_at | id | amount | status +-------+-------------------+--------------------------+-----+--------+----------- + Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 50.00 | paid + Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 75.50 | shipped + Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 20.00 | cancelled + Carol | | Sat Jan 03 12:00:00 2026 | | | +(4 rows) + +-- Schema-qualified star +SELECT s1.products.* (EXCLUDE (price)) +FROM s1.products +ORDER BY id; + id | name +----+----------- + 1 | Product A + 2 | Product B +(2 rows) + +-- Schema-qualified column +SELECT * (EXCLUDE (s1.products.price)) +FROM s1.products +ORDER BY id; + id | name +----+----------- + 1 | Product A + 2 | Product B +(2 rows) + +-- Name collision +SELECT * (EXCLUDE (id)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + name | email | created_at | user_id | amount | status +-------+-------------------+--------------------------+---------+--------+----------- + Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 1 | 50.00 | paid + Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 1 | 75.50 | shipped + Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 2 | 20.00 | cancelled +(3 rows) + +-- Subqueries with EXCLUDE +SELECT * (EXCLUDE (u.created_at)) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + id | name | email +----+-------+------------------- + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | +(3 rows) + +SELECT * (EXCLUDE (j.created_at)) +FROM ( + users JOIN orders USING (id) +) AS j; + id | name | email | user_id | amount | status +----+------+-------+---------+--------+-------- +(0 rows) + +-- Nested subqueries with EXCLUDE +SELECT * (EXCLUDE (email, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM users); + name +------- + Alice + Bob + Carol +(3 rows) + +SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM users); -- error, cannot exclude id again in outer query +ERROR: column "id" does not exist +LINE 1: SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE ... + ^ +-- CTEs with EXCLUDE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (EXCLUDE (base_users.created_at)) +FROM base_users +ORDER BY id; + id | name | email +----+-------+------------------- + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | +(3 rows) + +WITH CTE AS NOT MATERIALIZED (SELECT * (EXCLUDE (id, created_at)) FROM users) +SELECT * (EXCLUDE (id)) FROM CTE; -- error, cannot exclude id again in outer query +ERROR: column "id" does not exist +LINE 2: SELECT * (EXCLUDE (id)) FROM CTE; + ^ +-- Multiple FROM items with EXCLUDE +SELECT * (EXCLUDE (email, status)) FROM users, orders ORDER BY users.id, orders.id; + id | name | created_at | id | user_id | amount +----+-------+--------------------------+-----+---------+-------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 + 1 | Alice | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 + 1 | Alice | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 +(9 rows) + +-- CROSS JOIN with EXCLUDE +SELECT * (EXCLUDE (email, status)) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + id | name | created_at | id | user_id | amount +----+-------+--------------------------+-----+---------+-------- + 1 | Alice | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 + 1 | Alice | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 + 1 | Alice | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 + 2 | Bob | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 + 3 | Carol | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 +(9 rows) + +-- +-- REPLACE clause +-- +-- Simple REPLACE +SELECT * (REPLACE (email WITH '[email protected]', created_at WITH NULL)) +FROM users +ORDER BY id; + id | name | email | created_at +----+-------+------------------+------------ + 1 | Alice | [email protected] | + 2 | Bob | [email protected] | + 3 | Carol | [email protected] | +(3 rows) + +-- Aliasing with REPLACE +SELECT * (REPLACE (u.email WITH '[email protected]')) +FROM users AS u +ORDER BY u.id; + id | name | email | created_at +----+-------+------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- Expressions with REPLACE +SELECT * (REPLACE (created_at WITH NULL)), 1 + 1 AS two +FROM users +ORDER BY id; + id | name | email | created_at | two +----+-------+-------------------+------------+----- + 1 | Alice | [email protected] | | 2 + 2 | Bob | [email protected] | | 2 + 3 | Carol | | | 2 +(3 rows) + +-- JOINs with REPLACE +-- Join, unqualified REPLACE +SELECT * (REPLACE (created_at WITH created_at + INTERVAL '1 day')) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +----+-------+-------------------+--------------------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | Fri Jan 02 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Fri Jan 02 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Sat Jan 03 11:00:00 2026 | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Join, qualified REPLACE +SELECT * (REPLACE (users.created_at WITH NULL, orders.amount WITH orders.amount * 2)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +----+-------+-------------------+------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | | 101 | 1 | 100.00 | paid + 1 | Alice | [email protected] | | 102 | 1 | 151.00 | shipped + 2 | Bob | [email protected] | | 103 | 2 | 40.00 | cancelled +(3 rows) + +-- Join, aliased tables with REPLACE +SELECT * (REPLACE (u.created_at WITH NULL, o.amount WITH o.amount * 2)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + id | name | email | created_at | id | user_id | amount | status +----+-------+-------------------+------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | | 101 | 1 | 100.00 | paid + 1 | Alice | [email protected] | | 102 | 1 | 151.00 | shipped + 2 | Bob | [email protected] | | 103 | 2 | 40.00 | cancelled +(3 rows) + +-- Qualified stars; REPLACE is applied to each star separately +SELECT +users.* (REPLACE (id WITH 999)), +orders.* (REPLACE (user_id WITH NULL)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +-----+-------+-------------------+--------------------------+-----+---------+--------+----------- + 999 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | | 50.00 | paid + 999 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | | 75.50 | shipped + 999 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | | 20.00 | cancelled + 999 | Carol | | Sat Jan 03 12:00:00 2026 | | | | +(4 rows) + +-- Schema-qualified star +SELECT s1.products.* (REPLACE (price WITH price * 1.1)) +FROM s1.products +ORDER BY id; + id | name | price +----+-----------+-------- + 1 | Product A | 11.000 + 2 | Product B | 17.050 +(2 rows) + +-- Schema-qualified column +SELECT * (REPLACE (s1.products.price WITH s1.products.price * 1.1)) +FROM s1.products +ORDER BY id; + id | name | price +----+-----------+-------- + 1 | Product A | 11.000 + 2 | Product B | 17.050 +(2 rows) + +-- Name collision +SELECT * (REPLACE (id WITH 999)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +-----+-------+-------------------+--------------------------+-----+---------+--------+----------- + 999 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 999 | 1 | 50.00 | paid + 999 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 999 | 1 | 75.50 | shipped + 999 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 999 | 2 | 20.00 | cancelled +(3 rows) + +-- Subqueries with REPLACE +SELECT * (REPLACE (u.created_at WITH u.created_at + INTERVAL '1 day')) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + id | name | email | created_at +----+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Fri Jan 02 10:00:00 2026 + 2 | Bob | [email protected] | Sat Jan 03 11:00:00 2026 + 3 | Carol | | Sun Jan 04 12:00:00 2026 +(3 rows) + +SELECT * (REPLACE (j.created_at WITH j.created_at + INTERVAL '1 day')) +FROM ( + users JOIN orders USING (id) +) AS j; + id | name | email | created_at | user_id | amount | status +----+------+-------+------------+---------+--------+-------- +(0 rows) + +-- Nested subqueries with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', created_at WITH NULL)) FROM (SELECT * (REPLACE (id WITH 999)) FROM users); + id | name | email | created_at +-----+-------+------------------+------------ + 999 | Alice | [email protected] | + 999 | Bob | [email protected] | + 999 | Carol | [email protected] | +(3 rows) + +SELECT * (REPLACE (id WITH 0, created_at WITH NULL)) FROM (SELECT * (REPLACE (id WITH 999)) FROM users); + id | name | email | created_at +----+-------+-------------------+------------ + 0 | Alice | [email protected] | + 0 | Bob | [email protected] | + 0 | Carol | | +(3 rows) + +-- CTEs with REPLACE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (REPLACE (base_users.created_at WITH NULL)) +FROM base_users +ORDER BY id; + id | name | email | created_at +----+-------+-------------------+------------ + 1 | Alice | [email protected] | + 2 | Bob | [email protected] | + 3 | Carol | | +(3 rows) + +WITH CTE AS NOT MATERIALIZED (SELECT * (REPLACE (id WITH 999, created_at WITH NULL)) FROM users) +SELECT * (REPLACE (id WITH 0)) FROM CTE; + id | name | email | created_at +----+-------+-------------------+------------ + 0 | Alice | [email protected] | + 0 | Bob | [email protected] | + 0 | Carol | | +(3 rows) + +-- Multiple FROM items with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', status WITH 'N/A')) FROM users, orders ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +----+-------+------------------+--------------------------+-----+---------+--------+-------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | N/A + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | N/A + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 | N/A +(9 rows) + +-- CROSS JOIN with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', status WITH 'N/A')) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + id | name | email | created_at | id | user_id | amount | status +----+-------+------------------+--------------------------+-----+---------+--------+-------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | N/A + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | N/A + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 | N/A + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 | N/A + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 | N/A +(9 rows) + +-- +-- RENAME clause +-- +-- Simple RENAME +SELECT * (RENAME (name AS user_name, email AS user_email)) +FROM users +ORDER BY id; + id | user_name | user_email | created_at +----+-----------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- Aliasing with RENAME +SELECT * (RENAME (u.email AS user_email)) +FROM users AS u +ORDER BY u.id; + id | name | user_email | created_at +----+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- Expressions with RENAME +SELECT * (RENAME (created_at AS date_created)), 1 + 1 AS two +FROM users +ORDER BY id; + id | name | email | date_created | two +----+-------+-------------------+--------------------------+----- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 2 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 2 + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 2 +(3 rows) + +-- JOINs with RENAME +-- Join, unqualified RENAME +SELECT * (RENAME (created_at AS date_created)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | date_created | id | user_id | amount | status +----+-------+-------------------+--------------------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Join, qualified RENAME +SELECT * (RENAME (users.created_at AS date_created, orders.amount AS order_amount)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | date_created | id | user_id | order_amount | status +----+-------+-------------------+--------------------------+-----+---------+--------------+----------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Join, aliased tables with RENAME +SELECT * (RENAME (u.created_at AS date_created, o.amount AS order_amount)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + id | name | email | date_created | id | user_id | order_amount | status +----+-------+-------------------+--------------------------+-----+---------+--------------+----------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Qualified stars; RENAME is applied to each star separately +SELECT +users.* (RENAME (id AS uid)), +orders.* (RENAME (user_id AS customer_id)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + uid | name | email | created_at | id | customer_id | amount | status +-----+-------+-------------------+--------------------------+-----+-------------+--------+----------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled + 3 | Carol | | Sat Jan 03 12:00:00 2026 | | | | +(4 rows) + +-- Schema-qualified star +SELECT s1.products.* (RENAME (price AS product_price)) +FROM s1.products +ORDER BY id; + id | name | product_price +----+-----------+--------------- + 1 | Product A | 10.00 + 2 | Product B | 15.50 +(2 rows) + +-- Schema-qualified column +SELECT * (RENAME (s1.products.price AS product_price)) +FROM s1.products +ORDER BY id; + id | name | product_price +----+-----------+--------------- + 1 | Product A | 10.00 + 2 | Product B | 15.50 +(2 rows) + +-- Name collision +SELECT * (RENAME (id AS _id)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + _id | name | email | created_at | _id | user_id | amount | status +-----+-------+-------------------+--------------------------+-----+---------+--------+----------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Subqueries with RENAME +SELECT * (RENAME (u.created_at AS date_created)) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + id | name | email | date_created +----+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +SELECT * (RENAME (j.created_at AS date_created)) +FROM ( + users JOIN orders USING (id) +) AS j; + id | name | email | date_created | user_id | amount | status +----+------+-------+--------------+---------+--------+-------- +(0 rows) + +-- Nested subqueries with RENAME +SELECT * (RENAME (email AS user_email, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users); + uid | name | user_email | date_created +-----+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +SELECT * (RENAME (uid AS new_uid, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users); + new_uid | name | email | date_created +---------+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +SELECT * (RENAME (id AS new_uid, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users) AS u; -- error, "id" was already renamed in inner query +ERROR: column "id" does not exist +LINE 1: SELECT * (RENAME (id AS new_uid, created_at AS date_created)... + ^ +HINT: Perhaps you meant to reference the column "u.uid". +-- CTEs with RENAME +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (RENAME (base_users.created_at AS date_created)) +FROM base_users +ORDER BY id; + id | name | email | date_created +----+-------+-------------------+-------------------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Carol | | Sat Jan 03 12:00:00 2026 +(3 rows) + +WITH CTE AS NOT MATERIALIZED (SELECT * (RENAME (id AS uid, created_at AS date_created)) FROM users) +SELECT * (RENAME (id AS new_id)) FROM CTE; -- error, "id" was already renamed in inner query +ERROR: column "id" does not exist +LINE 2: SELECT * (RENAME (id AS new_id)) FROM CTE; + ^ +HINT: Perhaps you meant to reference the column "cte.uid". +-- Multiple FROM items with RENAME +SELECT * (RENAME (email AS user_email, status AS order_status)) FROM users, orders ORDER BY users.id, orders.id; + id | name | user_email | created_at | id | user_id | amount | order_status +----+-------+-------------------+--------------------------+-----+---------+--------+-------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 | cancelled + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 | paid + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 | paid + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 | shipped + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 | cancelled +(9 rows) + +-- CROSS JOIN with RENAME +SELECT * (RENAME (email AS user_email, status AS order_status)) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + id | name | user_email | created_at | id | user_id | amount | order_status +----+-------+-------------------+--------------------------+-----+---------+--------+-------------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 | shipped + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 | cancelled + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 | paid + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 | cancelled + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 | paid + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 | shipped + 3 | Carol | | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 | cancelled +(9 rows) + +-- +-- EXCLUDE/REPLACE/RENAME combinations +-- +-- EXCLUDE + REPLACE different columns +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]')) +FROM users; + id | name | email +----+-------+------------------ + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | [email protected] +(3 rows) + +-- EXCLUDE + RENAME different columns +SELECT * (EXCLUDE (created_at) RENAME (email AS user_email)) +FROM users; + id | name | user_email +----+-------+------------------- + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | +(3 rows) + +-- REPLACE + RENAME on same column +SELECT * (REPLACE (email WITH '[email protected]', name WITH 'Unknown') RENAME (email AS user_email, name AS user_name)) +FROM users +ORDER BY id; + id | user_name | user_email | created_at +----+-----------+------------------+-------------------------- + 1 | Unknown | [email protected] | Thu Jan 01 10:00:00 2026 + 2 | Unknown | [email protected] | Fri Jan 02 11:00:00 2026 + 3 | Unknown | [email protected] | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- REPLACE + RENAME on same column with EXCLUDE +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (email AS user_email)) +FROM users; + id | name | user_email +----+-------+------------------ + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | [email protected] +(3 rows) + +-- All three clauses on different columns +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (name AS user_name)) +FROM users +ORDER BY id; + id | user_name | email +----+-----------+------------------ + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | [email protected] +(3 rows) + +-- JOIN, unqualified +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (amount AS order_amount)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + id | name | email | id | user_id | order_amount | status +----+-------+------------------+-----+---------+--------------+----------- + 1 | Alice | [email protected] | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- JOIN, qualified + aliased tables +SELECT * (EXCLUDE (u.created_at) REPLACE (u.email WITH '[email protected]') RENAME (o.amount AS order_amount)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + id | name | email | id | user_id | order_amount | status +----+-------+------------------+-----+---------+--------------+----------- + 1 | Alice | [email protected] | 101 | 1 | 50.00 | paid + 1 | Alice | [email protected] | 102 | 1 | 75.50 | shipped + 2 | Bob | [email protected] | 103 | 2 | 20.00 | cancelled +(3 rows) + +-- Qualified stars with all three +SELECT +users.* (EXCLUDE (email) REPLACE (name WITH 'Unknown') RENAME (id AS uid)), +orders.* (EXCLUDE (status) REPLACE (amount WITH amount * 2) RENAME (id AS oid)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + uid | name | created_at | oid | user_id | amount +-----+---------+--------------------------+-----+---------+-------- + 1 | Unknown | Thu Jan 01 10:00:00 2026 | 101 | 1 | 100.00 + 1 | Unknown | Thu Jan 01 10:00:00 2026 | 102 | 1 | 151.00 + 2 | Unknown | Fri Jan 02 11:00:00 2026 | 103 | 2 | 40.00 +(3 rows) + +-- Schema-qualified star +SELECT s1.products.* (EXCLUDE (price) REPLACE (name WITH 'Product X') RENAME (id AS product_id)) +FROM s1.products +ORDER BY id; + product_id | name +------------+----------- + 1 | Product X + 2 | Product X +(2 rows) + +-- Schema-qualified column +SELECT * (EXCLUDE (s1.products.price) REPLACE (s1.products.name WITH 'Product X') RENAME (s1.products.id AS product_id)) +FROM s1.products +ORDER BY id; + product_id | name +------------+----------- + 1 | Product X + 2 | Product X +(2 rows) + +-- Subquery with all three +SELECT * (EXCLUDE (u.created_at) REPLACE (u.email WITH '[email protected]') RENAME (u.name AS user_name)) +FROM ( + SELECT * FROM users +) AS u +ORDER BY id; + id | user_name | email +----+-----------+------------------ + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | [email protected] +(3 rows) + +-- CTE with all three +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (EXCLUDE (base_users.created_at) REPLACE (base_users.email WITH '[email protected]') RENAME (base_users.name AS user_name)) +FROM base_users +ORDER BY id; + id | user_name | email +----+-----------+------------------ + 1 | Alice | [email protected] + 2 | Bob | [email protected] + 3 | Carol | [email protected] +(3 rows) + +-- CROSS JOIN with all three +SELECT * (EXCLUDE (status) REPLACE (email WITH '[email protected]') RENAME (users.id AS uid, orders.id AS oid)) FROM users, orders ORDER BY users.id, orders.id; + uid | name | email | created_at | oid | user_id | amount +-----+-------+------------------+--------------------------+-----+---------+-------- + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 101 | 1 | 50.00 + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 102 | 1 | 75.50 + 1 | Alice | [email protected] | Thu Jan 01 10:00:00 2026 | 103 | 2 | 20.00 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 101 | 1 | 50.00 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 102 | 1 | 75.50 + 2 | Bob | [email protected] | Fri Jan 02 11:00:00 2026 | 103 | 2 | 20.00 + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 101 | 1 | 50.00 + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 102 | 1 | 75.50 + 3 | Carol | [email protected] | Sat Jan 03 12:00:00 2026 | 103 | 2 | 20.00 +(9 rows) + +-- Test 3-part qualification with JOIN on different schema tables +SELECT * (EXCLUDE (s1.products.name) REPLACE (s2.products.name WITH 'Z') RENAME (s1.products.id AS p1_id, s2.products.id AS p2_id)) +FROM s1.products +JOIN s2.products ON s1.products.id = s2.products.id +ORDER BY s1.products.id; + p1_id | price | p2_id | name | price | description +-------+-------+-------+------+-------+------------------- + 1 | 10.00 | 1 | Z | 20.00 | Description for X + 2 | 15.50 | 2 | Z | 25.75 | Description for Y +(2 rows) + +-- Test 4-part qualification +SELECT * (EXCLUDE (regression.public.users.email)) +FROM users +ORDER BY id; + id | name | created_at +----+-------+-------------------------- + 1 | Alice | Thu Jan 01 10:00:00 2026 + 2 | Bob | Fri Jan 02 11:00:00 2026 + 3 | Carol | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- SELECT INTO with star options +CREATE TABLE tbl_a (a int, b int, c int); +CREATE TABLE tbl_b (c int, d int, e int); +INSERT INTO tbl_a VALUES (1, 2, 3), (4, 5, 6); +INSERT INTO tbl_b VALUES (6, 7, 8), (9, 10, 11); +SELECT * (EXCLUDE (b, e)) INTO tbl_copy +FROM tbl_a +JOIN tbl_b ON tbl_a.c = tbl_b.c +ORDER BY tbl_a.a, tbl_b.c; -- error, as c is in both tables +ERROR: column "c" specified more than once +SELECT * (EXCLUDE (ta.c, tb.d)) INTO tbl_copy +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +ORDER BY ta.a, tb.c; +SELECT * FROM tbl_copy; + a | b | c | e +---+---+---+--- + 4 | 5 | 6 | 8 +(1 row) + +-- INSERT INTO ... SELECT with star options +INSERT INTO tbl_copy SELECT * (EXCLUDE (ta.c, tb.d)) +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +ORDER BY ta.a, tb.c; +SELECT * FROM tbl_copy; + a | b | c | e +---+---+---+--- + 4 | 5 | 6 | 8 + 4 | 5 | 6 | 8 +(2 rows) + +-- RETURNING with star options +INSERT INTO tbl_copy SELECT * (EXCLUDE (b)) +FROM tbl_a +RETURNING * (EXCLUDE (c)); + a | b | e +---+---+--- + 1 | 3 | + 4 | 6 | +(2 rows) + +INSERT INTO tbl_copy SELECT * (EXCLUDE (ta.c, tb.d)) +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +RETURNING *; + a | b | c | e +---+---+---+--- + 4 | 5 | 6 | 8 +(1 row) + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING * (EXCLUDE (status)); + id | user_id | amount +-----+---------+-------- + 101 | 1 | 60.00 +(1 row) + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.*; + id | user_id | amount | status | id | user_id | amount | status +-----+---------+--------+--------+-----+---------+--------+-------- + 101 | 1 | 60.00 | paid | 101 | 1 | 70.00 | paid +(1 row) + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.* (EXCLUDE (status)); + id | user_id | amount | status | id | user_id | amount +-----+---------+--------+--------+-----+---------+-------- + 101 | 1 | 70.00 | paid | 101 | 1 | 80.00 +(1 row) + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.* (EXCLUDE (user_id, status)), new.* (EXCLUDE (status)); + id | amount | id | user_id | amount +-----+--------+-----+---------+-------- + 101 | 80.00 | 101 | 1 | 90.00 +(1 row) + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.* (EXCLUDE (old.status)); -- error +ERROR: column old.status does not exist +LINE 1: ...0 WHERE id = 101 RETURNING old.*, new.* (EXCLUDE (old.status... + ^ +-- Error cases +-- Non-existent columns (error case) +SELECT * (EXCLUDE (does_not_exist)) FROM users; +ERROR: column "does_not_exist" does not exist +LINE 1: SELECT * (EXCLUDE (does_not_exist)) FROM users; + ^ +SELECT * (REPLACE (does_not_exist WITH '[email protected]')) FROM users; +ERROR: column "does_not_exist" does not exist +LINE 1: SELECT * (REPLACE (does_not_exist WITH '[email protected]')) ... + ^ +SELECT * (RENAME (does_not_exist AS user_email)) FROM users; +ERROR: column "does_not_exist" does not exist +LINE 1: SELECT * (RENAME (does_not_exist AS user_email)) FROM users; + ^ +-- Empty lists (error case) +SELECT * (EXCLUDE ()) FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * (EXCLUDE ()) FROM users; + ^ +SELECT * (REPLACE ()) FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * (REPLACE ()) FROM users; + ^ +SELECT * (RENAME ()) FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * (RENAME ()) FROM users; + ^ +-- duplicate column names (error case) +SELECT * (EXCLUDE (id, id)) FROM users; +ERROR: duplicate column "id" in EXCLUDE list +SELECT * (REPLACE (email WITH '[email protected]', email WITH NULL)) FROM users; +ERROR: duplicate column "email" in REPLACE list +SELECT * (RENAME (email AS user_email, email AS user_name)) FROM users; +ERROR: duplicate column "email" in RENAME list +SELECT * () +FROM users; +ERROR: syntax error at or near ")" +LINE 1: SELECT * () + ^ +-- EXCLUDE + REPLACE same column (error case) +SELECT * (EXCLUDE (id) REPLACE (id WITH 999)) +FROM users; +ERROR: column "id" cannot occur in both EXCLUDE and REPLACE list +-- EXCLUDE + RENAME same column (error case) +SELECT * (EXCLUDE (id) RENAME (id AS user_id)) +FROM users; +ERROR: column "id" cannot occur in both EXCLUDE and RENAME list +-- Cannot use star inside options list (error case) +SELECT * (EXCLUDE (users.*)) +FROM users +ORDER BY id; +ERROR: syntax error at or near ")" +LINE 1: SELECT * (EXCLUDE (users.*)) + ^ +-- Incorrect use of table alias (error case) +SELECT u.* (EXCLUDE (users.email)) FROM users u; +ERROR: invalid reference to FROM-clause entry for table "users" +LINE 1: SELECT u.* (EXCLUDE (users.email)) FROM users u; + ^ +HINT: Perhaps you meant to reference the table alias "u". +-- Test dropping a column +ALTER TABLE users DROP COLUMN email; +SELECT * (EXCLUDE (users.email)) FROM users; -- error, as email column no longer exists +ERROR: column users.email does not exist +LINE 1: SELECT * (EXCLUDE (users.email)) FROM users; + ^ +SELECT * (EXCLUDE (name)) FROM users; + id | created_at +----+-------------------------- + 1 | Thu Jan 01 10:00:00 2026 + 2 | Fri Jan 02 11:00:00 2026 + 3 | Sat Jan 03 12:00:00 2026 +(3 rows) + +-- Apply star options to a column from a different table in a multi-table query (error case) +SELECT +users.* (EXCLUDE (orders.amount)) +FROM users, orders; +ERROR: column orders.amount does not exist +LINE 2: users.* (EXCLUDE (orders.amount)) + ^ +DETAIL: There is a column named "amount" in table "orders", but it cannot be referenced from this part of the query. +-- Check privilege handling with star options +CREATE USER regress_staroptions_user; +GRANT SELECT (name) ON TABLE users TO regress_staroptions_user; +SET ROLE regress_staroptions_user; +SELECT * (EXCLUDE (id, created_at)) FROM users; -- should succeed, as id and created_at are excluded + name +------- + Alice + Bob + Carol +(3 rows) + +SELECT * (REPLACE (created_at WITH NULL)) FROM users; -- should fail, as created_at is not accessible +ERROR: permission denied for table users +SELECT * (RENAME (created_at AS date_created)) FROM users; -- should fail, as created_at is not accessible +ERROR: permission denied for table users +RESET ROLE; +-- clean up +DROP TABLE orders; +DROP TABLE users; +DROP TABLE tbl_a; +DROP TABLE tbl_b; +DROP TABLE tbl_copy; +DROP TABLE s1.products; +DROP TABLE s2.products; +DROP SCHEMA s1; +DROP SCHEMA s2; +DROP USER regress_staroptions_user; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 75063f87a4a..5fde04091f0 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 dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass stats_rewrite +test: select_views select_staroptions portals_p2 foreign_key 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_staroptions.sql b/src/test/regress/sql/select_staroptions.sql new file mode 100644 index 00000000000..a95aa0b220d --- /dev/null +++ b/src/test/regress/sql/select_staroptions.sql @@ -0,0 +1,547 @@ +-- +-- SELECT EXCLUDE/REPLACE/RENAME +-- + +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, + name TEXT, + email TEXT, + created_at TIMESTAMP +); + +CREATE TABLE orders ( + id INT PRIMARY KEY, + user_id INT REFERENCES users(id), + amount NUMERIC(10,2), + status TEXT +); + +CREATE SCHEMA s1; +CREATE SCHEMA s2; + +CREATE TABLE s1.products ( + id INT PRIMARY KEY, + name TEXT, + price NUMERIC(10,2) +); + +CREATE TABLE s2.products ( + id INT PRIMARY KEY, + name TEXT, + price NUMERIC(10,2), + description TEXT +); + +-- Insert sample data +INSERT INTO users (id, name, email, created_at) VALUES +(1, 'Alice', '[email protected]', '2026-01-01 10:00:00'), +(2, 'Bob', '[email protected]', '2026-01-02 11:00:00'), +(3, 'Carol', NULL, '2026-01-03 12:00:00'); + +INSERT INTO orders (id, user_id, amount, status) VALUES +(101, 1, 50.00, 'paid'), +(102, 1, 75.50, 'shipped'), +(103, 2, 20.00, 'cancelled'); + +INSERT INTO s1.products (id, name, price) VALUES +(1, 'Product A', 10.00), +(2, 'Product B', 15.50); + +INSERT INTO s2.products (id, name, price, description) VALUES +(1, 'Product X', 20.00, 'Description for X'), +(2, 'Product Y', 25.75, 'Description for Y'); + +-- +-- EXCLUDE clause +-- +-- Simple EXCLUDE +SELECT * (EXCLUDE (email, created_at)) +FROM users +ORDER BY id; + +-- Exclude all but one column +SELECT * (EXCLUDE (name, email, created_at)) +FROM users +ORDER BY id; + +-- Exclude all columns +SELECT * (EXCLUDE (id, name, email, created_at)) +FROM users; + +-- EXCLUDE all using exclude list but overall SELECT list is not empty +SELECT id, users.* (EXCLUDE (id, name, email, created_at)) +FROM users +ORDER BY id; + +-- Aliasing with EXCLUDE +SELECT * (EXCLUDE (u.email)) +FROM users AS u +ORDER BY u.id; + +-- Expressions with EXCLUDE +SELECT * (EXCLUDE (name)), 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 +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; EXCLUDE is applied to each star separately +SELECT +users.* (EXCLUDE (id)), +orders.* (EXCLUDE (user_id)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Schema-qualified star +SELECT s1.products.* (EXCLUDE (price)) +FROM s1.products +ORDER BY id; + +-- Schema-qualified column +SELECT * (EXCLUDE (s1.products.price)) +FROM s1.products +ORDER BY 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; + +SELECT * (EXCLUDE (j.created_at)) +FROM ( + users JOIN orders USING (id) +) AS j; + +-- Nested subqueries with EXCLUDE +SELECT * (EXCLUDE (email, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM users); +SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM users); -- error, cannot exclude id again in outer query + +-- CTEs with EXCLUDE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (EXCLUDE (base_users.created_at)) +FROM base_users +ORDER BY id; + +WITH CTE AS NOT MATERIALIZED (SELECT * (EXCLUDE (id, created_at)) FROM users) +SELECT * (EXCLUDE (id)) FROM CTE; -- error, cannot exclude id again in outer query + +-- Multiple FROM items with EXCLUDE +SELECT * (EXCLUDE (email, status)) FROM users, orders ORDER BY users.id, orders.id; + +-- CROSS JOIN with EXCLUDE +SELECT * (EXCLUDE (email, status)) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + +-- +-- REPLACE clause +-- +-- Simple REPLACE +SELECT * (REPLACE (email WITH '[email protected]', created_at WITH NULL)) +FROM users +ORDER BY id; + +-- Aliasing with REPLACE +SELECT * (REPLACE (u.email WITH '[email protected]')) +FROM users AS u +ORDER BY u.id; + +-- Expressions with REPLACE +SELECT * (REPLACE (created_at WITH NULL)), 1 + 1 AS two +FROM users +ORDER BY id; + +-- JOINs with REPLACE +-- Join, unqualified REPLACE +SELECT * (REPLACE (created_at WITH created_at + INTERVAL '1 day')) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, qualified REPLACE +SELECT * (REPLACE (users.created_at WITH NULL, orders.amount WITH orders.amount * 2)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, aliased tables with REPLACE +SELECT * (REPLACE (u.created_at WITH NULL, o.amount WITH o.amount * 2)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + +-- Qualified stars; REPLACE is applied to each star separately +SELECT +users.* (REPLACE (id WITH 999)), +orders.* (REPLACE (user_id WITH NULL)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Schema-qualified star +SELECT s1.products.* (REPLACE (price WITH price * 1.1)) +FROM s1.products +ORDER BY id; + +-- Schema-qualified column +SELECT * (REPLACE (s1.products.price WITH s1.products.price * 1.1)) +FROM s1.products +ORDER BY id; + +-- Name collision +SELECT * (REPLACE (id WITH 999)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Subqueries with REPLACE +SELECT * (REPLACE (u.created_at WITH u.created_at + INTERVAL '1 day')) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + +SELECT * (REPLACE (j.created_at WITH j.created_at + INTERVAL '1 day')) +FROM ( + users JOIN orders USING (id) +) AS j; + +-- Nested subqueries with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', created_at WITH NULL)) FROM (SELECT * (REPLACE (id WITH 999)) FROM users); +SELECT * (REPLACE (id WITH 0, created_at WITH NULL)) FROM (SELECT * (REPLACE (id WITH 999)) FROM users); + +-- CTEs with REPLACE +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (REPLACE (base_users.created_at WITH NULL)) +FROM base_users +ORDER BY id; + +WITH CTE AS NOT MATERIALIZED (SELECT * (REPLACE (id WITH 999, created_at WITH NULL)) FROM users) +SELECT * (REPLACE (id WITH 0)) FROM CTE; + +-- Multiple FROM items with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', status WITH 'N/A')) FROM users, orders ORDER BY users.id, orders.id; + +-- CROSS JOIN with REPLACE +SELECT * (REPLACE (email WITH '[email protected]', status WITH 'N/A')) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + +-- +-- RENAME clause +-- +-- Simple RENAME +SELECT * (RENAME (name AS user_name, email AS user_email)) +FROM users +ORDER BY id; + +-- Aliasing with RENAME +SELECT * (RENAME (u.email AS user_email)) +FROM users AS u +ORDER BY u.id; + +-- Expressions with RENAME +SELECT * (RENAME (created_at AS date_created)), 1 + 1 AS two +FROM users +ORDER BY id; + +-- JOINs with RENAME +-- Join, unqualified RENAME +SELECT * (RENAME (created_at AS date_created)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, qualified RENAME +SELECT * (RENAME (users.created_at AS date_created, orders.amount AS order_amount)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Join, aliased tables with RENAME +SELECT * (RENAME (u.created_at AS date_created, o.amount AS order_amount)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + +-- Qualified stars; RENAME is applied to each star separately +SELECT +users.* (RENAME (id AS uid)), +orders.* (RENAME (user_id AS customer_id)) +FROM users +LEFT JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Schema-qualified star +SELECT s1.products.* (RENAME (price AS product_price)) +FROM s1.products +ORDER BY id; + +-- Schema-qualified column +SELECT * (RENAME (s1.products.price AS product_price)) +FROM s1.products +ORDER BY id; + +-- Name collision +SELECT * (RENAME (id AS _id)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Subqueries with RENAME +SELECT * (RENAME (u.created_at AS date_created)) +FROM ( + SELECT * FROM users +) u +ORDER BY id; + +SELECT * (RENAME (j.created_at AS date_created)) +FROM ( + users JOIN orders USING (id) +) AS j; + +-- Nested subqueries with RENAME +SELECT * (RENAME (email AS user_email, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users); +SELECT * (RENAME (uid AS new_uid, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users); +SELECT * (RENAME (id AS new_uid, created_at AS date_created)) FROM (SELECT * (RENAME (id AS uid)) FROM users) AS u; -- error, "id" was already renamed in inner query + +-- CTEs with RENAME +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (RENAME (base_users.created_at AS date_created)) +FROM base_users +ORDER BY id; + +WITH CTE AS NOT MATERIALIZED (SELECT * (RENAME (id AS uid, created_at AS date_created)) FROM users) +SELECT * (RENAME (id AS new_id)) FROM CTE; -- error, "id" was already renamed in inner query + +-- Multiple FROM items with RENAME +SELECT * (RENAME (email AS user_email, status AS order_status)) FROM users, orders ORDER BY users.id, orders.id; + +-- CROSS JOIN with RENAME +SELECT * (RENAME (email AS user_email, status AS order_status)) FROM users CROSS JOIN orders ORDER BY users.id, orders.id; + +-- +-- EXCLUDE/REPLACE/RENAME combinations +-- +-- EXCLUDE + REPLACE different columns +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]')) +FROM users; + +-- EXCLUDE + RENAME different columns +SELECT * (EXCLUDE (created_at) RENAME (email AS user_email)) +FROM users; + +-- REPLACE + RENAME on same column +SELECT * (REPLACE (email WITH '[email protected]', name WITH 'Unknown') RENAME (email AS user_email, name AS user_name)) +FROM users +ORDER BY id; + +-- REPLACE + RENAME on same column with EXCLUDE +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (email AS user_email)) +FROM users; + +-- All three clauses on different columns +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (name AS user_name)) +FROM users +ORDER BY id; + +-- JOIN, unqualified +SELECT * (EXCLUDE (created_at) REPLACE (email WITH '[email protected]') RENAME (amount AS order_amount)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- JOIN, qualified + aliased tables +SELECT * (EXCLUDE (u.created_at) REPLACE (u.email WITH '[email protected]') RENAME (o.amount AS order_amount)) +FROM users AS u +JOIN orders AS o ON o.user_id = u.id +ORDER BY u.id, o.id; + +-- Qualified stars with all three +SELECT +users.* (EXCLUDE (email) REPLACE (name WITH 'Unknown') RENAME (id AS uid)), +orders.* (EXCLUDE (status) REPLACE (amount WITH amount * 2) RENAME (id AS oid)) +FROM users +JOIN orders ON orders.user_id = users.id +ORDER BY users.id, orders.id; + +-- Schema-qualified star +SELECT s1.products.* (EXCLUDE (price) REPLACE (name WITH 'Product X') RENAME (id AS product_id)) +FROM s1.products +ORDER BY id; + +-- Schema-qualified column +SELECT * (EXCLUDE (s1.products.price) REPLACE (s1.products.name WITH 'Product X') RENAME (s1.products.id AS product_id)) +FROM s1.products +ORDER BY id; + +-- Subquery with all three +SELECT * (EXCLUDE (u.created_at) REPLACE (u.email WITH '[email protected]') RENAME (u.name AS user_name)) +FROM ( + SELECT * FROM users +) AS u +ORDER BY id; + +-- CTE with all three +WITH base_users AS ( + SELECT * FROM users +) +SELECT * (EXCLUDE (base_users.created_at) REPLACE (base_users.email WITH '[email protected]') RENAME (base_users.name AS user_name)) +FROM base_users +ORDER BY id; + +-- CROSS JOIN with all three +SELECT * (EXCLUDE (status) REPLACE (email WITH '[email protected]') RENAME (users.id AS uid, orders.id AS oid)) FROM users, orders ORDER BY users.id, orders.id; + +-- Test 3-part qualification with JOIN on different schema tables +SELECT * (EXCLUDE (s1.products.name) REPLACE (s2.products.name WITH 'Z') RENAME (s1.products.id AS p1_id, s2.products.id AS p2_id)) +FROM s1.products +JOIN s2.products ON s1.products.id = s2.products.id +ORDER BY s1.products.id; + +-- Test 4-part qualification +SELECT * (EXCLUDE (regression.public.users.email)) +FROM users +ORDER BY id; + +-- SELECT INTO with star options +CREATE TABLE tbl_a (a int, b int, c int); +CREATE TABLE tbl_b (c int, d int, e int); +INSERT INTO tbl_a VALUES (1, 2, 3), (4, 5, 6); +INSERT INTO tbl_b VALUES (6, 7, 8), (9, 10, 11); + +SELECT * (EXCLUDE (b, e)) INTO tbl_copy +FROM tbl_a +JOIN tbl_b ON tbl_a.c = tbl_b.c +ORDER BY tbl_a.a, tbl_b.c; -- error, as c is in both tables + +SELECT * (EXCLUDE (ta.c, tb.d)) INTO tbl_copy +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +ORDER BY ta.a, tb.c; + +SELECT * FROM tbl_copy; + +-- INSERT INTO ... SELECT with star options +INSERT INTO tbl_copy SELECT * (EXCLUDE (ta.c, tb.d)) +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +ORDER BY ta.a, tb.c; + +SELECT * FROM tbl_copy; + +-- RETURNING with star options +INSERT INTO tbl_copy SELECT * (EXCLUDE (b)) +FROM tbl_a +RETURNING * (EXCLUDE (c)); + +INSERT INTO tbl_copy SELECT * (EXCLUDE (ta.c, tb.d)) +FROM tbl_a AS ta +JOIN tbl_b AS tb ON ta.c = tb.c +RETURNING *; + +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING * (EXCLUDE (status)); +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.*; +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.* (EXCLUDE (status)); +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.* (EXCLUDE (user_id, status)), new.* (EXCLUDE (status)); +UPDATE orders SET amount = amount + 10 WHERE id = 101 RETURNING old.*, new.* (EXCLUDE (old.status)); -- error + +-- Error cases +-- Non-existent columns (error case) +SELECT * (EXCLUDE (does_not_exist)) FROM users; +SELECT * (REPLACE (does_not_exist WITH '[email protected]')) FROM users; +SELECT * (RENAME (does_not_exist AS user_email)) FROM users; + +-- Empty lists (error case) +SELECT * (EXCLUDE ()) FROM users; +SELECT * (REPLACE ()) FROM users; +SELECT * (RENAME ()) FROM users; + +-- duplicate column names (error case) +SELECT * (EXCLUDE (id, id)) FROM users; +SELECT * (REPLACE (email WITH '[email protected]', email WITH NULL)) FROM users; +SELECT * (RENAME (email AS user_email, email AS user_name)) FROM users; +SELECT * () +FROM users; + +-- EXCLUDE + REPLACE same column (error case) +SELECT * (EXCLUDE (id) REPLACE (id WITH 999)) +FROM users; + +-- EXCLUDE + RENAME same column (error case) +SELECT * (EXCLUDE (id) RENAME (id AS user_id)) +FROM users; + +-- Cannot use star inside options list (error case) +SELECT * (EXCLUDE (users.*)) +FROM users +ORDER BY id; + +-- Incorrect use of table alias (error case) +SELECT u.* (EXCLUDE (users.email)) FROM users u; + +-- Test dropping a column +ALTER TABLE users DROP COLUMN email; +SELECT * (EXCLUDE (users.email)) FROM users; -- error, as email column no longer exists +SELECT * (EXCLUDE (name)) FROM users; + +-- Apply star options to a column from a different table in a multi-table query (error case) +SELECT +users.* (EXCLUDE (orders.amount)) +FROM users, orders; + +-- Check privilege handling with star options +CREATE USER regress_staroptions_user; +GRANT SELECT (name) ON TABLE users TO regress_staroptions_user; +SET ROLE regress_staroptions_user; +SELECT * (EXCLUDE (id, created_at)) FROM users; -- should succeed, as id and created_at are excluded +SELECT * (REPLACE (created_at WITH NULL)) FROM users; -- should fail, as created_at is not accessible +SELECT * (RENAME (created_at AS date_created)) FROM users; -- should fail, as created_at is not accessible +RESET ROLE; + +-- clean up +DROP TABLE orders; +DROP TABLE users; +DROP TABLE tbl_a; +DROP TABLE tbl_b; +DROP TABLE tbl_copy; +DROP TABLE s1.products; +DROP TABLE s2.products; +DROP SCHEMA s1; +DROP SCHEMA s2; +DROP USER regress_staroptions_user; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index cfef572df80..b47529833bc 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2980,6 +2980,10 @@ SplitTextOutputData SplitVar StackElem StakindFlags +StarExcludeItem +StarOptions +StarRenameItem +StarReplaceItem StartBufferIOResult StartDataPtrType StartLOPtrType -- 2.43.0
