Message-ID: <e96b935f-0745-45da-9aa1-bed09d4be62d@uni-muenster.de>
Date: Fri, 10 Jul 2026 09:46:59 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Proposal: INSERT ... BY NAME
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
References: 
 <CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com>
Content-Language: en-US, de-DE
From: Jim Jones <jim.jones@uni-muenster.de>
In-Reply-To: 
 <CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e96b935f-0745-45da-9aa1-bed09d4be62d%40uni-muenster.de>
Precedence: bulk

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