INSERT ... SELECT nonpositional syntax

From: Matthew Mellon <matthew(dot)mellon(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: INSERT ... SELECT nonpositional syntax
Date: 2024-05-20 21:56:47
Message-ID: CADNMQCJb=ivwfjeVV=2V8eG24MJLxWC_stp3hW+JTJ8=zwMWJg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2024-05-20 22:58:05 Re: INSERT ... SELECT nonpositional syntax
Previous Message Alan Hodgson 2024-05-07 16:34:39 Re: Access a Postgres storage with two independent instances