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

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
>>>>
>>>>
>>>>>
>>>>> [...]