Npgsql 10.0 compatibility: DATE columns throw InvalidCastException due to DateOnly default mapping change

Open
#869 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
58/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
postgresql
Domain
databases

Research direction

Start with the F# reproduction in the issue using PostgreSQL 15.12, Npgsql 10.0.1, and SQLProvider.PostgreSql 1.5.17. Trace the PostgreSQL provider path that calls NpgsqlDataReader.GetDateTime() while reading a date column. Done means the query reads the date value without InvalidCastException under Npgsql 10.0, with the affected behavior covered by an appropriate regression test.

Written by the indexing model from the issue text.

Description

postgresql

Describe the bug

SQLProvider is incompatible with Npgsql 10.0, which changed the default .NET mapping for PostgreSQL date columns from System.DateTime to System.DateOnly (and time from TimeSpan to TimeOnly).

To Reproduce

CREATE TABLE test (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    d DATE NOT NULL
);
INSERT INTO test (d) VALUES ('2024-01-28');

open FSharp.Data.Sql.PostgreSql
open FSharp.Data.Sql.Common

type Db = SqlDataProvider<
  DatabaseProviderTypes.POSTGRESQL,
  "Host=localhost;Database=test;Username=postgres;Password=test">
let ctx = Db.GetDataContext()
for row in query { for r in ctx.Public.Test do select r } do
    printfn "%A" row.D   // throws InvalidCastException with Npgsql 10

Environment (please complete the following information):

  • PostgreSQL v15.12
<PackageReference Include="Npgsql" Version="10.0.1" />
<PackageReference Include="SQLProvider.PostgreSql" Version="1.5.17" />

Additional context

This is from an analysis by Claude.

SQLProvider's PostgreSQL provider internally calls GetDateTime() on the NpgsqlDataReader when reading date columns. In Npgsql 10.0, this throws an InvalidCastException at runtime because the reader now returns DateOnly by default for date columns.

The build succeeds because SQLProvider's type mapping is resolved at design time independently of Npgsql's runtime behavior. The failure only surfaces at runtime when rows are actually read.

Workaround

Apply the Npgsql 10.0 legacy behavior opt-out via AppContext switch as documented in the Npgsql 10.0 release notes.

Additional References

Dominant language
F#
Stars
627
Forks
147
Avg merge
2h 2m
Merged PRs (30d)
1

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from fsprojects/SQLProvider

All issues in fsprojects/SQLProvider

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.