MIME-Version: 1.0
From: Matthew Mellon <matthew.mellon@gmail.com>
Date: Mon, 20 May 2024 17:56:47 -0400
Message-ID: 
 <CADNMQCJb=ivwfjeVV=2V8eG24MJLxWC_stp3hW+JTJ8=zwMWJg@mail.gmail.com>
Subject: INSERT ... SELECT nonpositional syntax
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004a72120618e9c91f"
Archived-At: 
 <https://www.postgresql.org/message-id/CADNMQCJb%3DivwfjeVV%3D2V8eG24MJLxWC_stp3hW%2BJTJ8%3DzwMWJg%40mail.gmail.com>
Precedence: bulk

--0000000000004a72120618e9c91f
Content-Type: text/plain; charset="UTF-8"

I'm a PostgreSQL novice but a very, very experienced DBA from the SAP SQL
Anywhere context (formerly Sybase / iAnywhere). Most of my code has been in
the Watcom SQL dialect in the past.

SAP's products have a nice feature not found in the ANSI standards for the
INSERT ... SELECT syntax: WITH AUTO NAME. Assume you have a table that
looks something like this:


In PostgreSQL, you can:

INSERT INTO TableX
(
  i,
  j,
  k
)
SELECT a,
  b,
  c
FROM TableY; -- or whatever SELECT expression you want returning possibly
many rows

This is nice, but it's so much nicer to have the destination columns
adjacent to your source values:

INSERT INTO TableX
WITH AUTO NAME
SELECT a i,
  b j,
  c k
FROM TableY;

I'm not complaining here, but just wondering if there is a way that the
syntax can be extended at this level using an extension, or if there is a
fighting chance of extending the syntax in the base product?

Thanks,

Matthew Mellon

--0000000000004a72120618e9c91f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I&#39;m a PostgreSQL novice but a very, very experienced D=
BA from the SAP SQL Anywhere context (formerly Sybase / iAnywhere). Most of=
 my code has been in the Watcom SQL dialect in the past.<div><br></div><div=
>SAP&#39;s products have a nice feature not found in the ANSI standards for=
 the INSERT ... SELECT syntax: WITH AUTO NAME. Assume you have a table that=
 looks something like this:</div><div><br></div><div><br></div><div>In Post=
greSQL, you can:</div><div><br></div><div>INSERT INTO TableX=C2=A0</div><di=
v>(</div><div>=C2=A0 i,=C2=A0</div><div>=C2=A0 j,=C2=A0</div><div>=C2=A0 k<=
/div><div>)=C2=A0</div><div>SELECT a,=C2=A0</div><div>=C2=A0 b,=C2=A0</div>=
<div>=C2=A0 c</div><div>FROM TableY; -- or whatever SELECT expression you w=
ant returning possibly many rows</div><div><br></div><div>This is nice, but=
 it&#39;s so much nicer to have the destination columns adjacent to your so=
urce values:</div><div><br></div><div>INSERT INTO TableX</div><div>WITH AUT=
O NAME</div><div>SELECT a i,</div><div>=C2=A0 b j,</div><div>=C2=A0 c k</di=
v><div>FROM TableY;</div><div><br></div><div>I&#39;m not complaining here, =
but just wondering if there is a way that the syntax can be extended at thi=
s level using an extension, or if there is a fighting chance of extending t=
he syntax in the base product?</div><div><br></div><div>Thanks,</div><div><=
br></div><div>Matthew Mellon</div></div>

--0000000000004a72120618e9c91f--