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