PostgreSQL Ranges

[00:00 - 00:11] One of the more advanced and powerful features of Postgres is ranges. You can make ranges out of numerical types and out of timestamps and other types that support the comparison operations.

[00:12 - 00:27] And it's very powerful because when you have a range of something, Postgres allows you to do a rhythmitix on it. You can compare ranges to see if they overlap, and you can enforce constraints on them to make sure that no two ranges overlap and when you are inserting into a table.

[00:28 - 00:34] So using a range of timestamps is perfect for our use case. Let's try to play around with it a little bit.

[00:35 - 00:52] I have a VS Code extension here for connecting to Postgres databases, and you can see I've added my local host Postgres connection here. So here we have the easy booking database, and the public schema has the two next migration tables and our users table.

[00:53 - 01:04] We'll query the new query here. And let's try and create a table called appointment, which will be using a range.

[01:05 - 01:20] So this has an ID just to have an ID, and then we have the time slot, which is a TS range, which is short for timestamp range. So when we create this, let's just make sure that it gets into here correctly.

[01:21 - 01:26] There we go. We have our table. We can now insert into this using a specific syntax.

[01:27 - 01:43] So let me show you this. These items that we're inserting here are basically two polls, but there's one thing to note about the syntax, which is that we're using a square bracket on the left and a regular parenthesis on the right.

[01:44 - 01:52] And you can exchange these for either one when you want to. The square bracket means that the left is exclusive.

[01:53 - 02:02] So that means this timestamp here is not included in the range. The range starts immediately after this timestamp, but the right part of the timestamp is included in the range.

[02:03 - 02:19] So it goes from right after this until this. And that might seem like a trivial little thing, but it's pretty important if you are creating ranges that go up to an almost to where the -- so that one starts with the other one ends, so to speak.

[02:20 - 02:26] Then you can run into a silly errors if you don't have this directly set up. Let's try and run this query and see what we get.

[02:27 - 02:33] Three rows are inserted. So we now have three rows with these ranges.

[02:34 - 02:51] So each of them is January 1, 2023, going from 9 a.m. to 10 a.m., to 11 a.m., and 11 a.m. to 12. So if we try and select in this table, let's just close that.

[02:52 - 03:05] Anything that goes -- anything that overlaps with -- so the double ampersand operator means overlaps. And we want to select anything that overlaps with the time range from 10, 30 to 11, 30.

[03:06 - 03:10] So let's try and see what we get if we do this. We get these two.

[03:11 - 03:22] So the 10 to 11 and the 11 to 12 timestamps, time ranges both overlap with this time range that we've selected here. Right.

[03:23 - 03:35] So now we want to enforce that no two rows in this table can have overlapping -- overlapping time ranges. We do this by creating a constraint, and specifically we'll be creating something called an exclusion constraint.

[03:36 - 03:53] The syntax for this is that we alter our table to add a constraint, and we will call our constraint no overlapping appointments, and it's an exclusion constraint. So when we specify one of these, we need to tell it what type of index method it will use.

[03:54 - 04:11] And we're going to be using something called GIST, which is something that comes from this extension that we're fetching here, B-tree_GIST. So basically it combines B-tree and general search tree index methods.

[04:12 - 04:25] And if you want to read up on what that means specifically, be my guest. I wouldn't even be able to explain to you what it means, but it is as far as I understand one of the most efficient ways of making indexes for timestamp ranges.

[04:26 - 04:35] So with this in place, let's just first try and run it. We've added the extension, and now we should have created the constraint.

[04:36 - 04:51] So if we try to insert now into our appointment time range that overlaps with what we created already, we should get an error. Let's try and see.

[04:52 - 05:06] Error conflicting key values violates exclusion constraint no overlapping appointments. This is nice because that means that we are now certain that our database will enforce this particular constraint.

[05:07 - 05:13] That doesn't mean that our data is fully sound. Like there can be other kinds of human mistakes, obviously.

[05:14 - 05:22] You may have made a booking that is the wrong time altogether. But at least we know that this particular constraint is being enforced by the database.

[05:23 - 05:40] So even if somebody has direct database access or if they're accessing the database through a different tool that our back end, they will not be able to enter garbage data at least for this particular set of problems. So in the next lesson, we'll start actually doing some real schema work.

[05:41 - 05:58] I'll just delete this table in the extension again because obviously we want to be adding those with a proper migration. So drop table appointment and...

[05:59 - 06:09] There we go. So now we have a clean database again.

[06:10 - 06:17] Let's just check this. Good.