Обсуждение: Request new version to support "on commit drop" for create temp table ... as select ?

Good morning,

I googled to find that "on commit drop" does not support:

(a) create temp table as select * from table1 where 1<>2;
http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php

If table1 has complex table structure, grammar(a) will save lots of 
codes - col1 varchar(1), col2, ..... colN

I just wonder would the new version support "on commit drop" for select?


Thanks a lot!
-- 
Lu Ying

Emi Lu <emilu@encs.concordia.ca> writes:
> I googled to find that "on commit drop" does not support:

> (a) create temp table as select * from table1 where 1<>2;
> http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php

Ah, the pitfalls of believing that the first google hit you get
is authoritative.  Didn't you notice that message was from 2005?
        regards, tom lane

On Tue, Jul 14, 2009 at 10:47 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Emi Lu <emilu@encs.concordia.ca> writes:
>> I googled to find that "on commit drop" does not support:
>
>> (a) create temp table as select * from table1 where 1<>2;
>> http://archives.postgresql.org/pgsql-sql/2005-09/msg00153.php
>
> Ah, the pitfalls of believing that the first google hit you get
> is authoritative.  Didn't you notice that message was from 2005?

That's what I thought, but

create temp table xyz as select * from abc on commit drop;

still fails on 8.3.  Was this fixed in 8.4 or is my syntax wonky?

Scott Marlowe <scott.marlowe@gmail.com> writes:
> That's what I thought, but

> create temp table xyz as select * from abc on commit drop;

> still fails on 8.3.  Was this fixed in 8.4 or is my syntax wonky?

Your syntax is wonky -- switch the clause order.
http://www.postgresql.org/docs/8.3/static/sql-createtableas.html
        regards, tom lane

Got it. Thank you Tom!

> Scott Marlowe <scott.marlowe@gmail.com> writes:
>> That's what I thought, but
> 
>> create temp table xyz as select * from abc on commit drop;
> 
>> still fails on 8.3.  Was this fixed in 8.4 or is my syntax wonky?
> 
> Your syntax is wonky -- switch the clause order.
> http://www.postgresql.org/docs/8.3/static/sql-createtableas.html
> 
>             regards, tom lane
>