Subject: Re: Proposal: INSERT ... BY NAME

From: Jim Jones <[email-masked]>
Date: Fri, 10 Jul 2026 09:46:59 +0200

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

---

From: Andreas Karlsson <[email-masked]>
Date: Fri, 10 Jul 2026 10:06:42 +0200

> 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

---

From: Andreas Karlsson <[email-masked]>
Date: Fri, 10 Jul 2026 10:52:36 +0200

> 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) SELECT a, b FROM (SELECT 37 AS b, 42 AS a);

-- 
Andreas Karlsson
Percona

---

From: Marcos Pegoraro <[email-masked]>
Date: Fri, 10 Jul 2026 11:44:55 -0300

Em sex., 3 de jul. de 2026 às 08:08, Ayush Tiwari <
[email-masked]> 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