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

Hi,

Thanks for the review.

When the same array element or field target was assigned twice in one row,
transformInsertSetClause() collapsed the two into one slot and kept only
the last value.
The earlier expression was never analysed, so an invalid reference (or a
nextval() call) was silently dropped.

I've followed your suggestion. The same-row duplicate check now also
compares
the indirection with equal(), so an identical repeated target is rejected:

insert into ta set id=1, arr[0]=0, arr[1]=some_random, arr[1]=2;
ERROR: column "arr" specified more than once

This applies to the single-row form, the parenthesised single-row form,
and every row of a multi-row INSERT ... SET. Assignments to different
elements or fields
of the same column (arr[1]=1, arr[2]=2) are still allowed, and so are
whole-column
assignments that differ between rows. I've added regression tests for these
cases.

--

Thanks & Regards,
Suraj kharage,

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

On Tue, Oct 20, 2026 at 2:54 AM Mario González <gonzalemario(at)gmail(dot)com>
wrote:

> On Mon, 12 Oct 2026 at 09:02, Vaibhav Dalvi <
> vaibhav(dot)dalvi(at)enterprisedb(dot)com> wrote:
>
>> Hi Suraj,
>>
>> This makes sense to me.
>>
>> Regards,
>> Vaibhav
>>
>>
> Hello Suraj, I was having another look and I ran into this scenario that I
> think it's an error worth fixing. Not sure if you've fixed this in a future
> version already?
>
> Whilst testing v6 I came across a corner case with repeated array/field
> targets. If the same target is assigned twice in one row, the earlier
> expression is quietly discarded before it's ever analysed.
>
> A single invalid assignment is caught, as you'd expect:
>
> create table ta (id int, arr int[]);
>
> insert into ta set id=1, arr[0]=0, arr[1]=some_random;
> ERROR: column "some_random" does not exist
>
> But repeating the target lets it through:
>
> insert into ta set id=1, arr[0]=0, arr[1]=some_random, arr[1]=2;
> INSERT 0 1
>
> The column-list form, insert into ta (id, arr[0], arr[1], arr[1]) values
> (...), rejects the same thing, and UPDATE ... SET evaluates both
> expressions, so any side effects (e.g. nextval()) are lost here too. The
> comment in transformInsertSetClause() saying this matches column-list
> INSERT/UPDATE behaviour isn't quite right.
>
> Hardly a serious issue, but I'd suggest simply rejecting an identical
> repeated target within a row, by adding equal(res->indirection,
> row_col->indirection) to the first-pass duplicate check, and adding a
> regression test for it.
>
> Also, I reckon there are other smaller issues with arrays but before going
> any further, the main problem IMHO, it seems there's no committer
> sponsoring this? I've leanrt that the code could be rock solid but it can
> stay as-is forever, or worse, someone else with commit perms can push a
> similar fix bypassing all this effort.
>
> Best.
> Mario.
>
>
>>
>>
>> On Thu, Sep 10, 2026 at 2:58 PM Suraj Kharage <
>> suraj(dot)kharage(at)enterprisedb(dot)com> wrote:
>>
>>>
>>> On Wed, Sep 2, 2026 at 6:10 PM Vaibhav Dalvi <
>>> vaibhav(dot)dalvi(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi,
>>>>
>>>> Thanks Suraj for the clarification. I see two issues here, both
>>>> stemming from the same root cause:
>>>>
>>>> *Issue #1: A row that skips an array column entirely gets wrongly
>>>> rejected*
>>>> Setup:
>>>> create table ta(id int primary key, arr int[] default array[1]);
>>>>
>>>> Command:
>>>> insert into ta set (id=70, arr[1]=1), (id=71, arr[1]=2), (id=72);
>>>>
>>>> Actual result:
>>>> ERROR: cannot set an array element to DEFAULT
>>>>
>>>> What's wrong: row id=72 doesn't mention arr in any way, no slot, no
>>>> whole column, nothing.
>>>> In plain English, it's saying "I don't care about arr, just use
>>>> whatever it normally is."
>>>> That should give it the column's default ({1} here, since one was
>>>> declared, or NULL if not)
>>>> exactly like skipping any other ordinary column works fine. Instead,
>>>> because other rows in the
>>>> same statement touch arr[1], the code silently treats row 72's silence
>>>> as if it had explicitly written
>>>> arr[1] = DEFAULT, and setting a single array slot to DEFAULT is always
>>>> that asked for nothing
>>>> gets blamed for a request it never made, and the whole statement fails.
>>>>
>>>> Expected result:
>>>> id | arr
>>>> ----+-----
>>>> 70 | {1}
>>>> 71 | {2}
>>>> 72 | {1} -- the column's default, same as ny other statement
>>>>
>>>> *Issue #2: Mixing "whole column" and "one slot" across different rows
>>>> gets wrongly rejected*
>>>> Setup:
>>>> create table t1(id int primary key, arr int[] default array[1]);
>>>>
>>>> Command:
>>>> insert into t1 set (id=1, arr[1]=1), (id=2, arr=default);
>>>>
>>>> Actual result:
>>>> ERROR: column "arr" specified more than once
>>>>
>>>> What's wrong: Row 1 wants to set slot 1 of its own array. Row 2 wants
>>>> its own array to just be the default.
>>>> These are two completely separate rows with two completely separate
>>>> array values, nothing about them
>>>> actually conflicts. However, the code doesn't check rows independently;
>>>> it first lumps every row's arr-related
>>>> target into one shared bucket, and only then checks for conflicts. In
>>>> that shared bucket, "the whole column"
>>>> and "just slot 1" look like a contradiction, even though they aren't,
>>>> since they belong to two different rows, not
>>>> one row saying two contradictory things about the same value.
>>>>
>>>> Expected result:
>>>> id | arr
>>>> ----+-----
>>>> 1 | {1}
>>>> 2 | {1} -- the column's default
>>>>
>>>> Another thought here is: should we restrict array/field indirection
>>>> targets instead like below?
>>>>
>>> Thanks for looking into this.
>>>
>>> Both issues share the same root cause: multi-row SET merges all rows
>>> into one shared column list,
>>> so once a column uses arr[1]-style indirection, every row must supply
>>> that exact same target.
>>> I think if we make this work, then need bypassing core Postgres
>>> INSERT/rewrite internals logic and it becomes complex.
>>> Postgres builds one assignment expression per column and reuses it for
>>> every row (via Vars into the VALUES RTE),
>>> and the rewriter refuses to merge a whole-column value with an
>>> element/field assignment on the same column
>>> ("multiple assignments to same column" in process_matched_tle()).
>>>
>>> So I went with your suggested restriction, but narrower: if all rows set
>>> the same array/field target (e.g. all rows set arr[1]),
>>> it keeps working as before. But if rows disagree — one row skips it, or
>>> sets the whole column, or targets a different element/field —
>>> we now raise one clear error.
>>> Updated docs and tests accordingly.
>>>
>>> Please let me know your thoughts on the same.
>>>
>>> Regards,
>>> Suraj
>>>
>>>
>>>
>>>> insert into t1 set (id=1, arr[1]=1), (id=2, arr=default);
>>>> ERROR: array/field indirection targets are not supported in multi-row
>>>> INSERT ... SET
>>>>
>>>> Per my knowledge, MySQL and MariaDB support this INSERT ... SET syntax:
>>>> - Standard MySQL does not support multi-row INSERT ... SET directly
>>>> using SET ... SET.
>>>> In MySQL, INSERT ... SET is strictly single-row:
>>>>
>>>> INSERT INTO employees SET id = 1, name = 'Alice', salary = 70000;
>>>>
>>>> - MariaDB extends the standard INSERT ... SET syntax to support
>>>> multiple rows by repeating the SET clause for each row:
>>>>
>>>> INSERT INTO employees
>>>> SET id = 1, name = 'Alice', salary = 70000,
>>>> SET id = 2, name = 'Bob', salary = 80000,
>>>> SET id = 3, name = 'Charlie', salary = 75000;
>>>>
>>>> The point is to check if we can think in that direction?
>>>>
>>>> Regards,
>>>> Vaibhav
>>>>
>>>> On Mon, Aug 31, 2026 at 10:20 AM Suraj Kharage <
>>>> suraj(dot)kharage(at)enterprisedb(dot)com> wrote:
>>>>
>>>>>
>>>>> On Fri, Aug 28, 2026 at 11:39 AM Vaibhav Dalvi <
>>>>> vaibhav(dot)dalvi(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> create table ta(id int primary key, arr int[] default array[1]);
>>>>>>
>>>>>> -- classic VALUES: row 3 gets the table's default {1}
>>>>>> insert into ta values (70, array[1]), (71, array[2]), (72, default);
>>>>>> -- id | arr
>>>>>> -- ----+-----
>>>>>> -- 70 | {1}
>>>>>> -- 71 | {2}
>>>>>> -- 72 | {1}
>>>>>>
>>>>>> -- new SET syntax: same intent, row 3 just omits arr
>>>>>> insert into ta set (id=170, arr[1]=1), (id=171, arr[1]=2), (id=172);
>>>>>> -- ERROR: cannot set an array element to DEFAULT
>>>>>>
>>>>>
>>>>> Thanks for reporting this. I have looked into this and feel that this
>>>>> is an existing behavior for INSERT command.
>>>>> transformAssignedExpr() unconditionally rejects DEFAULT for any
>>>>> indirection target (array element or subfield).
>>>>>
>>>>> For e.g. for plain insert as well it will fail -
>>>>> postgres(at)2495950=#INSERT INTO ta(id, arr[1]) VALUES
>>>>> (70,1),(71,2),(72, default);
>>>>> ERROR: cannot set an array element to DEFAULT
>>>>> LINE 1: INSERT INTO ta(id, arr[1]) VALUES (70,1),(71,2),(72, default...
>>>>>
>>>>>
>>>>> It will work in both cases, if we try to insert in arr column.
>>>>> postgres(at)2495950=#INSERT INTO ta VALUES
>>>>> (70,array[1]),(71,array[2]),(72, default);
>>>>> INSERT 0 3
>>>>> postgres(at)2495950=#select * from ta;
>>>>> id | arr
>>>>> ----+-----
>>>>> 70 | {1}
>>>>> 71 | {2}
>>>>> 72 | {1}
>>>>> (3 rows)
>>>>>
>>>>>
>>>>> postgres(at)2495950=#insert into ta set (id=170, arr=array[1]), (id=171,
>>>>> arr=array[2]), (id=172, arr=default);
>>>>> INSERT 0 3
>>>>> postgres(at)2495950=#select * from ta;
>>>>> id | arr
>>>>> -----+-----
>>>>> 170 | {1}
>>>>> 171 | {2}
>>>>> 172 | {1}
>>>>> (3 rows)
>>>>>
>>>>> I hope this helps.
>>>>>
>>>>> Regards,
>>>>> Suraj
>>>>>
>>>>>
>>>>>>
>>>>>> [...]
>
>