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

From: Andrew Dunstan <[email-masked]>
Date: Mon, 30 Mar 2026 14:52:54 -0400

On 2026-03-30 Mo 11:49 AM, Álvaro Herrera wrote:
> On 2026-Mar-30, Andrew Dunstan wrote:
>
>> Well, Suraj has kinda beaten me to it, but he didn't invent this syntax.
>> Oracle did <https://oracle-base.com/articles/23/non-positional-insert-into-set-and-insert-into-by-name-clauses-23>
>> and I believe there is a proposal to add it to the standard. (Unlike
>> Suraj's, my WIP patch also supports the INSERT BY NAME variant.)
> Hmm, I don't see any WIP patch from you

No, I haven't submitted it, still working on it. Given Suraj's work, I 
will probably just submit a patch for INSERT BY NAME now.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

---

From: Ajay Pal <[email-masked]>
Date: Tue, 31 Mar 2026 11:44:12 +0530

Hello all,

I am reporting a server crash encountered while testing the patch
provided by Suraj. The crash is consistently triggered by the query
attached below.

postgres=# INSERT INTO emp_test SET (empno,ename)=(SELECT 1,'aa');

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Thanks
Ajay

On Mon, Mar 30, 2026 at 9:19 PM Álvaro Herrera <[email-masked]> wrote:
> Hmm, I don't see any WIP patch from you -- are you talking about this
> patch from July 2019?
> https://postgr.es/m/CA+A-St+NntBh2EGu3a0xbVxJFzaeEOn=[email-masked]
>
> Funnily enough, we have an even older proposal from 2016,
> https://postgr.es/m/[email-masked]
>
> It seems this is quite a popular missing feature, as Marko's patch was
> also asked about in February 2019:
> https://postgr.es/m/[email-masked]
>
> --
> Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
>
>

---

From: Suraj Kharage <[email-masked]>
Date: Wed, 1 Apr 2026 09:58:20 +0530

Thanks, Ajay for reporting this.

The issue is that the INSERT...SET grammar was incorrectly using
set_clause_list which includes the UPDATE-style
multi-column assignment syntax (col1, col2) = expr. This creates
MultiAssignRef nodes that are only valid in UPDATE contexts, not INSERT.
Fix this by changing the grammer rule.

Please find attached v2 patch with the above fix. I have also added support
for different column sets in multi-row inserts.

Thanks, Andrew for the offline discussion and help on this.
--

Thanks & Regards,
Suraj kharage,

enterprisedb.com <https://www.enterprisedb.com/>

On Tue, Mar 31, 2026 at 11:44 AM Ajay Pal <[email-masked]> wrote:

> > > Oracle did <
> https://oracle-base.com/articles/23/non-positional-insert-into-set-and-insert-into-by-name-clauses-23
> >

---

From: Suraj Kharage <[email-masked]>
Date: Mon, 6 Apr 2026 16:13:36 +0530

Hi,

Rebased the patch with some documentation changes.
Also, added commitfest entry - https://commitfest.postgresql.org/patch/6635/

--

Thanks & Regards,
Suraj kharage,

---

From: Triveni N <[email-masked]>
Date: Fri, 19 Jun 2026 19:05:03 +0530

From: Suraj Kharage <[email-masked]>
Date: Mon, Apr 6, 2026 at 4:13 PM
Subject: Re: [PATCH] Add support for INSERT ... SET syntax
To: Ajay Pal <[email-masked]>
Cc: Álvaro Herrera <[email-masked]>, Andrew Dunstan <
[email-masked]>, David G. Johnston <[email-masked]>,
PostgreSQL Hackers <[email-masked]>

Hi,

--

Thanks & Regards,
Suraj kharage,

Sharing an update on testing.

Below are the areas that I’ve covered:

**Core syntax**
- Basic SET syntax (single row, with and without parentheses)
- Column order independence
- Multi-row insertion — SET (col=val, ...), (col=val, ...) syntax

**Value types**
- DEFAULT keyword and implicit defaults (column omission)
- Expressions, functions, and subqueries (including deeply nested)
- NULL values, array columns, composite types, and domain types

**Clauses & advanced features**
- RETURNING clause (single and multi-row)
- ON CONFLICT DO NOTHING / DO UPDATE
- OVERRIDING SYSTEM VALUE
- CTE (WITH clause)
- EXPLAIN / EXPLAIN ANALYZE

**Schema & constraints**
- CHECK constraints and column-level privileges
- Foreign key constraints (valid and failing cases)
- Partitioned tables, table inheritance, and schema-qualified table names
- Quoted and reserved keyword column names

**Procedural contexts**
- PL/pgSQL functions and DO blocks (including multi-row)
- Dynamic SQL via EXECUTE with parameters
- Prepared statements (PREPARE + EXECUTE)
- SAVEPOINTs and rollback behavior

**Other**
- Updatable views
- BEFORE and AFTER INSERT triggers
- Negative cases covering syntax errors, constraint violations, and
privilege violations

Note: Issue reported by Ajay Pal has been resolved in v3 patch.

-- 
Warm regards,
Triveni

---

From: solai v <[email-masked]>
Date: Mon, 22 Jun 2026 10:44:09 +0530

Hi Suraj,

I tested v3 of the patch on current master.
The patch applied and built cleanly on my setup. Before applying it,
statements using the proposed INSERT...SET syntax resulted in a syntax
error near SET, which is expected since PostgreSQL does not currently
support this syntax. After applying the patch, the same statements
worked as expected and rows were inserted successfully.
I went through the functionality added by the patch and tested a few
different scenarios, including basic INSERT...SET usage, DEFAULT
values, expressions/functions, RETURNING, ON CONFLICT DO UPDATE,
multi-row inserts, different column sets in multi-row inserts, and
subqueries in assignments.
All of them behaved as expected in my testing.
I also verified the query that Ajay previously reported as causing a
backend crash:
INSERT INTO emp_test SET (empno,ename)=(SELECT 1,'aa');
With v3, I was not able to reproduce the crash. The query returned a
normal syntax error, and the server continued to run normally
afterward.
In addition, I ran the regression test suite and all tests passed successfully.
Overall, the patch worked well in my testing and I did not notice any
regressions.

Regards,
solai