PostgreSQL 19 Beta 3: Testing Temporal Tables with EF Core

Introduction

Temporal data is useful whenever an application needs to answer a simple but important question: "What did this data look like at a particular point in time?"

In a normal table, an UPDATE replaces the previous value. Once that happens, the application may have no easy way to determine what the previous state looked like.

Temporal tables solve this problem by keeping historical versions of rows.

PostgreSQL 19 introduces temporal constraints and temporal table functionality that makes this area particularly interesting for developers working with PostgreSQL and Entity Framework Core. PostgreSQL 19 Beta 3 also includes fixes related to the new FOR PORTION OF temporal-table syntax, making the beta a useful release to test against before relying on the feature in production.

For .NET developers, however, there is an important distinction to understand: PostgreSQL support for temporal functionality and EF Core's provider-level support are separate concerns. A database feature being available in PostgreSQL does not automatically mean EF Core can model and manage it with the same APIs developers may know from SQL Server.

That makes PostgreSQL 19 temporal tables an excellent feature to explore carefully.

What Are Temporal Tables?

A temporal table keeps information about how rows changed over time.

Imagine an employee table:

EmployeeId | Name       | Department | Salary
-----------|------------|------------|--------
101        | Rahul      | Engineering| 85000

Later, the employee moves to another department:

EmployeeId | Name       | Department | Salary
-----------|------------|------------|--------
101        | Rahul      | Management | 90000

A traditional table only shows the current state.

A temporal design can preserve the history:

EmployeeId | Department  | Salary | Valid From | Valid To
-----------|-------------|--------|------------|---------
101        | Engineering | 85000  | Jan 1      | Jun 30
101        | Management  | 90000  | Jul 1      | Open

Now the application can answer questions such as:

What department was the employee in last month?
What was the salary before the change?
Which records were valid during a particular period?
Did two records overlap in time?
What was the state of the system at a historical point?

This is especially useful for auditing, financial systems, subscriptions, contracts, inventory, pricing, and other domains where historical state matters.

PostgreSQL 19 Temporal Support

PostgreSQL 19 adds temporal constraints based around SQL-standard temporal functionality.

One important addition is support for temporal primary and unique constraints using WITHOUT OVERLAPS.

A simplified example looks like:

CREATE TABLE employee_assignments
(
    employee_id integer,
    valid_period daterange NOT NULL,

    CONSTRAINT employee_assignment_pk
        PRIMARY KEY (employee_id, valid_period WITHOUT OVERLAPS)
);

The idea is that PostgreSQL can enforce that the time periods associated with a particular key do not overlap.

For example:

Employee 101
-------------------------------
Jan 01 - Mar 31     Valid
Apr 01 - Jun 30     Valid
Jul 01 - Sep 30     Valid

But this should not be allowed:

Employee 101
-------------------------------
Jan 01 - Jun 30     Valid
Apr 01 - Aug 31     Overlap

Temporal constraints move an important part of temporal data correctness into the database.

Why Temporal Constraints Matter

Without database enforcement, an application may have to perform a check before inserting a new period.

For example:

SELECT 1
FROM employee_assignments
WHERE employee_id = 101
  AND valid_period && daterange(
      DATE '2026-04-01',
      DATE '2026-08-31',
      '[]'
  );

The application could then reject the new record if an overlap exists.

The problem is that application-level checks can introduce race conditions.

Two requests could execute the check at almost the same time:

Request A                 Request B
---------                 ---------
Check overlap             Check overlap
No overlap                No overlap
Insert                    Insert
       \                   /
        \                 /
         Invalid overlap

A database constraint is much stronger because PostgreSQL itself becomes responsible for enforcing the rule.

Understanding `WITHOUT OVERLAPS`

WITHOUT OVERLAPS is designed for temporal keys.

Consider:

CREATE TABLE product_prices
(
    product_id integer,
    valid_period daterange NOT NULL,

    PRIMARY KEY (
        product_id,
        valid_period WITHOUT OVERLAPS
    )
);

This expresses an important business rule:

A product cannot have two overlapping price periods.

For example:

INSERT INTO product_prices
(product_id, valid_period)
VALUES
(10, daterange('2026-01-01', '2026-03-31', '[]'));

A second non-overlapping period is acceptable:

INSERT INTO product_prices
(product_id, valid_period)
VALUES
(10, daterange('2026-04-01', '2026-06-30', '[]'));

But an overlapping period should violate the temporal constraint.

This is much cleaner than implementing all overlap rules manually in application code.

The `FOR PORTION OF` Syntax

Another important part of PostgreSQL's temporal work is FOR PORTION OF.

It is intended for changing only a particular portion of a row's valid time.

Conceptually, suppose a contract is valid for an entire year:

Contract
Jan ----------------------------- Dec
              Valid

The application wants to change the contract only during a smaller period:

Jan ----- Jun ---- Sep ---------- Dec
          Updated

The FOR PORTION OF syntax allows an update to target a specific temporal portion rather than treating the entire historical period as one ordinary row update.

This is one of the areas that PostgreSQL 19 Beta 3 specifically addressed with fixes around temporal-table behavior.

Because PostgreSQL 19 is a beta release, this is exactly the kind of feature that should be tested against realistic workloads rather than immediately treated as production-ready functionality.

What Does This Mean for EF Core?

This is where .NET developers need to be careful.

EF Core supports different database providers, and provider capabilities are not identical.

For example, SQL Server has long had EF Core support for SQL Server temporal tables through APIs such as:

modelBuilder.Entity<Employee>()
    .ToTable("Employees", b =>
        b.IsTemporal());

A developer familiar with this API might reasonably assume that the same model configuration will work with PostgreSQL.

It should not be assumed.

PostgreSQL temporal functionality and EF Core PostgreSQL provider support need to be evaluated separately.

The database may support a feature that the provider does not yet expose through EF Core's high-level model-building APIs.

A Practical EF Core Model

Suppose we have:

public class EmployeeAssignment
{
    public int EmployeeId { get; set; }

    public string Department { get; set; } = string.Empty;

    public DateOnly StartDate { get; set; }

    public DateOnly EndDate { get; set; }
}

A simple EF Core configuration might be:

public class EmployeeAssignmentConfiguration
    : IEntityTypeConfiguration<EmployeeAssignment>
{
    public void Configure(
        EntityTypeBuilder<EmployeeAssignment> builder)
    {
        builder.HasKey(x => new
        {
            x.EmployeeId,
            x.StartDate,
            x.EndDate
        });

        builder.Property(x => x.Department)
            .HasMaxLength(200);
    }
}

This models the application entity, but it does not automatically create PostgreSQL's temporal semantics.

That distinction is important.

Using Raw SQL for Database-Specific Features

When a PostgreSQL feature is not directly represented by the EF Core provider's model API, migrations can use database-specific SQL.

For example:

migrationBuilder.Sql("""
    ALTER TABLE employee_assignments
    ADD CONSTRAINT employee_assignment_pk
    PRIMARY KEY (
        employee_id,
        valid_period WITHOUT OVERLAPS
    );
""");

This approach can be useful when the application deliberately depends on PostgreSQL-specific capabilities.

However, it also means the database design is now partly outside EF Core's provider-independent abstraction.

That is not necessarily bad.

It simply means the team needs to document the dependency clearly.

Mapping PostgreSQL Range Types

PostgreSQL range types are particularly useful for temporal data.

For example:

daterange

can represent:

2026-01-01 → 2026-03-31

An EF Core model might represent a period using an appropriate PostgreSQL range mapping supported by the PostgreSQL EF Core provider.

Conceptually:

public class ProductPrice
{
    public int ProductId { get; set; }

    public NpgsqlRange<DateTime> ValidPeriod { get; set; }

    public decimal Price { get; set; }
}

The exact CLR type and mapping should be verified against the version of the PostgreSQL EF Core provider being used.

This is another reason beta testing matters: provider support can evolve independently from the PostgreSQL server itself.

Testing Temporal Behavior

A good temporal-table test suite should not only verify that records can be inserted.

It should test the actual business rules.

Test 1: Non-Overlapping Periods

[Fact]
public async Task Allows_NonOverlapping_Periods()
{
    var first = new ProductPrice
    {
        ProductId = 10,
        Price = 100,
        // Configure first valid period
    };

    var second = new ProductPrice
    {
        ProductId = 10,
        Price = 120,
        // Configure second valid period
    };

    db.ProductPrices.AddRange(first, second);

    await db.SaveChangesAsync();
}

Test 2: Overlapping Periods

The second important test is the opposite case.

[Fact]
public async Task Rejects_Overlapping_Periods()
{
    // Insert an existing period.

    // Attempt to insert an overlapping period.

    // Verify PostgreSQL rejects the operation.
}

The test should verify the actual database exception rather than relying on an application-side overlap check.

Test 3: Historical Updates

If your design uses temporal modification semantics, test partial-period changes carefully:

Original:
Jan ---------------- Dec

Requested change:
      Apr ---- Jun

Expected:
Jan -- Mar   Original
Apr -- Jun   Updated
Jul -- Dec   Original

This is the kind of scenario where PostgreSQL 19's temporal features become particularly interesting.

Testing PostgreSQL 19 Beta Safely

Because PostgreSQL 19 Beta 3 is a pre-release version, testing should happen in an isolated environment.

A Docker-based environment is convenient:

services:
  postgres:
    image: postgres:19
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: password
      POSTGRES_DB: temporal_demo
    ports:
      - "5432:5432"

Do not use a beta database version for an existing production workload simply because a feature looks useful.

Instead, create a separate test environment.

The goal is to answer practical questions:

Does the SQL behave as expected?
Does the provider generate compatible SQL?
Do migrations work?
Does EF Core correctly read and write the data?
What happens when a constraint is violated?
Do transactions behave correctly?
Does application-level retry logic interact correctly with constraint failures?
Does the behavior remain correct under concurrent writes?

PostgreSQL Temporal Tables vs Application-Level History

There are several ways to maintain historical data.

Approach	Historical Data	Database Enforcement	Application Complexity	PostgreSQL Specific
Current row only	No	Low	Low	No
Audit table	Yes	Medium	Medium	No
Application history logic	Yes	Low	High	No
Temporal constraints	Yes	High	Medium	Yes
Database temporal features	Yes	High	Medium	Yes

There is no universally correct choice.

A simple audit table may be enough for many applications.

Temporal functionality becomes more attractive when time validity is itself part of the business model.

For example:

Contract validity
Product pricing
Employee assignments
Subscription periods
Insurance coverage
Inventory validity
Scheduling

Advantages

Database-Level Integrity

Temporal constraints can prevent invalid overlapping periods without depending entirely on application code.

Better Historical Modeling

Time becomes a first-class part of the data model instead of being represented through ad-hoc audit records.

Stronger Concurrency Protection

Database constraints are safer than application-only checks when multiple requests can modify the same data concurrently.

Useful for Business Rules

Temporal constraints can express rules that would otherwise require complicated validation logic.

Disadvantages and Limitations

PostgreSQL 19 Is Still Beta

Beta functionality should be evaluated carefully and should not automatically be treated as production-ready.

EF Core Support May Not Match Database Support

A PostgreSQL feature can exist at the database level while provider support in EF Core is still evolving.

More Complex Data Modeling

Ranges and temporal constraints require developers to understand interval semantics, boundaries, and overlap behavior.

Provider Lock-In

Using PostgreSQL-specific temporal features can reduce database portability.

Migration Complexity

Database-specific SQL may be necessary when EF Core cannot represent a feature directly.

Common Mistakes

Assuming SQL Server Temporal APIs Work on PostgreSQL

This is probably the easiest mistake for an experienced .NET developer to make.

Do not assume:

.ToTable("Employees", b => b.IsTemporal());

automatically translates into PostgreSQL temporal behavior.

Always verify what the PostgreSQL EF Core provider actually supports.

Mixing Inclusive and Exclusive Boundaries

Temporal ranges can become confusing if one part of the application treats the end date as inclusive and another treats it as exclusive.

Define the rule clearly.

For example:

[2026-01-01, 2026-04-01)

means January 1 through March 31 when using a half-open interval.

Be consistent.

Only Testing Successful Inserts

Temporal systems are mostly about preventing invalid states.

Test overlapping periods, boundary conditions, updates, deletes, and concurrent operations.

Testing Only EF Core

Temporal behavior is enforced by PostgreSQL.

Therefore, tests should validate both:

EF Core
   +
PostgreSQL

rather than assuming an in-memory test accurately represents database behavior.

Troubleshooting

Problem	Possible Cause
Temporal SQL fails	PostgreSQL version or syntax mismatch
EF migration fails	Provider does not understand the feature
Overlap is accepted	Constraint not created correctly
Unexpected period behavior	Inclusive/exclusive boundary mismatch
EF model works but migration fails	Database-specific feature not represented by provider
Constraint exception is unexpected	Application is not handling database constraint violations
Tests pass locally but fail elsewhere	Different PostgreSQL/provider versions

When troubleshooting beta features, always record the exact PostgreSQL server version and EF Core provider version.

For example:

PostgreSQL: 19 Beta 3
EF Core: <application version>
Npgsql: <provider version>

This information makes compatibility problems much easier to reproduce.

Best Practices

Keep Temporal Rules in the Database

If overlapping periods are forbidden, enforce that rule at the database level rather than relying only on application validation.

Test With Real PostgreSQL

Avoid relying exclusively on an in-memory database for temporal behavior.

Keep Provider Versions Aligned

Test the PostgreSQL server version and Npgsql/EF Core provider combination together.

Use Integration Tests

Temporal behavior involves SQL semantics and database constraints, so integration tests provide much more confidence than isolated unit tests.

Document Database-Specific Features

If migrations contain PostgreSQL-specific SQL, explain why it exists.

Treat Beta Features as Experiments

Use PostgreSQL 19 Beta 3 to understand the feature, test applications against it, and identify provider gaps. Do not assume that a successful development test means the feature is ready for production.

Conclusion

PostgreSQL 19's temporal features are worth paying attention to because they give developers a better way to model data that changes over time. For .NET developers using EF Core, the important thing is to look at both sides of the equation: what PostgreSQL can do and what the EF Core provider can actually map and manage. These are not always the same thing. PostgreSQL 19 Beta 3 is a good opportunity to experiment with temporal constraints, range-based periods, and FOR PORTION OF, but it should be treated as a testing exercise while the release is still in beta. If your application has real business rules around contracts, pricing, subscriptions, assignments, or other time-based data, testing these capabilities now can help you decide whether PostgreSQL's temporal features are a good fit for your architecture.

Introduction

What Are Temporal Tables?

PostgreSQL 19 Temporal Support

Why Temporal Constraints Matter

Understanding WITHOUT OVERLAPS

The FOR PORTION OF Syntax

What Does This Mean for EF Core?

A Practical EF Core Model

Using Raw SQL for Database-Specific Features

Mapping PostgreSQL Range Types

Testing Temporal Behavior

Test 1: Non-Overlapping Periods

Test 2: Overlapping Periods

Test 3: Historical Updates

Testing PostgreSQL 19 Beta Safely

PostgreSQL Temporal Tables vs Application-Level History

Advantages

Database-Level Integrity

Better Historical Modeling

Stronger Concurrency Protection

Useful for Business Rules

Disadvantages and Limitations

PostgreSQL 19 Is Still Beta

EF Core Support May Not Match Database Support

More Complex Data Modeling

Provider Lock-In

Migration Complexity

Common Mistakes

Assuming SQL Server Temporal APIs Work on PostgreSQL

Mixing Inclusive and Exclusive Boundaries

Only Testing Successful Inserts

Testing Only EF Core

Troubleshooting

Best Practices

Keep Temporal Rules in the Database

Test With Real PostgreSQL

Keep Provider Versions Aligned

Use Integration Tests

Document Database-Specific Features

Treat Beta Features as Experiments

Conclusion

Understanding `WITHOUT OVERLAPS`

The `FOR PORTION OF` Syntax