public inbox for [email protected]  

help / color / mirror / Atom feed

INSERT ... SELECT nonpositional syntax
2+ messages / 2 participants
[nested] [flat]

* INSERT ... SELECT nonpositional syntax
@ 2024-05-20 21:56  Matthew Mellon <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Matthew Mellon @ 2024-05-20 21:56 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: INSERT ... SELECT nonpositional syntax
@ 2024-05-20 22:58  Tom Lane <[email protected]>
  parent: Matthew Mellon <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2024-05-20 22:58 UTC (permalink / raw)
  To: Matthew Mellon <[email protected]>; +Cc: [email protected]

Matthew Mellon <[email protected]> writes:
> 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:
> ...
> 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?

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-05-20 22:58 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-20 21:56 INSERT ... SELECT nonpositional syntax Matthew Mellon <[email protected]>
2024-05-20 22:58 ` Tom Lane <[email protected]>

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