Skip to content

PostgresException: 22007: invalid input syntax for type interval: " days" #2889

Closed

Description

@pil0t

In continue to #2888

Example code:

using Microsoft.EntityFrameworkCore;
using Npgsql;
using Xunit;

namespace DateOnlyNpgsql;

public class Linked
{
    public int Id { get; set; }
    public int Diff { get; set; }
}
public class TestEntity
{
    public int Id { get; set; }
    public DateOnly? Date { get; set; }
    public int Diff { get; set; }
    public Linked Linked { get; set; }
    public int? LinkedId { get; set; }
}


public class MyTest
{
    [Fact]
    public async Task PostgresExceptionExample()
    {
        var x = new DbCtx();
        await x.Database.EnsureDeletedAsync();
        await x.Database.EnsureCreatedAsync();
        var e = new TestEntity()
        {
            Id = 1,
            Date = DateOnly.Parse("2023-09-26")
        };
        x.TestEntities.Add(e);
        await x.SaveChangesAsync();

        var r1 = x.TestEntities
            .Select(x => new
            {
                x.Id,
                NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
            });
// PostgresException: 22007: invalid input syntax for type interval: " days"
        var a1 = r1.ToArray();
    }

}

public class DbCtx : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; } = null!;
    public DbSet<Linked> LinkedEntities { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.EnableSensitiveDataLogging();
        options.UseNpgsql(
            new NpgsqlConnection("Host=localhost;Uid=postgres;Pwd=mysecretpassword;Database=DummyDatabase"));
    }
}

From logs I can extract this:

SELECT t."Id", t."Date" + CAST((COALESCE(l."Diff"::text, '') || ' days') AS interval) AS "NextMonth"
FROM "TestEntities" AS t
LEFT JOIN "LinkedEntities" AS l ON t."LinkedId" = l."Id"

Looks like COALESCE(l."Diff"::text, '') should be replaced with COALESCE(l."Diff"::text, '0')

Activity

  1. dedalexij commented on Aug 5, 2024

    @dedalexij

    I have the same problem with the AddYears method

  2. roji commented on Aug 5, 2024

    @roji
    Member

    Sorry for not responding to this earlier.

    I am unable to reproduce this with the latest versions (EF Core 8.0.7, Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4). Using the following attempted repro:

    await using var context = new BlogContext();
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();
    
    var e = new TestEntity
    {
        Id = 1,
        Date = DateOnly.Parse("2023-09-26"),
        Linked = new Linked
        {
            Diff = 8
        }
    };
    context.TestEntities.Add(e);
    await context.SaveChangesAsync();
    
    _ = context.TestEntities
        .Select(x => new
        {
            x.Id,
            NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
        })
        .ToArray();
    
    public class BlogContext : DbContext
    {
        public DbSet<TestEntity> TestEntities { get; set; } = null!;
        public DbSet<Linked> LinkedEntities { get; set; } = null!;
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseNpgsql("Host=localhost;Username=test;Password=test")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
    }
    
    public class Linked
    {
        public int Id { get; set; }
        public int Diff { get; set; }
    }
    public class TestEntity
    {
        public int Id { get; set; }
        public DateOnly? Date { get; set; }
        public int Diff { get; set; }
        public Linked Linked { get; set; }
        public int? LinkedId { get; set; }
    }

    I can see the following SQL:

    SELECT t."Id", t."Date" + l."Diff" AS "NextMonth"
    FROM "TestEntities" AS t
    LEFT JOIN "LinkedEntities" AS l ON t."LinkedId" = l."Id"

    The COALESCE was probably removed at some point, making this work. @dedalexij or @pil0t, if you can submit an updated repro that shows this actually failing on the latest versions, I'd be happy to take another look.

  3. roji commented on Oct 28, 2024

    @roji
    Member

    Closing as the problem isn't reproducible at this point.

  4. jamend commented on Jun 3, 2025

    @jamend

    I ran into this issue in 9.0. To reproduce it, make Linked.Diff nullable and don't assign it a value. Workaround: NextMonth = x.Linked.Diff == null ? null : x.Date.Value.AddDays(x.Linked.Diff)

  5. roji commented on Jun 5, 2025

    @roji
    Member

    @jamend can you please post a console program showing this? As always, posting a partial snippet isn't sufficient - you're basically asking me to guess the context around it.

  6. jamend commented on Jun 5, 2025

    @jamend

    @roji In reproducing the issue I realized my use case is a bit different from the original (DateTime instead of DateOnly), so perhaps it has since been fixed for DateOnly, but I do have the issue with DateTime.

    Note the main difference between the original repro and the one you posted is there is no related Linked entity, so x.Linked.Diff projects to null.

    await using var context = new BlogContext();
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();
    
    var e = new TestEntity()
    {
        Id = 1,
        Date = DateTime.UtcNow
    };
    context.TestEntities.Add(e);
    await context.SaveChangesAsync();
    
    var r1 = context.TestEntities
        .Select(x => new
        {
            x.Id,
            NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
        });
    // PostgresException: 22007: invalid input syntax for type interval: " days"
    var a1 = r1.ToArray();
    
    public class BlogContext : DbContext
    {
        public DbSet<TestEntity> TestEntities { get; set; } = null!;
        public DbSet<Linked> LinkedEntities { get; set; } = null!;
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseNpgsql("Host=localhost;Username=test;Password=test")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
    }
    
    public class Linked
    {
        public int Id { get; set; }
        public int? Diff { get; set; }
    }
    public class TestEntity
    {
        public int Id { get; set; }
        public DateTime? Date { get; set; }
        public int Diff { get; set; }
        public Linked Linked { get; set; }
        public int? LinkedId { get; set; }
    }
    
  7. roji commented on Jun 5, 2025

    @roji
    Member

    Thanks, I can repro this (see below for a simplified repro using a single table). The issue occurs when a non-constant interval expression is generated via concatenation:

    SELECT t."Id", t."Date" + CAST(COALESCE(t."Diff"::text, '') || ' days' AS interval) AS "NextMonth"
    FROM "TestEntities" AS t

    ... and the number being concatenated is NULL.

    Will reopen and use this issue to track the problem, as the original one is likely the same and did not provide a repro.

    Minimal repro
    await using var context = new BlogContext();
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();
    
    var e = new TestEntity()
    {
        Id = 1,
        Date = DateTime.UtcNow,
        Diff = null // Causes the issue
    };
    context.TestEntities.Add(e);
    await context.SaveChangesAsync();
    
    var r1 = context.TestEntities
        .Select(x => new
        {
            x.Id,
            NextMonth = x.Date.AddDays((double)x.Diff)
        })
        .ToArray(); // PostgresException: 22007: invalid input syntax for type interval: " days"
    
    public class BlogContext : DbContext
    {
        public DbSet<TestEntity> TestEntities { get; set; }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseNpgsql("Host=localhost;Username=test;Password=test")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
    }
    
    public class TestEntity
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
        public double? Diff { get; set; }
    }
  8. reopened this on Jun 5, 2025
  9. self-assigned this
    on Jun 5, 2025
  10. added theissue type on Jun 5, 2025
  11. jamend commented on Jun 5, 2025

    @jamend

    I was also able to reproduce it with DateOnly with a test case in GearsOfWarQueryNpgsqlTest:

    [ConditionalTheory]
    [MemberData(nameof(IsAsyncData))]
    public virtual async Task Select_DateOnly_NullProjection_AddYears(bool async)
    {
        await AssertQuery(
            async,
            ss => ss.Set<Mission>()
                // We filter out DateOnly.MinValue which maps to -infinity
                .Where(m => m.Date != DateOnly.MinValue)
                // AddYears(null)
                .Select(m => m.Date.AddYears(ss.Set<Mission>().FirstOrDefault(m2 => m2.Id == -1).Id)));
    
        AssertSql(
            """
    @__MinValue_0='01/01/0001' (DbType = Date)
    
    SELECT CAST(m."Date" + INTERVAL '3 years' AS date)
    FROM "Missions" AS m
    WHERE m."Date" <> @__MinValue_0
    """);
    }
    
  12. roji commented on Jun 8, 2025

    @roji
    Member
  13. roji commented on Oct 18, 2026

    @roji
    Member

    AI Triage

    The below is an AI-generated analysis and may contain inaccuracies.

    Confirmed: this is still a PostgreSQL-provider bug. A single row with a null numeric column is sufficient; no relationship or optional navigation is needed. The issue is already correctly classified as Bug.

    Reproduction and version history

    The DateTime.AddDays repro below fails with PostgreSQL error 22007: invalid input syntax for type interval: " days" on every tested version:

    Npgsql.EntityFrameworkCore.PostgreSQL Resolved EF Core Result
    6.0.22 6.0.22 Reproduces
    7.0.11 7.0.11 Reproduces
    8.0.4 8.0.4 Reproduces
    9.0.0 9.0.0 Reproduces
    9.0.4 9.0.1 Reproduces
    10.0.3 10.0.4 Reproduces
    11.0.0-rc.1.1 11.0.0-rc.1.26425.128 Reproduces

    These runs used PostgreSQL 18.4. This is not a regression introduced in 9.0: it also fails on 6.0.22. No known-good version was established.

    The equivalent query on SQLite/EF Core 9.0.0 executes successfully and returns null. SQL Server was not tested; SQLite was used as the alternative-provider check.

    minimal repro

    Reference Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 in a .NET 10 console project, or 11.0.0-rc.1.1 in a .NET 11 project. Set Test__Npgsql__DefaultConnection to a disposable database, since this program deletes and recreates it.

    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Logging;
    
    await using var context = new BlogContext();
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();
    context.TestEntities.Add(new TestEntity { Date = new DateTime(2023, 9, 26, 0, 0, 0, DateTimeKind.Utc) });
    await context.SaveChangesAsync();
    
    _ = context.TestEntities
        .Select(x => new { x.Id, NextMonth = (DateTime?)x.Date.AddDays(x.Diff.Value) })
        .ToArray();
    
    public class BlogContext : DbContext
    {
        public DbSet<TestEntity> TestEntities { get; set; }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"))
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging();
    }
    
    public class TestEntity
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
        public double? Diff { get; set; }
    }

    The nullable result projection avoids conflating this server-side error with materializing a SQL null into a non-nullable DateTime.

    Cause and scope

    The generated SQL is:

    SELECT t."Id", t."Date" + CAST(COALESCE(t."Diff"::text, '') || ' days' AS interval) AS "NextMonth"
    FROM "TestEntities" AS t

    The provider's CreateIntervalExpression builds non-constant intervals using string concatenation. EF's nullability processing adds COALESCE(..., '') to preserve C# string-concatenation semantics. With a null number, this produces ' days', which PostgreSQL cannot parse as an interval. Without that coalesce, PostgreSQL propagates null instead.

    On provider 10.0.3, I also verified that:

    • DateOnly.AddDays with a null numeric column succeeds and returns null: it translates to date + integer, without interval construction.
    • DateOnly.AddYears with a null numeric column still fails with 22007, this time for " years".

    This explains why the earlier DateOnly.AddDays test did not demonstrate the remaining interval-construction problem.

    Workaround

    An explicit null guard works on provider 10.0.3 and returns null:

    context.TestEntities
        .Select(x => x.Diff == null ? (DateTime?)null : x.Date.AddDays(x.Diff.Value))
        .ToArray();

    Replacing the empty string with '0' would avoid the parse error but change null propagation into adding zero days, so it is not an equivalent fix.

    Related upstream issues

    • dotnet/efcore#36210 is the directly related upstream investigation already linked here. It was closed as expected behavior, not fixed: C# string concatenation treats null as an empty string. This issue therefore still needs a provider-side solution rather than waiting for that upstream issue.
    • dotnet/efcore#3836 is the historical issue behind that string-concatenation null compensation, not a separate unresolved instance of this PostgreSQL interval bug.

    No additional likely upstream duplicate was found in the targeted search. Keep this issue open as the provider-side tracking bug.

  14. added this to the 10.0.4 milestone on Oct 18, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions