创建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
