PostgreSQL分区表 - 笔记一

目录

1、PostgreSQL不支持挂载当前数据库的视图

2、PostgreSQL查询外部表分区不会自动添加分区范围条件

备注

---

1、PostgreSQL不支持挂载当前数据库的视图

【验证步骤】

  创建分区表，并尝试在分区表上挂载视图

-- 创建分区表
CREATE TABLE orders  (
    id int,
    ts timestamp
) PARTITION BY RANGE(ts);

-- 创建测试表(供创建视图使用）
CREATE TABLE orders_tmp(
    id int,
    ts timestamp
) ;

-- 创建视图
create view view_orders_tmp
as 
select * from orders_tmp
where   ts >= '2020-11-01'
		
-- 挂载分区
ALTER TABLE orders attach PARTITION view_orders_tmp FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');

  报错如下：

ALTER TABLE orders attach PARTITION view_orders_tmp FOR VALUES FROM ('2020-11-01') TO ('2020-12-01')
ERROR:  "view_orders_tmp" is not a table or foreign table

   PS:

           虽说PostgreSQL不能挂载本库的视图，但是可以以外部表的形式挂载外库的视图。

2、PostgreSQL查询外部表分区不会自动添加分区范围条件

【验证步骤】

   2.1、总览

2.2、主PG 创建分区表

-- 设定ts为分区字段
CREATE TABLE orders  (
    id int,
    ts timestamp
) PARTITION BY RANGE(ts);

2.3、主PG 创建本地子表

-- 分区区域 1000-01-01 ~ 2020-09-01
create table orders_history partition of orders for values from ('1000-01-01') TO ('2020-09-01');

2.4、主PG 创建外部表，并挂载到分区表

-- 创建外部表
CREATE FOREIGN TABLE orders_current
PARTITION OF orders FOR VALUES FROM  ('2020-09-01') TO ('9999-12-31')
SERVER postgre_server
OPTIONS (schema_name 'public', table_name 'orders'); 

     PS：  外部表的相关操作可以参考我的另一篇博文 PostgreSQL安装mysql_fdw

2.5、主PG 铺设数据

INSERT INTO "public"."orders_history"("id", "ts") VALUES (1, '2020-01-05 16:19:11');
INSERT INTO "public"."orders_history"("id", "ts") VALUES (2, '2020-08-20 16:19:31');

2.6、另一个PostgreSQL库创建表并铺设数据

-- 创建表
CREATE TABLE orders(
	id int,
	ts timestamp
) ;

-- 铺设数据，包含不符合分区的数据，主分区表希望范围是 ('2020-09-01') ~ ('9999-12-31');
INSERT INTO "public"."orders"("id", "ts") VALUES (22, '2020-08-15 16:19:32');
INSERT INTO "public"."orders"("id", "ts") VALUES (11, '2020-11-05 06:19:11');

2.7、测试

   2.7.1 查看主PG库orders详情：

postgres-# \d+ orders
                                            Table "public.orders"
 Column |            Type             | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer                     |           |          |         | plain   |              |
 ts     | timestamp without time zone |           |          |         | plain   |              |
Partition key: RANGE (ts)
Partitions: orders_current FOR VALUES FROM ('2020-09-01 00:00:00') TO ('9999-12-31 00:00:00'),
            orders_history FOR VALUES FROM ('1000-01-01 00:00:00') TO ('2020-09-01 00:00:00')

2.7.2 验证

select * from orders where ts >= '2020-08-01';


-- 查询结果
id   ts
------------------------
2	 2020-08-20 16:19:31
22	 2020-08-15 16:19:32
11	 2020-11-05 06:19:11

  总结：以上SQL搜索出了外部表的记录（id = 22） ，表明外部表分区查询时未自动添加分区区域条件

备注

      若有帮助，欢迎点赞，转载请注明出处！！！