本文共 7023 字，大约阅读时间需要 23 分钟。
PostgreSQL , tsrange , 范围 , exclude using , 排他约束 , btree_gist , 会议室预定 , 时间重叠 , 空间重叠
PostgreSQL 范围、数组、空间类型(range, array, geometry)，都有交叉属性，例如时间范围：7点到9点，8点到9点，这两个内容是有重叠部分的。例如数组类型：[1,2,3]和[2,4,5]是有交叉部分的。例如空间类型也有交叉的属性。
那么在设计时，实际上业务上会有这样的约束，不允许对象有相交。
例如会议室预定系统，不允许两个人预定的会议室时间交叉，否则就有可能一个会议室在某个时间段被多人共享了，业务上是不允许的。
那么如何做到这样的约束呢？
PostgreSQL 提供了exclude约束，可以实现这个需求。
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | exclude_element in an EXCLUDE constraint is: { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] 范围、数组、空间类型的相交操作符如下：
postgres=# \do && List of operators Schema | Name | Left arg type | Right arg type | Result type | Description ------------+------+---------------+----------------+-------------+---------------------------------- pg_catalog | && | anyarray | anyarray | boolean | overlaps pg_catalog | && | anyrange | anyrange | boolean | overlaps pg_catalog | && | box | box | boolean | overlaps pg_catalog | && | circle | circle | boolean | overlaps pg_catalog | && | inet | inet | boolean | overlaps (is subnet or supernet) pg_catalog | && | polygon | polygon | boolean | overlaps pg_catalog | && | tinterval | tinterval | boolean | overlaps pg_catalog | && | tsquery | tsquery | tsquery | AND-concatenate public | && | integer[] | integer[] | boolean | overlaps (9 rows)
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 -------- 14 3 4 16 42 50 19 13 40 46 18 34 39 7 35 43 23 36 29 30 28 8 24 32 10 33 9 45 22 49 48 38 37 5 12 31 11 27 20 44 41 6 21 15 47 17 26 25 (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语法，很方便的找到需要预定的时间段还有那些会议室是空闲的。
开不开心，解放开发人员的大脑。
转载地址：http://auico.baihongyu.com/