Обсуждение: [PATCH] Add support for INSERT ... SET syntax
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.
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, 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.
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.
--
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/
>
>