Hello,
PFA v3 of the patch.
A few notes about it:
1. This version follows the new standard syntax as mentioned in Peter's
blog along with REPLACE and RENAME clauses.
2. You might see 'star options' in code and documentation, well
that is what I am calling them collectively. This is of course open
to discussion.
3. A column specified in EXCLUDE cannot also be specified in REPLACE
or RENAME. A column may, however, be specified in both
REPLACE and RENAME.
4. The columns in the REPLACE and RENAME clauses are marked
for SELECT privilege checks, whereas the columns in the
EXCLUDE clause are not. (needs discussion)
5. No ambiguity error is raised, and operation is applied to all matching
columns in case of unqualified column names. (needs discussion)
6. Regression tests are expanded and now are now includes:
- Tests of independent clauses
- Tests of clauses in combinations
- SELECT INTO with star options
- INSERT INTO ... SELECT with star options
- RETURNING with star options is rejected, as it made no sense there.
- Error cases
7. The case: select (t2).* exclude (bar)
It comes as A_Indirection node, maybe we can reject these in gram.y
in the check_indirection function. I have not touched it yet.
Other bugs reported by Kacper are fixed in this version, and also
a few similar cases are added to regression tests as well.
I believe there is still some stuff to refactor but just wanted to show
how things are going, and gather feedback from the community.
Thanks!
Regards,
Hunaid Sohail
From f63fdab9c1c6493adf7c0b635eb36513aefcc902 Mon Sep 17 00:00:00 2001
From: Hunaid2000 <[email protected]>
Date: Fri, 9 Oct 2026 18:43:24 +0500
Subject: [PATCH v3] Implement SELECT * EXCLUDE/REPLACE/RENAME clauses
---
doc/src/sgml/ref/select.sgml | 147 ++-
src/backend/parser/analyze.c | 2 +-
src/backend/parser/gram.y | 159 ++-
src/backend/parser/parse_relation.c | 253 ++++-
src/backend/parser/parse_target.c | 104 +-
src/include/nodes/parsenodes.h | 40 +
src/include/parser/parse_relation.h | 2 +-
.../regress/expected/select_staroptions.out | 965 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/select_staroptions.sql | 519 ++++++++++
src/tools/pgindent/typedefs.list | 4 +
11 files changed, 2176 insertions(+), 21 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..8ef187dddfe 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -455,6 +455,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> exclude_item replace_item rename_item opt_star_options star_options
%type <retclause> returning_clause
%type <node> returning_option
@@ -12654,6 +12657,27 @@ returning_clause:
RETURNING returning_with_clause target_list
{
ReturningClause *n = makeNode(ReturningClause);
+ ListCell *lc;
+
+ /* Check for RETURNING with star options, which is not supported */
+ foreach(lc, $3)
+ {
+ ResTarget *res = (ResTarget *) lfirst(lc);
+ if (IsA(res->val, ColumnRef))
+ {
+ ColumnRef *cref = (ColumnRef *) res->val;
+
+ if (IsA(llast(cref->fields), A_Star))
+ {
+ A_Star *star = (A_Star *) llast(cref->fields);
+ if (star->staroptions != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING with star options is not supported"),
+ parser_errposition(cref->location)));
+ }
+ }
+ }
n->options = $2;
n->exprs = $3;
@@ -17331,6 +17355,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 +17690,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 +17707,128 @@ target_el: a_expr AS ColLabel
}
;
+star_options:
+ '(' EXCLUDE '(' exclude_list ')' opt_replace opt_rename ')'
+ {
+ StarOptions *n = makeNode(StarOptions);
+
+ n->exclude_list = $4;
+ n->replace_list = $6;
+ n->rename_list = $7;
+
+ $$ = (Node *) n;
+ }
+ | '(' REPLACE '(' replace_list ')' opt_rename ')'
+ {
+ StarOptions *n = makeNode(StarOptions);
+
+ n->exclude_list = NIL;
+ n->replace_list = $4;
+ n->rename_list = $6;
+
+ $$ = (Node *) n;
+ }
+ | '(' RENAME '(' rename_list ')' ')'
+ {
+ StarOptions *n = makeNode(StarOptions);
+
+ n->exclude_list = NIL;
+ n->replace_list = NIL;
+ n->rename_list = $4;
+
+ $$ = (Node *) n;
+ }
+ ;
+
+opt_star_options:
+ star_options { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+exclude_list:
+ exclude_item
+ { $$ = list_make1($1); }
+ | exclude_list ',' exclude_item
+ { $$ = lappend($1, $3); }
+ ;
+
+exclude_item:
+ columnref
+ {
+ StarExcludeItem *n = makeNode(StarExcludeItem);
+ ColumnRef *c = (ColumnRef *) $1;
+ c->staroptcol_match = false;
+
+ check_qualified_name(c->fields, yyscanner);
+
+ n->name = c;
+ n->location = @1;
+
+ $$ = (Node *) n;
+ }
+ ;
+
+replace_list:
+ replace_item
+ { $$ = list_make1($1); }
+ | replace_list ',' replace_item
+ { $$ = lappend($1, $3); }
+ ;
+
+replace_item:
+ columnref WITH a_expr
+ {
+ StarReplaceItem *n = makeNode(StarReplaceItem);
+ ColumnRef *c = (ColumnRef *) $1;
+ c->staroptcol_match = false;
+
+ check_qualified_name(c->fields, yyscanner);
+
+ n->name = c;
+ n->expr = (Node *) $3;
+ n->location = @1;
+
+ $$ = (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:
+ columnref AS ColId
+ {
+ StarRenameItem *n = makeNode(StarRenameItem);
+ ColumnRef *c = (ColumnRef *) $1;
+ c->staroptcol_match = false;
+
+ check_qualified_name(c->fields, yyscanner);
+
+ n->name = c;
+ n->aliasname = $3;
+ n->location = @1;
+
+ $$ = (Node *) n;
+ }
+ ;
+
+opt_rename:
+ RENAME '(' rename_list ')'
+ { $$ = $3; }
+ | /* EMPTY */
+ { $$ = NIL; }
+ ;
/*****************************************************************************
*
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 8aeb3e219ac..7af152d1e9b 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,239 @@ 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 */
+ colname = strVal(linitial(cref->fields));
+ break;
+ 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)));
+ }
+
+ /*
+ * Match the column name first.
+ */
+ if (strcmp(colname, label) != 0)
+ return false;
+
+ /*
+ * Unqualified column name matches regardless of which RTE it came from.
+ */
+ if (relname == NULL)
+ return true;
+
+ /* 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.
+ */
+ return refnsitem->p_rtindex == varnode->varnosyn;
+}
+
+/*
+ * 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 +3545,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 +3576,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..01cbf261f98 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -44,14 +44,14 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
Node *rhs,
CoercionContext ccontext,
int location);
-static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
+static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, StarOptions *staroptions,
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,80 @@ transformTargetEntry(ParseState *pstate,
resjunk);
}
+/*
+ * check_star_options()
+ * Check that all columns mentioned in the star options 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
+check_star_options(ParseState *pstate, StarOptions *staroptions)
+{
+ List *exclude_list = staroptions->exclude_list;
+ List *replace_list = staroptions->replace_list;
+ List *rename_list = staroptions->rename_list;
+
+ if (exclude_list)
+ {
+ ListCell *elc;
+
+ foreach(elc, exclude_list)
+ {
+ StarExcludeItem *item = (StarExcludeItem *) lfirst(elc);
+ ColumnRef *cref = item->name;
+ char *relname = NULL;
+ char *colname = NULL;
+
+ 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);
+ }
+ }
+ if (replace_list)
+ {
+ ListCell *rlc;
+
+ foreach(rlc, replace_list)
+ {
+ StarReplaceItem *item = (StarReplaceItem *) lfirst(rlc);
+ ColumnRef *cref = item->name;
+ char *relname = NULL;
+ char *colname = NULL;
+
+ 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);
+ }
+ }
+ if (rename_list)
+ {
+ ListCell *rlc;
+
+ foreach(rlc, rename_list)
+ {
+ StarRenameItem *item = (StarRenameItem *) lfirst(rlc);
+ ColumnRef *cref = item->name;
+ char *relname = NULL;
+ char *colname = NULL;
+
+ 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);
+ }
+ }
+}
+
/*
* transformTargetList()
@@ -147,11 +221,16 @@ 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,
+ star->staroptions,
true));
+ if (star->staroptions)
+ check_star_options(pstate, star->staroptions);
continue;
}
}
@@ -239,7 +318,7 @@ transformExpressionList(ParseState *pstate, List *exprlist,
{
/* It is something.*, expand into multiple items */
result = list_concat(result,
- ExpandColumnRefStar(pstate, cref,
+ ExpandColumnRefStar(pstate, cref, NULL,
false));
continue;
}
@@ -1121,7 +1200,7 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos)
* The referenced columns are marked as requiring SELECT access.
*/
static List *
-ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
+ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, StarOptions *staroptions,
bool make_target_entry)
{
List *fields = cref->fields;
@@ -1138,7 +1217,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 +1357,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 +1373,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 +1396,8 @@ ExpandAllTables(ParseState *pstate, int location)
nsitem,
0,
true,
- location));
+ location,
+ staroptions));
}
/*
@@ -1374,12 +1454,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 +1528,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..44e8e285e33 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -310,9 +310,48 @@ 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 */
+ ParseLoc location; /* token location, or -1 if unknown */
+} StarExcludeItem;
+
+typedef struct StarReplaceItem
+{
+ NodeTag type;
+ ColumnRef *name; /* possibly qualified column name */
+ Node *expr; /* expression to replace the column with */
+ ParseLoc location; /* token location, or -1 if unknown */
+} StarReplaceItem;
+
+typedef struct StarRenameItem
+{
+ NodeTag type;
+ ColumnRef *name; /* possibly qualified column name */
+ char *aliasname; /* new output name for the column */
+ ParseLoc location; /* token location, or -1 if unknown */
+} 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 +511,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..6617d144002
--- /dev/null
+++ b/src/test/regress/expected/select_staroptions.out
@@ -0,0 +1,965 @@
+--
+-- 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)
+
+-- 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)
+
+-- 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)
+
+-- 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)
+
+-- 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)
+
+-- 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)
+
+-- 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)); -- error, cannot use star options with RETURNING clause
+ERROR: RETURNING with star options is not supported
+LINE 3: 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 *;
+ a | b | c | e
+---+---+---+---
+ 4 | 5 | 6 | 8
+(1 row)
+
+-- 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 tuser;
+GRANT SELECT (name) ON TABLE users TO tuser;
+SET ROLE tuser;
+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 tuser;
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..d24f91ad4c0
--- /dev/null
+++ b/src/test/regress/sql/select_staroptions.sql
@@ -0,0 +1,519 @@
+--
+-- 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;
+
+-- CTEs with EXCLUDE
+WITH base_users AS (
+ SELECT * FROM users
+)
+SELECT * (EXCLUDE (base_users.created_at))
+FROM base_users
+ORDER BY id;
+
+-- 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;
+
+-- CTEs with REPLACE
+WITH base_users AS (
+ SELECT * FROM users
+)
+SELECT * (REPLACE (base_users.created_at WITH NULL))
+FROM base_users
+ORDER BY id;
+
+-- 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;
+
+-- 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;
+
+-- 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)); -- error, cannot use star options with RETURNING clause
+
+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 *;
+
+-- 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 tuser;
+GRANT SELECT (name) ON TABLE users TO tuser;
+SET ROLE tuser;
+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 tuser;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3bed561f7f2..26db272a181 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2978,6 +2978,10 @@ SplitTextOutputData
SplitVar
StackElem
StakindFlags
+StarExcludeItem
+StarOptions
+StarRenameItem
+StarReplaceItem
StartBufferIOResult
StartDataPtrType
StartLOPtrType
--
2.43.0
