Subject: INSERT ... SELECT nonpositional syntax

From: Matthew Mellon <[email-masked]>
Date: Mon, 20 May 2024 17:56:47 -0400

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

---

From: Tom Lane <[email-masked]>
Date: Mon, 20 May 2024 18:58:05 -0400

Matthew Mellon <[email-masked]> writes:
> ...

> fighting chance of extending the syntax in the base product?

I recall that we've discussed having some way to do this; but not with
that particular syntax.  If memory serves, the last proposal looked
more like UPDATE ... SET, that is

  INSERT INTO TableX SET i = a, j = b, k = c FROM TableY;

The general consensus though seems to be that we don't want to get
out front of the SQL standards committee on something like this.
If we do, for sure they'll standardize some other way to do
the same thing :-(

			regards, tom lane