Re: [PATCH] Add support for INSERT ... SET syntax

On Wed, 1 Jul 2026 at 15:27, Suraj Kharage <suraj(dot)kharage(at)enterprisedb(dot)com>
wrote:

> Hi,
>
> I would like to propose adding support for an alternative INSERT syntax
> that uses named column assignments via a SET clause. This provides a more
> convenient and readable way to write inserts, particularly when only
> specific columns need values.
>
> Currently, PostgreSQL requires INSERT statements to separate the column
> list from the values:
>
> INSERT INTO users (name, email, status) VALUES ('Alice', '
> alice(at)example(dot)com', 'active');
>
> For inserts with many columns or where only a subset of columns are
> specified, the proposed SET syntax offers better readability by keeping
> column names adjacent to their values:
>
> INSERT INTO users SET name='Alice', email='alice(at)example(dot)com',
> status='active';
>
> Proposed Syntax:
>
> INSERT INTO table_name
> SET (column1=value1, column2=value2, ...), (, ...)
> [ ON CONFLICT ... ]
> [ RETURNING ... ];
>
>
This is some feedback for -v3. I had some problems resending the other
conversations in the thread from the archives[1], that's why I'm replying
to this, sorry.

I like the idea. In spite it's not (yet) part of the SQL standard, it
wouldn't be odd if we see that happening in the near future.

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 2a2e00b372e..11cb4fcd2da 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4370,6 +4370,8 @@ raw_expression_tree_walker_impl(Node *node,
return true;
if (WALK(stmt->selectStmt))
return true;
+ if (WALK(stmt->setClauseList))
+ return true;
if (WALK(stmt->onConflictClause))

you used `stmt->setClauseList` however, I read the entire
`raw_expression_tree_walker_impl`
function and it seems we don't mix "clause" with "List" in the variable
names. Reading the whole file, I just found "targetList" and "valuesList".

If you get my point, maybe you could use "setClause" only? I know that
sounds like something that exists in setter/getters stuff. Like we're
setting a clause up but would it be worth looking for a new variable name?
I personally think so. Actually, after reading
`src/include/nodes/parsenodes.h`,
I think we should go for a change.

----
Also, in src/backend/parser/analyze.c we can change a lot of those foreach
by foreach_node, however, I need to ask, did you have a reason to not use
foreach_node() when you first wrote the code? Maybe I'm missing something.
Because this patch is on a commitfest already, I didn't want to send a
patch we might need to squash if I'm right afterwards. That's why I'd like
to show you what I did:
https://github.com/postgres/postgres/commit/7be0538f2a5d916f2fb4a39764985b358cf6d379
If you like I could send a v4- with the squashed version.

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 70c75d0bb20..d2f5b0edcc8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -679,31 +679,23 @@ transformInsertSetClause(ParseState *pstate, List
*setClauseList,
{
List *all_cols = NIL; /* List of all unique
column names */
List *valuesLists = NIL;
- ListCell *outer_lc;
- ListCell *lc;

/*
* First pass: collect all unique column names from all rows.
* We need to scan all rows first to determine the complete set of
columns.
* Also check for duplicate columns within each row.
*/
- foreach(outer_lc, setClauseList)
+ foreach_node(List, set_clause, setClauseList)
{
- List *set_clause = (List *) lfirst(outer_lc);
List *row_cols = NIL; /* Columns seen in
this row */
- ListCell *set_lc;
[...]
(more of that in the link to the diff in GH)

That's all for now.
Thanks!

[1]
https://www.postgresql.org/message-id/CAF1DzPXhKc15Toa06MTT7NYOAEq8Ucr=RYe5V7X2FkwGMmjD6w@mail.gmail.com

> Below INSERT features are supported:
> - DEFAULT keyword: SET col=DEFAULT
> - Expressions and functions: SET col=expr, col2=function(...)
> - Subqueries: SET col=(SELECT ...)
> - RETURNING clause
> - ON CONFLICT DO UPDATE/NOTHING
> - OVERRIDING SYSTEM VALUE
> - Multi-row syntax: SET (col1=val1, col2=val2), (col1=val3, col2=val4)
>
> Columns not mentioned receive their default values or NULL, consistent
> with standard INSERT behavior.
>
> I've attached the patch. Looking forward to your feedback.
> --
>
> Thanks & Regards,
> Suraj kharage,
>

--
Mario Gonzalez
EDB: https://www.enterprisedb.com