Thread

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

   Suraj Kharage <suraj.kharage@enterprisedb.com> — 2026-03-30T11:04:27Z

   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@example.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@example.com',
   status='active';
   
   Proposed Syntax:
   
       INSERT INTO table_name
           SET (column1=value1, column2=value2, ...), (, ...)
           [ ON CONFLICT ... ]
           [ RETURNING ... ];
   
   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,
   
   
   
   enterprisedb.com <https://www.enterprisedb.com/>
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2026-03-30T14:38:27Z

   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.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-30T14:56:12Z

   "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
   
   
   
   

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

   Andrew Dunstan <andrew@dunslane.net> — 2026-03-30T15:06:39Z

   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> 
   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
   

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

   Álvaro Herrera <alvherre@kurilemu.de> — 2026-03-30T15:49:12Z

   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 -- are you talking about this
   patch from July 2019?
   https://postgr.es/m/CA+A-St+NntBh2EGu3a0xbVxJFzaeEOn=Vn_V84OuhM59_HKarQ@mail.gmail.com
   
   Funnily enough, we have an even older proposal from 2016,
   https://postgr.es/m/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:
   https://postgr.es/m/e58dd487-7ed4-3f95-c63c-24200ed768be@berkvens.net
   
   -- 
   Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
   
   
   
   

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

   Andrew Dunstan <andrew@dunslane.net> — 2026-03-30T18:52:54Z

   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
   
   
   
   
   

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

   Ajay Pal <ajay.pal.k@gmail.com> — 2026-03-31T06:14:12Z

   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 <alvherre@kurilemu.de> 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 -- are you talking about this
   > patch from July 2019?
   > https://postgr.es/m/CA+A-St+NntBh2EGu3a0xbVxJFzaeEOn=Vn_V84OuhM59_HKarQ@mail.gmail.com
   >
   > Funnily enough, we have an even older proposal from 2016,
   > https://postgr.es/m/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:
   > https://postgr.es/m/e58dd487-7ed4-3f95-c63c-24200ed768be@berkvens.net
   >
   > --
   > Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
   >
   >
   
   
   
   

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

   Suraj Kharage <suraj.kharage@enterprisedb.com> — 2026-04-01T04:28:20Z

   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 <ajay.pal.k@gmail.com> wrote:
   
   > 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 <alvherre@kurilemu.de>
   > 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 -- are you talking about this
   > > patch from July 2019?
   > >
   > https://postgr.es/m/CA+A-St+NntBh2EGu3a0xbVxJFzaeEOn=Vn_V84OuhM59_HKarQ@mail.gmail.com
   > >
   > > Funnily enough, we have an even older proposal from 2016,
   > > https://postgr.es/m/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:
   > > https://postgr.es/m/e58dd487-7ed4-3f95-c63c-24200ed768be@berkvens.net
   > >
   > > --
   > > Álvaro Herrera        Breisgau, Deutschland  —
   > https://www.EnterpriseDB.com/
   > >
   > >
   >
   

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

   Suraj Kharage <suraj.kharage@enterprisedb.com> — 2026-04-06T10:43:36Z

   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/>
   

10. Fwd: [PATCH] Add support for INSERT ... SET syntax

    Triveni N <triveni.n@enterprisedb.com> — 2026-06-19T13:35:03Z

    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/>
    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
    

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

    solai v <solai.cdac@gmail.com> — 2026-06-22T05:14:09Z

    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