On Mon, 6 Jun 2005, Francois Ochsenbein wrote:
> Well, I'm surprised -- and doubt that Postgres really represents THE SQL
> standard ... SELECT INTO exists since the first versions of SQL,
> and does generates a table (or a relation if you prefer the "purist"
> relational language)
I hate to disagree with someone with as much experience as Francois, but my reading of the SQL1992 Standard (the latest one I can find online) is that SELECT INTO only refers to a [list of] variables, not a table. The syntax is pretty clear, but the semantics in the Standard are pretty hard to understand, so my interpretation could be wrong.
The PostgreSQL reference manual is quite helpful about describing any differences between its syntax and that of Standard SQL. It has this to say about SELECT INTO:
<quote>
SQL92 uses SELECT ... INTO to represent selecting values into scalar
variables of a host program, rather than creating a new table. [snip] The
PostgreSQL usage of SELECT INTO to represent table creation is historical.
It is best to use CREATE TABLE AS for this purpose.
</quote>
There is an excellent compilation of differences in the implementation of SQL among popular DBMS at http://troels.arvin.dk/db/rdbms/ although unfortunately it does not deal with this particular issue. But the only conclusion that can be drawn from reading this compilation is that the whole subject is a can of worms: *none* of the main DMBS vendors takes *any* serious notice of *any* SQL Standards, so I don't see why our ADQL should be constrained by them.
My suggestion is that we should continue to use SELECT INTO <newtable> in the way that we originally planned, rather than have to get involvind in adding a whole new syntax tree for CREATE TABLE AS, when we don't at present support anything like CREATE TABLE.
-- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1 7RH, U.K.Received on 2005-06-07Z12:50:26