Thread

1. Proposal: INSERT ... BY NAME

   Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-07-03T11:07:55Z

   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
   

2. Re: Proposal: INSERT ... BY NAME

   solai v <solai.cdac@gmail.com> — 2026-07-08T06:52:28Z

   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.
   
   From my testing, the implementation behaves as described in the
   proposal and clearly demonstrates the benefit of name-based column
   matching.
   
   Thanks for working on this feature.
   
   Regards,
   solai