包阅导读总结
1. 关键词：
– 时间查询
– 范围列
– 数据完整性
– 重叠约束
– 预订应用
2. 总结：
本文介绍了在预订或日历等应用中处理时间范围的问题，指出传统两列存储方式的不足，阐述了 Postgres 中范围列的优势，包括简化查询、保证数据完整性和避免重叠，还说明了如何为不同场景添加约束，强调其能助力开发者创建更可靠的应用。
3. 主要内容：
– 传统处理事件时间范围的方式及缺点
– 用两列分别存储开始和结束时间
– 缺点包括查询复杂、难以保证数据完整性
– Postgres 中的范围列
– 介绍了多种范围类型
– 以 tstzrange 为例创建预订表
– 范围列的优势
– 用 && 操作符轻松查询重叠预订
– 提供更多特定范围的操作符
– 范围列的约束
– 防止预订重叠的约束
– 针对相同表的预订添加约束以避免重叠
思维导图：
文章地址：https://supabase.com/blog/range-columns
文章来源：supabase.com
作者：Supabase Blog
发布时间：2024/7/11 0:00
语言：英文
总字数：993字
预计阅读时间：4分钟
评分：83分
标签：PostgreSQL,时间范围列,基于时间的查询,数据库管理,应用程序开发
以下为原文内容
本内容来源于用户推荐转载，旨在分享知识与观点，如有侵权请联系删除 联系邮箱 media@ilingban.com
When working on applications such as a reservation app or calendar app, you need to store the start time and end time of an event. You may also need to query events occurring in a specific time frame or ensure that certain events do not overlap. If you have a table with two separate columns start_at and end_at to hold the beginning and end of an event, it might be hard to perform advanced queries or add constraints to prevent overlaps. This article will show how range-type columns could provide helpful query functionalities and advanced constraints to avoid overlapping.
Traditionally, when dealing with events or periods, developers often use two separate columns to represent the start and end of a range. For example:
_10
create table reservations (
_10
id serial primary key,
_10
start_at timestamptz,
While this approach works, it has a few drawbacks:
- Querying Complexity: Writing queries to find overlapping events or events within a specific period becomes complex and error-prone.
- Data Integrity: Ensuring that reservations do not overlap is difficult.
Range types are data types in Postgres that hold the beginning and end of a range of a base type. The range of int4 is int4range, the range of timestamptz is tstzrange, and the range of date is daterange. Each range has a start value, an end value, and either square brackets [] or parenthesis () surrounding them. A bracket means the end is inclusive, and a parenthesis means the end is exclusive. An int4range of [2,5) represents a range of integers from 2 including it to 5 excluding it, so 2, 3, and 4.
Using these range values, we can create a reservation table like the following:
_10
create table reservations (
_10
id serial primary key,
Using tstzrange instead of two timestamptz columns have a few advantages. First, it allows us to easily query reservations that overlap with a provided range using the && operator. Look at the following select query:
_10
where duration && '[2024-07-04 16:00, 2024-07-04 19:00)';
This query returns rows where the duration overlaps with [2024-07-04 16:00, 2024-07-04 19:00). For example, a row with [2024-07-04 18:00, 2024-07-04 21:00) will be returned, but a row with [2024-07-04 20:00, 2024-07-04 22:00) will not be returned. The overlaps operator can be used when finding reservations or events in a given period.
Postgres provides more range-specific operators. The official Postgres documentation provides a complete list of range operators.
When working on a reservations app, you might want to ensure there are no overlapping reservations. Range columns make it easy to add such constraints. The following SQL statement adds an exclude constraint that prevents new inserts/ updates from overlapping on any of the existing reservations.
_10
alter table reservations
_10
add constraint exclude_duration exclude
_10
using gist (duration with &&)
With the above constraint, the second insert on the following SQL statements fails because the duration overlaps with the first insert.
_10
-- Add a first reservation
_10
insert into reservations (title, duration)
_10
values ('Tyler Dinner', '[2024-07-04 18:00, 2024-07-04 21:00)');
_10
-- The following insert fails because the duration overlaps with the above
_10
insert into reservations (title, duration)
_10
values ('Thor Dinner', '[2024-07-04 20:00, 2024-07-04 22:00)');
Now, the exclusion constraint prevents any reservations from overlapping, but in the real world, a single reservations table typically holds reservations for different restaurants and tables within a restaurant, and just because a single reservation was made at a restaurant, it does not mean the entire restaurant is booked. Postgres can create such constraints where an insert or an update is disallowed only if a specific other column matches and the range overlaps.
Let’s say we had a table_id column in our reservations table. This table_id could represent a single table in various restaurants this database holds.
_10
create table reservations (
_10
id serial primary key,
With a table_id column in place, we can add a constraint to ensure that reservations on the same table do not overlap. The constraint requires the btree_gist extension.
_10
-- Enable the btree_gist index required for the constraint.
_10
create extension btree_gist
_10
-- Add a constraint to prevent overlaps with the same table_id
_10
alter table reservations
_10
add constraint exclude_duration
_10
exclude using gist (table_id WITH =, duration WITH &&);
With this simple constraint, no two reservations will overlap with each other with the same table_id. If we run the following inserts, the second insert will fail because it is trying to book the same table as the first insert while the duration overlaps.
_11
-- Add a first reservation
_11
insert into reservations (title, table_id, duration)
_11
values ('Tyler Dinner', 1, '[2024-07-04 18:00, 2024-07-04 21:00)');
_11
-- Insert fails, because table 1 is taken from 18:00 - 21:00
_11
insert into reservations (title, table_id, duration)
_11
values ('Thor Dinner', 1, '[2024-07-04 20:00, 2024-07-04 22:00)');
_11
-- Insert succeeds because table 2 is not taken by anyone
_11
insert into reservations (title, table_id, duration)
_11
values ('Thor Dinner', 2, '[2024-07-04 20:00, 2024-07-04 22:00)');
And that is how to create an air-tight table that holds reservations.
Postgres’s range columns offer a solution for handling range data in applications like reservation systems. They simplify queries with specific operators such as && and improve data integrity by enabling constraints to prevent overlaps. Range columns provide an alternative to traditional two-column approaches for representing periods. By leveraging these features, developers can create more sophisticated and reliable applications with less code.
