Use Range to make sure your reservations don't overlap in Postgres!

In apps involving start and end times, such as reservation or calendar apps, using range types in Postgres can bring many benefits, such as querying overlapping events or preventing overlaps under certain conditions.

Range columns in Postgres represent the start and end of quantifiable data such as numbers, dates, or timestamps. Using ranges, you are introduced to advanced query operators along with exclude constraints that ensure no ranges overlap with each other under certain conditions, ensuring that reservations are protected at the database level.

CHAPTERS:
00:00 Intro
00:26: Range column introduction
01:45 Range type operations
02:37 Adding a constraint to prevent overlaps
04:00 Expanding the constraint to prevent overlaps only under certain condition
05:57 Outro

💻 Videos to watch next:

👇 Learn more about Supabase 👇

📱 Connect with Us:

ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.

Build in a weekend, scale to millions.

#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[dshukertjr]

I think I will go back to creating Flutter content for the next video. Any other Flutter fans watching this video? What content would you like to see?

[krtirtho]

The best thing to ever happen to postgres is supabase. Without supabase a lof of developer would've missed postgres.

[jamesmoynihan]

Perfect timing. I was just about to add some starts_at and ends_at columns! This is a nice feature to learn about!

[zzej]

I’ve struggled with this for a long time. Thanks

[itscooldawgdonteventrip]

elite level of pg sql. nice. thanks learning a lot looks like not everything needs to be code. put as much as possible in the db. ( should be faster. )

[mrrolandlawrence]

what id love to see is more on the realtime. how to keep your client side JS object the same as your postgres view. including what happens if you miss a channel broadcast because of say network connection issue. from what i understand from the docs if you miss a broadcast message - too bad. there is no queuing for subscribed clients to catch up on?

[thibault]

Awesome! In the recent videos you published you talk about constraints, which I think are great to be implemented at database layer. Do you have any advise to handle properly exceptions raised by constraint on client side? How client side can recognize the violated constraint and display the correct friendly message? (with javascript sdk would be perfect 😇)

[galore]

What would the tstzrange constraint look like if you want to allow maximum overlaps at any certain time.

I had to implement this about 15 years ago in Java bizlogic. The use case was to have max 3 top ads active on a webpage at any time for the best sales. They were not assigned to a spot / table only limited by the maximum amount of overlaps at any point in time.

Would that be possible to achieve with a postgres db-constraint? - Surely the constraint introduced in the video could be used in a work around / good enough manner by having kind of 3 spots for the max 3 sales but that wouldn't produce a the same UX.

[Spacer-lj]

i'm with Swift and SwiftUI. I have created my brand new project with Supabase as my backend, i'm on week 2 and i love it. I find the Supabase SDK very lightweight compared with Firebase spyware, also very performant and my Previews work wow!... The Supabase Dashboard is brilliant and easy to understand, the only part i need to work on is to learn the basics of SQL as i have never interacted with this programming language and looks like is a must. But even without any SQL knowledge, the default templates does the job with a little of twist here and there. Is .range also used for pagination? For example in SwiftUI we have a List of items and by default it returns 1000 rows from Supabase. Can this be paginated with .range ? thank you

[alinagy]

Man, the more I get into Supabase, the more I realize Firebase is just not good enough.

[jackbrown]

When can we expect to see support and documentation for these column types in the JS API?

[BonBaisers]

It's cool, to enforce a business rule that should be on domain layer. But is that really useful to put those kind of constraints on the database ? It blurs the lines between data physical integrity and business logic.