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

On Monday, March 30, 2026, Suraj Kharage <suraj.kharage@enterprisedb.com>
wrote:

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.

-1 for inventing our own full variant of insert command syntax.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Monday, March 30, 2026, Suraj Kharage <suraj.kharage@enterprisedb.com>
wrote:

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.

-1 for inventing our own full variant of insert command syntax.

This has been discussed before, no? I don't recall exactly why
we didn't adopt the earlier proposal(s), but some digging in the
mailing list archives should be fruitful.

regards, tom lane

On 2026-03-30 Mo 10:38 AM, David G. Johnston wrote:

On Monday, March 30, 2026, Suraj Kharage
<suraj.kharage@enterprisedb.com> wrote:

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.

-1 for inventing our own full variant of insert command syntax.

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&gt;
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.)

cheers

andrew

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

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&gt;
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 -- are you talking about this
patch from July 2019?
/messages/by-id/CA+A-St+NntBh2EGu3a0xbVxJFzaeEOn=Vn_V84OuhM59_HKarQ@mail.gmail.com

Funnily enough, we have an even older proposal from 2016,
/messages/by-id/709e06c0-59c9-ccec-d216-21e38cb5ed61@joh.to

It seems this is quite a popular missing feature, as Marko's patch was
also asked about in February 2019:
/messages/by-id/e58dd487-7ed4-3f95-c63c-24200ed768be@berkvens.net

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

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&gt;
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

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

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/&gt;

On Tue, Mar 31, 2026 at 11:44 AM Ajay Pal <ajay.pal.k@gmail.com> wrote:

Hi,

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

--

Thanks & Regards,
Suraj kharage,

enterprisedb.com <https://www.enterprisedb.com/&gt;

From: Suraj Kharage <suraj.kharage@enterprisedb.com>
Date: Mon, Apr 6, 2026 at 4:13 PM
Subject: Re: [PATCH] Add support for INSERT ... SET syntax
To: Ajay Pal <ajay.pal.k@gmail.com>
Cc: Álvaro Herrera <alvherre@kurilemu.de>, Andrew Dunstan <
andrew@dunslane.net>, David G. Johnston <david.g.johnston@gmail.com>,
PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Hi,

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

--

Thanks & Regards,
Suraj kharage,

enterprisedb.com <https://www.enterprisedb.com/&gt;
Hi,

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

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