Proposal: INSERT ... BY NAME

Hi Ayush,
I tested the patch and the feature worked as expected in my testing.

Before applying the patch, INSERT ... BY NAME resulted in a syntax
error because PostgreSQL only supports positional matching.
After applying the patch, the same query executed successfully and the
source columns were matched to the target columns by their names, even
when the SELECT list was in a different order.

I also tried a case where the SELECT list reordered columns with
different data types (age, id, name). Without BY NAME, PostgreSQL
attempted positional insertion and failed with a type mismatch error.
With BY NAME, the columns were correctly matched by name and the row
was inserted successfully.

From my testing, the implementation behaves as described in the
proposal and clearly demonstrates the benefit of name-based column
matching.

Thanks for working on this feature.

Regards,
solai

Hi Ayush

On 03/07/2026 13:07, Ayush Tiwari wrote:

Attached is a draft patch for INSERT ... BY NAME.  It matches the result
columns of a source query to target columns by name instead of by position,
which is useful when the source and target columns are written in different
orders.  Similar syntax exists in DuckDB [1] and Oracle [2]:

    INSERT INTO t1 (c1, c2)
        BY NAME
        SELECT c1 * 10 AS c2, c2 + 5 AS c1 FROM t2;

Thanks for the patch!

I've tested the feature and it's doing what it intends to.

Apparently DuckDB does not support specific target columns:

memory D CREATE TABLE t (a int, b int);
memory D INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
Parser Error:
syntax error at or near "BY"

LINE 1: INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
^
memory D INSERT INTO t BY NAME SELECT 37 AS b, 42 AS a;
memory D SELECT * FROM t;
┌───────┬───────┐
│ a │ b │
│ int32 │ int32 │
├───────┼───────┤
│ 42 │ 37 │
└───────┴───────┘

But your patch allows it:

psql (20devel)
Type "help" for help.

postgres=# CREATE TABLE t (a int, b int);
CREATE TABLE
postgres=# INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
INSERT 0 1
postgres=# SELECT * FROM t;
a | b
----+----
42 | 37
(1 row)

Since I don't have a copy of the SQL spec, I can't tell if this is a
feature gap from DuckDB or if you just added this feature yourself. What
does the spec say about it?

Thanks!

Best, Jim

On 7/10/26 09:46, Jim Jones wrote:

postgres=# CREATE TABLE t (a int, b int);
CREATE TABLE
postgres=# INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
INSERT 0 1
postgres=# SELECT * FROM t;
a | b
----+----
42 | 37
(1 row)

Since I don't have a copy of the SQL spec, I can't tell if this is a
feature gap from DuckDB or if you just added this feature yourself. What
does the spec say about it?

It is technically not yet in the standard but it was accepted and will
almost certainly be there in the next version. I was there as a guest at
the meeting when this very question was discussed and that is indeed a
feature gap in DuckDB. But as it is a very new feature in the upcoming
version of the standard that is to be expected.

According the accepted change proposal which was discussed when I was there:

INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;

is effectively the same thing as:

INSERT INTO t (a,b) BY NAME SELECT a, b FROM (SELECT 37 AS b, 42 AS a);

--
Andreas Karlsson
Percona

On 7/10/26 10:06, Andreas Karlsson wrote:

According the accepted change proposal which was discussed when I was
there:

INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;

is effectively the same thing as:

INSERT INTO t (a,b) BY NAME SELECT a, b FROM (SELECT 37 AS b, 42 AS a);

Sorry, saw a minor typo in my example, it should have been:

INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;

is effectively the same thing as:

INSERT INTO t (a,b) SELECT a, b FROM (SELECT 37 AS b, 42 AS a);

--
Andreas Karlsson
Percona

Em sex., 3 de jul. de 2026 às 08:08, Ayush Tiwari <
ayushtiwari.slg01@gmail.com> escreveu:

- BY NAME requires a query source and is rejected for VALUES and DEFAULT
VALUES. BY POSITION is accepted with DEFAULT VALUES as a no-op.

Is it rejected for VALUES, even if those VALUES are named ?
This one should work, doesn't it ?
insert into t1 (c1, c2) by name select * from (Values (1,2)) x(c2, c1)

And is missing a test for select *, right ?
create table a(a int, b int, c int);
create table b(b int, c int, a int);
insert into a by name select * from b;

regards
Marcos

On 10.07.26 10:06, Andreas Karlsson wrote:

It is technically not yet in the standard but it was accepted and will
almost certainly be there in the next version. I was there as a guest at
the meeting when this very question was discussed and that is indeed a
feature gap in DuckDB. But as it is a very new feature in the upcoming
version of the standard that is to be expected.

Thanks for the info!

I tested this feature in my different scenarios and it seems pretty
solid. I'll try to get my hands on this spec draft to see if it's compliant.

Thanks!

Best, Jim

Hi,

On Fri, 10 Jul 2026 at 20:15, Marcos Pegoraro <marcos@f10.com.br> wrote:

Em sex., 3 de jul. de 2026 às 08:08, Ayush Tiwari <
ayushtiwari.slg01@gmail.com> escreveu:

- BY NAME requires a query source and is rejected for VALUES and DEFAULT
VALUES. BY POSITION is accepted with DEFAULT VALUES as a no-op.

Is it rejected for VALUES, even if those VALUES are named ?
This one should work, doesn't it ?
insert into t1 (c1, c2) by name select * from (Values (1,2)) x(c2, c1)

Yes, it works, the source there is a SELECT, not a bare VALUES list,
so the aliased columns c2/c1 give BY NAME real names to match on:

insert into t1 (c1, c2) by name select * from (Values (1,2)) x(c2, c1);
-- c1=2, c2=1

Only the *bare* VALUES form is rejected, since a VALUES row has no column
names of its own. Maybe I'll reword the docs to make that distinction
clear.

And is missing a test for select *, right ?

create table a(a int, b int, c int);
create table b(b int, c int, a int);
insert into a by name select * from b;

Good catch, that path isn't tested. It works (the star expands to b's
column names before matching), so I'll add a regression test for it.

Thanks for the review!

Regards,
Ayush

Hi,

On Mon, 13 Jul 2026 at 16:07, Jim Jones <jim.jones@uni-muenster.de> wrote:

On 10.07.26 10:06, Andreas Karlsson wrote:

It is technically not yet in the standard but it was accepted and will
almost certainly be there in the next version. I was there as a guest at
the meeting when this very question was discussed and that is indeed a
feature gap in DuckDB. But as it is a very new feature in the upcoming
version of the standard that is to be expected.

Thanks for the info!

I tested this feature in my different scenarios and it seems pretty
solid. I'll try to get my hands on this spec draft to see if it's
compliant.

Thanks a lot for testing the feature out.

Regards,
Ayush

Em qua., 22 de jul. de 2026 às 08:39, Ayush Tiwari <
ayushtiwari.slg01@gmail.com> escreveu:

Only the *bare* VALUES form is rejected, since a VALUES row has no column
names of its own. Maybe I'll reword the docs to make that distinction
clear.

yeap, I tested and it works, but it would be good to mention that only
unnamed values don't work.

regards
Marcos

Hi Ayush,

This is a solid patch but here are my observations:

1. The BY NAME + VALUES/DEFAULT VALUES rejection check runs before the
target
table/column are validated, which masks the real error:

postgres=# INSERT INTO no_such_table BY NAME VALUES (1,2);
ERROR: cannot use BY NAME with VALUES
HINT: BY NAME requires a query, such as a SELECT, as the data source.

It reports "cannot use BY NAME with VALUES" instead of "relation does not
exist".
This is an ordering bug, easy to fix by moving the check after
setTargetTable()/checkInsertTargets().

2. *srccolnames list is not needed: *It's a separate list built only to
carry tle->resname values, but the same names are already available on
selectQuery->targetList. transformInsertColsByName could just
read them from there directly, one less list to carry around.

3. *Grammar has too many hand-written alternatives:*
insert_rest grows from 5 to 13 almost-identical rules to cover
every order of BY NAME/POSITION with OVERRIDING and the column
list. Each one repeats the same 3 assignments by hand. The
codebase already has a pattern for this kind of thing
(opt_unique_null_treatment), worth factoring this the same way so
a future change does not need to touch 8 places at once.
4. *matched Bitmapset duplicates new_attrnos:*
Both matched and new_attrnos are built in the same loop and hold
the same information. bms_is_member(x, matched) could just be
list_member_int(new_attrnos, x), one less variable to keep in
sync.

Regards,
Vaibhav

On Wed, Jul 22, 2026 at 5:42 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

Hi,

Thanks for the review.

I've attached v2 that addresses both Marcos and your points.

Marcos wrote:

it would be good to mention that only unnamed values don't work.

The docs now call the unsupported form a "bare VALUES source", and show
the named form:

INSERT INTO t1 BY NAME
SELECT * FROM (VALUES (1, 2)) AS v(c2, c1);

And is missing a test for select *, right?

Added.

On Thu, 10 Sept 2026 at 13:50, Vaibhav Dalvi
<vaibhav.dalvi@enterprisedb.com> wrote:

This is a solid patch but here are my observations:

1. The BY NAME + VALUES/DEFAULT VALUES rejection check runs before the target
table/column are validated, which masks the real error:

postgres=# INSERT INTO no_such_table BY NAME VALUES (1,2);
ERROR: cannot use BY NAME with VALUES
HINT: BY NAME requires a query, such as a SELECT, as the data source.

It reports "cannot use BY NAME with VALUES" instead of "relation does not exist".
This is an ordering bug, easy to fix by moving the check after setTargetTable()/checkInsertTargets().

I moved it after setTargetTable() and checkInsertTargets(), so relation and
attname errors win, but bare VALUES is still rejected before source
transformation. Thanks for raising this.

2. srccolnames list is not needed: It's a separate list built only to
carry tle->resname values, but the same names are already available on
selectQuery->targetList. transformInsertColsByName could just
read them from there directly, one less list to carry around.

Dropped. transformInsertColsByName() now walks selectQuery->targetList,
skips resjunk TLEs, matches on resname, and uses tle->expr for errposition.

3. Grammar has too many hand-written alternatives:
insert_rest grows from 5 to 13 almost-identical rules to cover
every order of BY NAME/POSITION with OVERRIDING and the column
list. Each one repeats the same 3 assignments by hand. The
codebase already has a pattern for this kind of thing
(opt_unique_null_treatment), worth factoring this the same way so
a future change does not need to touch 8 places at once.

I took a narrower route here: the old insert_rest productions stay as-is,
while insert_by_options covers only the new BY forms. My thought was to
avoid repeating the semantic actions without routing existing INSERT syntax
through new grammar. Does that seem like a reasonable boundary?

4. matched Bitmapset duplicates new_attrnos:
Both matched and new_attrnos are built in the same loop and hold
the same information. bms_is_member(x, matched) could just be
list_member_int(new_attrnos, x), one less variable to keep in
sync.

Dropped. The duplicate check now uses list_member_int(new_attrnos,
matchattno).

I also added psql completion.

Thoughts on the v2?

Regards,
Ayush

Hi Ayush,

All the points from my last review are fixed.

A few small new comments, none are bugs:

1) No tab-completion after "BY NAME"/"BY POSITION" is typed (unlike
OVERRIDING, which does complete SELECT/VALUES/TABLE after it).
2) "INSERT ... BY NAME TABLE other_table" works but has no regression
test.

Regards,
Vaibhav Dalvi
EnterpriseDB

On Sat, Sep 12, 2026 at 9:12 PM Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote: