public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jim Jones <[email protected]>
To: Ayush Tiwari <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: Proposal: INSERT ... BY NAME
Date: Fri, 10 Jul 2026 09:46:59 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com>
References: <CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com>

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

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Proposal: INSERT ... BY NAME
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox