Thread: CREATE TEMP TABLE AS ... ON COMMIT DROP fails

I need to create temporary table with data which is dropped at end of
transaction.

CREATE TABLE t1 ( c1 INTEGER ) ;
INSERT INTO t1 VALUES (1);
CREATE TEMP TABLE t2 AS SELECT * FROM t1 ON COMMIT DROP;

Causes ERROR:  syntax error at or near "ON" at character 104

How to implement this ?

Andrus.

In article <dklr9g$781$2@news.hub.org>, Andrus Moor <eetasoft@online.ee> wrote:
>I need to create temporary table with data which is dropped at end of
>transaction.
>
>CREATE TABLE t1 ( c1 INTEGER ) ;
>INSERT INTO t1 VALUES (1);
>CREATE TEMP TABLE t2 AS SELECT * FROM t1 ON COMMIT DROP;
>
>Causes ERROR:  syntax error at or near "ON" at character 104
>
>How to implement this ?

You can't just explicitly drop the table just before the commit?

--
http://yosemitenews.info/

"Andrus Moor" <eetasoft@online.ee> writes:
> CREATE TEMP TABLE t2 AS SELECT * FROM t1 ON COMMIT DROP;
> Causes ERROR:  syntax error at or near "ON" at character 104

Not implemented, as the manual clearly states.

> How to implement this ?

Create the temp table with a plain CREATE, then fill it with
INSERT ... SELECT.

            regards, tom lane