創建xstore存儲表
更新時間 2025-02-05 09:36:50
最近更新時間: 2025-02-05 09:36:50
分享文章
本頁介紹天翼云TeleDB數據庫xstore存儲表的創建、查看、刪除等操作。
創建xstore表
要創建xstore表，需要將表的訪問方式(Table Access Method) 設為 xstore，在建表時顯式使用USING xstore子句指定表訪問方式。
teledb=# create table xt(a int,b int, c text) using xstore;
CREATE TABLE
teledb=# \d+ xt;
Table "public.xt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | text | | | | extended | |
Distribute By: HASH(a)
Location Nodes: ALL DATANODES建立xbtree索引
xstore引擎使用專用的xbtree索引,建立主鍵和索引默認都是使用xbtree索引,指定btree索引會自動變成xbtree索引,指定其他類型索引會失敗。
teledb=# create table xt1(a int primary key, b int ,c text) using xstore;
teledb=# \d+ xt1;
Table "public.xt1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
c | text | | | | extended | |
Indexes:
"xt1_pkey" PRIMARY KEY, xbtree (a)
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
teledb=# create index tx1_b on xt1 using btree(b);
CREATE INDEX
teledb=# create index tx1_c on xt1 using hash(c);
ERROR: hash index is not supported for xstore, please use xbtree instead
postgres=# create index tx1_c on xt1 using xbtree(c);
CREATE INDEX
postgres=# \d+ xt1;
Table "public.xt1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
c | text | | | | extended | |
Indexes:
"xt1_pkey" PRIMARY KEY, xbtree (a)
"tx1_b" xbtree (b)
"tx1_c" xbtree (c)
Distribute By: HASH(a)
Location Nodes: ALL DATANODES查看xstore表訪問方式
查詢系統目錄表pg_class 和 pg_am檢查xstore存儲表的訪問方式
teledb=# select relname,amname from pg_class,pg_am where relam=pg_am.oid and relname='xt';
relname | amname
---------+--------
xt | xstore
(1 row)刪除xstore存儲表
刪除列xstore表和行存表語法一致，使用drop table 語句刪除
teledb=# drop table xt;
DROP TABLE
