1、用户自建表支持页式存储
create table zcz(id int) with (segment = off);
2、创建本地临时表
create temp table tb1(a int);
insert into tb1 values(12);
select * from tb1;
drop table tb1;
CREATE TEMPORARY TABLE warehouse_t25
( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) ON COMMIT DELETE ROWS;
3、创建全局临时表
create global temp table tb2(a int);
insert into tb2 values(12);
select * from tb2;
drop table tb2;
CREATE GLOBAL TEMPORARY TABLE gtt1
( ID INTEGER NOT NULL, NAME CHAR(16) NOT NULL, ADDRESS VARCHAR(50) , POSTCODE CHAR(6) ) ON COMMIT PRESERVE ROWS;
4、创建事务级临时表
事务级临时表如果进行了TCL(commit, rollback)的操作，数据就被清空
create global temporary table tb3( empno number, ename varchar2(50), sal number )on commit delete rows;
5、创建会话级临时表
会话临时表只在当前会话(连接)内可用，且在当前会话结束(断开数据库连接)后就会销毁；当切换或者关闭了当前的窗口，数据就清空销毁。
create global temporary table tb4( empno number, ename varchar2(50), sal number )on commit preserve rows;
6、创建unlogged表
create unlogged table unlogtb(a int);
insert into unlogtb values(1);
drop table unlogtb;
CREATE UNLOGGED TABLE asdfasdsaf
( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) );
7、创建物化视图
全量物化视图
--准备数据。
openGauss=# CREATE TABLE t1(c1 int, c2 int);
openGauss=# INSERT INTO t1 VALUES(1, 1);
openGauss=# INSERT INTO t1 VALUES(2, 2);
--创建全量物化视图。
openGauss=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;
CREATE MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# SELECT * FROM mv;
count
-------
2
(1 row)
--向物化视图中基表插入数据。
openGauss=# INSERT INTO t1 VALUES(3, 3);
--对全量物化视图做全量刷新。
openGauss=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# SELECT * FROM mv;
count
-------
3
(1 row)
--删除物化视图。
openGauss=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
增量物化视图
--创建增量物化视图。
openGauss=# CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM t1;
CREATE MATERIALIZED VIEW
--插入数据。
openGauss=# INSERT INTO t1 VALUES(3, 3);
INSERT 0 1
--增量刷新物化视图。
openGauss=# REFRESH INCREMENTAL MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# SELECT * FROM mv;
c1 | c2
----+----
1 | 1
2 | 2
3 | 3
(3 rows)
--插入数据。
openGauss=# INSERT INTO t1 VALUES(4, 4);
INSERT 0 1
--全量刷新物化视图。
openGauss=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# select * from mv;
c1 | c2
----+----
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
--删除物化视图。
openGauss=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
8、创建外表
create database postgresfdw_test_db;
\c postgresfdw_test_db
set show_fdw_remote_plan = on;
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
EXECUTE $$CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING FOR public SERVER loopback3;
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10),
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
c2 text,
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft1',
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft2',
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
