PostgreSQL range types and Entity Framework Core
This blog post is part of The C# Advent Calendar, a series of 50 posts about C#. Be sure to check out the rest of the blog posts in the calendar!
Ranges in Relational Databases
When modeling scheduling applications, such as an application for managing calendar events or hotel bookings, it is needed to store the start date(time) and end date(time) of the events. It is also common to store price history or market data with an associated validity period. Finally, if you are building a system where users can define price based on the order quantity (If you buy less than five items, the unit price is $100, between 5 and 10 unit price is $90, etc), you need to store the starting and ending quantities.
The typical solution for such scenarios is to store the range in two separate columns (such as StartDate and EndDate) and use a query like this to lookup for the events that occur at a particular time: SELECT * FROM events WHERE @lookupTime BETWEEN StartDate AND EndDate. While this works in simple cases, there is no easy way to
make sure that no two events overlap, and more advanced scenarios can quickly become complex to support.
In this article, I will show how to use PostgreSQL range types to represent ranges, how it simplifies querying and can guarantee non-overlapping periods in the database, and how to use them with Entity Framework Core.
Ranges in PostgreSQL
Range types in PostgreSQL represent a range of values for their underlying type. Postgres comes with several built-in range types: int4range and int8range represent ranges of int and bigint, numrange - ranges of numerics, tstzrange, daterange, and tsrange represent ranges of timestamptz, date, and time types. You can use these types just like any other:
CREATE TABLE IF NOT EXISTS public."Meetings"
(
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
"RoomName" character varying(20) NOT NULL,
"Title" character varying(50) NOT NULL,
"Time" tstzrange NOT NULL,
CONSTRAINT "PK_Meetings" PRIMARY KEY ("Id")
)
In this example, the Time column represents the start time and end time of the corresponding meeting. Next, let’s insert a couple of records:
INSERT INTO public."Meetings"("RoomName", "Title", "Time")
VALUES ('Winterfell', 'PostgreSQL Demo', '[2023-12-23 11:30, 2023-12-23 12:30)');
INSERT INTO public."Meetings"("RoomName", "Title", "Time")
VALUES ('Jobria', 'Npgsql Demo', '[2023-12-23 13:00, 2023-12-23 13:30]');
INSERT INTO public."Meetings"("RoomName", "Title", "Time")
VALUES ('Casterly Rock', 'Upgrade PostgreSQL', '[2023-12-23 14:00, 2023-12-23 15:30]');
Range values are created by specifying the start and end bounds, and they follow mathematical notation for interval: A square bracket means inclusive bound, and parentheses means exclusive bound.
Range Operators in PostgreSQL
The real power of PostgreSQL ranges comes from range operators. The most commonly used operators are:
&&- Overlap operator, tests if two ranges overlap.@>and<@- Containment operators test if the first range contains the second one or vice versa.-|-- Checks if two ranges are adjacent.*- Computes intersection of two ranges.
Let’s see some real-life examples:
- Find all meetings that occur in the specified time range:
Select "RoomName", "Title", "Time" from "Meetings" where
"Time" <@ '[2023-12-23 13:00, 2023-12-23 15:00]'::tstzrange
| "RoomName" | "Title" | "Time" |
|------------|---------------|-----------------------------------------------------------|
| "Jobria" | "Npgsql Demo" | "[""2023-12-23 13:00:00+04"",""2023-12-23 13:30:00+04""]" |
| | | |
- Find all meetings that overlap with the specified time range:
Select "RoomName", "Title", "Time" from "Meetings" where
"Time" && '[2023-12-23 13:00, 2023-12-23 15:00]'::tstzrange
| "RoomName" | "Title" | "Time" |
|-----------------|----------------------|-----------------------------------------------------------|
| "Jobria" | "Npgsql Demo" | "[""2023-12-23 13:00:00+04"",""2023-12-23 13:30:00+04""]" |
| "Casterly Rock" | "Upgrade PostgreSQL" | "[""2023-12-23 14:00:00+04"",""2023-12-23 15:30:00+04""]" |
| | | |
- Find all busy times in the specified time range:
Select "RoomName", "Title", "Time",
"Time" *'[2023-12-23 13:00, 2023-12-23 15:00]'::tstzrange as BusyTime
from "Meetings" where "Time" && '[2023-12-23 13:00, 2023-12-23 15:00]'::tstzrange
| "RoomName" | "Title" | "Time" | "BusyTime" |
|-----------------|----------------------|-----------------------------------------------------------|-----------------------------------------------------------|
| "Jobria" | "Npgsql Demo" | "[""2023-12-23 13:00:00+04"",""2023-12-23 13:30:00+04""]" | "[""2023-12-23 13:00:00+04"",""2023-12-23 13:30:00+04""]" |
| "Casterly Rock" | "Upgrade PostgreSQL" | "[""2023-12-23 14:00:00+04"",""2023-12-23 15:30:00+04""]" | "[""2023-12-23 14:00:00+04"",""2023-12-23 15:00:00+04""]" |
Ranges Exclusion Constraints
Finally, let’s make sure that no two events are happening at the same time in the same room. First, you need to load
btree_gist extension by running the following command: CREATE EXTENSION btree_gist;. Next, create an
Exclusion Constraint that guarantees that there are no overlapping periods for any given room:
Alter Table "Meetings"
Add Constraint "NoOverlap"
EXCLUDE USING gist ("RoomName" WITH =, "Time" WITH &&)
This Exclude constraint checks that no two rows with the same RoomName have Time values that overlap. With this constraint in place, if you try to insert an overlapping record, the insert will fail:
INSERT INTO public."Meetings"("RoomName", "Title", "Time")
VALUES ('Winterfell', 'Overlapping Event', '[2023-12-23 12:00, 2023-12-23 12:30]');
ERROR: Key ("RoomName", "Time")=(Winterfell, ["2023-12-23 12:00:00+04","2023-12-23 12:30:00+04"])
conflicts with existing key ("RoomName", "Time")=(Winterfell, ["2023-12-23 11:30:00+04","2023-12-23 12:30:00+04")).
conflicting key value violates exclusion constraint "NoOverlap"
ERROR: conflicting key value violates exclusion constraint "NoOverlap"
SQL state: 23P01
Detail: Key ("RoomName", "Time")=(Winterfell, ["2023-12-23 12:00:00+04","2023-12-23 12:30:00+04"])
conflicts with existing key ("RoomName", "Time")=(Winterfell, ["2023-12-23 11:30:00+04","2023-12-23 12:30:00+04")).
Now that we have covered how range types work in PostgreSQL, let’s see how to use them from Entity Framework Core.
Entity Framework Core and PostgreSQL Range Types.
Entity Framework Core provider for PostgreSQL supports
mapping ranges and performing operations on ranges in LINQ queries. To represent the PostgreSQL range in C# I will use NpgsqlRange<T> type provided by Npgsql:
class MeetingRoom
{
public int Id { get; set; }
public string Name { get; set; };
public int MaxCapacity { get; set; }
}
class Meeting
{
public int Id { get; set; }
public string Title { get; set; }
public MeetingRoom Room { get; set; }
public NpgsqlRange<DateTime> Time { get; set; }
}
The exclusion constraint can’t be specified using the Entity Framework ModelBuilder api, so I’ll use
raw SQL inside a migration to create it:
public partial class ExcludeConstraint : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"Alter Table ""Meetings""
Add Constraint ""NoOverlap""
EXCLUDE USING gist (""RoomId"" WITH =, ""Time"" WITH &&)");
}
}
Next, let’s insert some rooms and events and see how to perform queries on ranges with Entity Framework Core.
demoContext.Meetings.Add(new Meeting
{
Title = "PostgreSQL Demo",
Room = winterfellMeetingRoom,
Time = new NpgsqlRange<DateTime>(new DateTime(2023, 12, 23, 11, 30, 0, DateTimeKind.Utc),
new DateTime(2023, 12, 23, 12, 30, 0, DateTimeKind.Utc))
});
demoContext.Meetings.Add(new Meeting
{
Title = "Npgsql Demo",
Room = jobriaMeetingRoom,
Time = new NpgsqlRange<DateTime>(new DateTime(2023, 12, 23, 13, 00, 0, DateTimeKind.Utc),
new DateTime(2023, 12, 23, 13, 30, 0, DateTimeKind.Utc))
});
demoContext.Meetings.Add(new Meeting
{
Title = "Upgrade PostgreSQL",
Room = casterlyRockMeetingRoom,
Time = new NpgsqlRange<DateTime>(new DateTime(2023, 12, 23, 14, 00, 0, DateTimeKind.Utc),
new DateTime(2023, 12, 23, 15, 30, 0, DateTimeKind.Utc))
});
await demoContext.SaveChangesAsync();
As you can see inserting range values is quite straightforward with Entity Framework Core. Performing operations on ranges is simple as well:
var targetRange = new NpgsqlRange<DateTime>(new DateTime(2023, 12, 23, 13, 00, 0, DateTimeKind.Utc),
new DateTime(2023, 12, 23, 15, 00, 0, DateTimeKind.Utc));
//Find all meetings that occur in the specified time range
var meetings = await demoContext.Meetings.Where(m => m.Time.ContainedBy(targetRange)).ToListAsync();
//Find all meetings that overlap with the specified time range
meetings = await demoContext.Meetings.Where(m => m.Time.Overlaps(targetRange)).ToListAsync();
//Find all busy times in the specified time range
var meetingsWithBusyTimes = await demoContext.Meetings.Where(m => m.Time.Overlaps(targetRange))
.Select(m => new
{
Meeting = m,
BusyTime = m.Time.Intersect(targetRange)
}).ToListAsync();
Entity Framework Core and Npgsql translate these operations to corresponding PostgreSQL range operations so they get evaluated on the database server.
Finally, if you try to insert a new meeting that overlaps with an existing one, Entity Framework Core will throw DbUpdateException:
try
{
demoContext.Meetings.Add(new Meeting
{
Title = "Conflicting meeting",
Room = casterlyRockMeetingRoom,
Time = new NpgsqlRange<DateTime>(new DateTime(2023, 12, 23, 15, 00, 0, DateTimeKind.Utc),
new DateTime(2023, 12, 23, 16, 00, 0, DateTimeKind.Utc))
});
await demoContext.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
var npgsqlException = ex.GetBaseException() as NpgsqlException;
if (npgsqlException?.SqlState == PostgresErrorCodes.ExclusionViolation)
{
Console.WriteLine("This meeting overlaps with another meeting in the same room");
}
}
Conclusion
In this article I showed how PostgreSQL range types can simplify representing and performing operations on ranges. Apart from range types PostgreSQL has many other interesting features not found in other relational databases and I recommend every developer to check it out.
The sample code from this post is available on GitHub: https://github.com/Giorgi/PostgresRangeTypes
