Re: Proposal: INSERT ... BY NAME

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