PostgreSQL时间范围类型

今天找到了 一个postgres数据库的一个特性，完美契合业务逻辑。

原文地址：https://yq.aliyun.com/articles/308481

 

会议室预定系统的例子

1、创建btree_gist插件.

postgres=# create extension btree_gist;

CREATE EXTENSION

2、创建会议室预定表

postgres=# create table t_meeting (

roomid int, -- 会议室ID

who int, -- 谁定了这个会议室

ts tsrange, -- 时间范围

desc text, -- 会议内容描述

exclude using gist (roomid with = , ts with &&) -- 排他约束，同一个会议室，不允许有时间范围交叉的记录

);

CREATE TABLE

3、预定会议室，如果同一个会议室输入的时间不允许预定（有交叉），则自动报错。实现强约束。

postgres=# insert into t_meeting values (1, 1, $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);

INSERT 0 1

 

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);

ERROR: conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"

DETAIL: Key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")).

 

postgres=# insert into t_meeting values (2,1,$$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);

INSERT 0 1

 

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 09:00:00', '2017-01-01 10:00:00')$$);

INSERT 0 1

 

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 09:00:00', '2017-01-01 11:00:00')$$);

ERROR: conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"

DETAIL: Key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 11:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 10:00:00")).

 

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 08:00:00', '2017-01-01 09:00:00')$$);

INSERT 0 1

postgres=# select * from t_meeting order by roomid, ts;

roomid | who | ts

--------+-----+-----------------------------------------------

1 | 1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00")

1 | 1 | ["2017-01-01 08:00:00","2017-01-01 09:00:00")

1 | 1 | ["2017-01-01 09:00:00","2017-01-01 10:00:00")

2 | 1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00")

(4 rows)

4、查询某个时间段还有哪些会议室能预定

会议室ID表，假设有50个会议室。

create table t_room (roomid int primary key);

 

insert into t_room select generate_series(1,50);

假设用户要预定 某一天：7点到9点的会议室，这样操作即可：

select roomid from t_room

except

select roomid from t_meeting where ts && $$['2017-01-01 07:00:00', '2017-01-01 09:00:00')$$;

roomid

--------

(48 rows)

 

 

postgres=# explain (analyze,verbose,timing,costs,buffers) select roomid from t_room

except

select roomid from t_meeting where ts && $$['2017-01-01 07:00:00', '2017-01-01 09:00:00')$$;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------

HashSetOp Except (cost=0.00..77.28 rows=2550 width=8) (actual time=0.074..0.085 rows=48 loops=1)

Output: "*SELECT* 1".roomid, (0)

Buffers: shared hit=3

-> Append (cost=0.00..70.88 rows=2562 width=8) (actual time=0.013..0.058 rows=53 loops=1)

Buffers: shared hit=3

-> Subquery Scan on "*SELECT* 1" (cost=0.00..61.00 rows=2550 width=8) (actual time=0.012..0.029 rows=50 loops=1)

Output: "*SELECT* 1".roomid, 0

Buffers: shared hit=1

-> Seq Scan on public.t_room (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.016 rows=50 loops=1)

Output: t_room.roomid

Buffers: shared hit=1

-> Subquery Scan on "*SELECT* 2" (cost=1.44..9.88 rows=12 width=8) (actual time=0.018..0.019 rows=3 loops=1)

Output: "*SELECT* 2".roomid, 1

Buffers: shared hit=2

-> Bitmap Heap Scan on public.t_meeting (cost=1.44..9.76 rows=12 width=4) (actual time=0.018..0.018 rows=3 loops=1)

Output: t_meeting.roomid

Recheck Cond: (t_meeting.ts && '["2017-01-01 07:00:00","2017-01-01 09:00:00")'::tsrange)

Heap Blocks: exact=1

Buffers: shared hit=2

-> Bitmap Index Scan on t_meeting_roomid_ts_excl (cost=0.00..1.44 rows=12 width=0) (actual time=0.010..0.010 rows=4 loops=1)

Index Cond: (t_meeting.ts && '["2017-01-01 07:00:00","2017-01-01 09:00:00")'::tsrange)

Buffers: shared hit=1

Planning time: 0.123 ms

Execution time: 0.172 ms

(24 rows)

速度杠杠的。开发也方便了。

 

小结

使用PostgreSQL，时间范围类型、exclude约束，很好的帮助业务系统实现会议室预定的强约束。

使用except语法，很方便的找到需要预定的时间段还有那些会议室是空闲的。

开不开心，解放开发人员的大脑。