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
