AntDB用户手册——建表之创建临时表和说明

AntDB 的临时表只存在于当前会话中，会话结束后，临时表也就消失了。
查看帮助信息：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
复制

其中 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] 部分决定了 临时表及其数据的生命周期。

• PRESERVE ROWS ： 默认值，事务提交后，数据还在，跟随表的生命周期。
• DELETE ROWS ： 事务提交后，临时表里的数据也被删除。
• DROP： 事务提交后，临时表也被删除。

通过实例查看数据的生命周期：
drop :

antdb=# begin;
BEGIN
antdb=# CREATE TEMP TABLE mytemp(c INT) on commit drop;
CREATE TABLE
antdb=# insert into mytemp select 1;
INSERT 0 1
antdb=# select * from mytemp;
 c 
---
 1
(1 row)

antdb=# commit;
COMMIT
antdb=# select * from mytemp;
ERROR:  relation "mytemp" does not exist
LINE 1: select * from mytemp;
复制

delete rows:

antdb=# CREATE TEMP TABLE mytemp(c INT) on commit delete rows;
CREATE TABLE
antdb=# begin;
BEGIN
antdb=# insert into mytemp select 1;
INSERT 0 1
antdb=# select * from mytemp;
 c 
---
 1
(1 row)

antdb=# commit;
COMMIT
antdb=# select * from mytemp;
 c 
---
(0 rows)
复制

默认：

antdb=# CREATE TEMP TABLE mytemp(c INT);
CREATE TABLE
antdb=# begin;
BEGIN
antdb=# insert into mytemp select 1;
INSERT 0 1
antdb=# select * from mytemp;
 c 
---
 1
(1 row)

antdb=# commit;
COMMIT
antdb=# select * from mytemp;
 c 
---
 1
(1 row)
复制

确保连接信息正确后，就可以进行建表操作。通过如下SQL进行查询验证连接信息：

antdb=> select current_database(),current_user,current_schema;
 current_database | current_user | current_schema 
------------------+--------------+----------------
 antdb            | user1        | user1
(1 row)
复制