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

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