Subject: Proposal: INSERT ... BY NAME

From: Ayush Tiwari <[email-masked]>
Date: Fri, 3 Jul 2026 16:37:55 +0530

Hi,

I wanted to take a stab at implementing INSERT ... BY NAME, which has been
accepted into the SQL draft standard.  I could not find an existing patch
for
PostgreSQL; apologies if this has already been proposed.  I learned about
the
standardization update from Peter's recent blog post.

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;

Here c1 gets c2 + 5 and c2 gets c1 * 10.  BY POSITION remains the default
and can also be written explicitly.

Behavior that the patch exhibits:

  - Each source column must match exactly one target column.  Unknown source
    names and duplicate matches are errors.

  - Target columns not named by the query get their default values.  An
    explicit target column list narrows the candidate target columns;
without
    one, all table columns are candidates.

  - BY NAME requires a query source and is rejected for VALUES and DEFAULT
    VALUES.  BY POSITION is accepted with DEFAULT VALUES as a no-op.

  - BY NAME is rejected for target lists that assign to subfields or array
    elements, since matching on the base column name would be ambiguous.

The implementation resolves BY NAME during parse analysis by reordering the
target column/attribute-number lists to match the source column order.
After
that, the query tree is an ordinary positional INSERT, so no rule/view
deparsing changes were needed in the patch.

For comparison, DuckDB [1] and Oracle 23ai [2] implement the same core
behavior: source names are matched against target columns, unmatched target
columns default, and unknown source names are errors.

It's still a WIP patch, I'm trying out different queries, but it would be
great if I
could get some reviews on the direction.

Thoughts?

[1]
https://duckdb.org/docs/current/sql/statements/insert#insert-into--by-name
[2]
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/INSERT.html

Regards,
Ayush

---

From: solai v <[email-masked]>
Date: Wed, 8 Jul 2026 12:22:28 +0530

Hi Ayush,
I tested the patch and the feature worked as expected in my testing.

Before applying the patch, INSERT ... BY NAME resulted in a syntax
error because PostgreSQL only supports positional matching.
After applying the patch, the same query executed successfully and the
source columns were matched to the target columns by their names, even
when the SELECT list was in a different order.

I also tried a case where the SELECT list reordered columns with
different data types (age, id, name). Without BY NAME, PostgreSQL
attempted positional insertion and failed with a type mismatch error.
With BY NAME, the columns were correctly matched by name and the row
was inserted successfully.