From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
cc: Michael Paquier <michael@paquier.xyz>, Stephen Frost <sfrost@snowman.net>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: SELECT INTO deprecation
In-reply-to: <8e05fc79-abca-dd3f-4d48-8aaae9525a8f@enterprisedb.com>
References: <96dc0df3-e13a-a85d-d045-d6e2c85218da@enterprisedb.com>
 <20201202175836.GX16415@tamriel.snowman.net>
 <1672926.1606941346@sss.pgh.pa.us> <X8gpP9GL7R5AsF3E@paquier.xyz>
 <8e05fc79-abca-dd3f-4d48-8aaae9525a8f@enterprisedb.com>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@enterprisedb.com>
	message dated "Thu, 03 Dec 2020 13:16:32 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1723855.1607009655.1@sss.pgh.pa.us>
Date: Thu, 03 Dec 2020 10:34:15 -0500
Message-ID: <1723856.1607009655@sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> Interesting.  This appears to be the case.  SQL Server uses SELECT INTO 
> to create a table, and does not appear to have CREATE TABLE AS.
> So maybe we should keep it, but adjust the documentation to point out 
> this use case.

That argument makes sense, but only if our version is a drop-in
replacement for SQL Server's version: if people have to adjust their
commands anyway in corner cases, we're not doing them any big favor.
So: are the syntax and semantics really a match?  Do we have feature
parity?

As I recall, a whole lot of the pain we have with INTO has to do
with the semantics we've chosen for INTO in a set-operation nest.
We think you can write something like

   SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...

but we insist on the INTO being in the first component SELECT.
I'd like to know exactly how much of that messiness is shared
by SQL Server.

(FWIW, I think the fact that SELECT INTO means something entirely
different in plpgsql is a good reason for killing off one version
or the other.  As things stand, it's mighty confusing.)

			regards, tom lane