Unsupported type: nil when querying custom type in PostgreSQL

[ivoras]

I'm using timex.Date (https://github.com/invzhi/timex) to work with DATE fields in PostgreSQL, and I'm receiving en error when I try to load a null Date field (with the intent of it being loaded as the zero value in Go).

script:

package main

import (
    "context"
    "database/sql"
    "encoding/json"
    "errors"
    "fmt"
    "math/rand"

    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"

    "github.com/invzhi/timex"
)

type S struct {
    bun.BaseModel `bun:"table:test,alias:t"`
    N             int        `json:"n" bun:"n,pk"`
    D             timex.Date `json:"d" bun:"d,nullzero,type:date"`
}

func main() {
    s := S{}
    var err error
    s.D, err = timex.ParseDate("YYYY-MM-DD", "2023-12-27")
    if err != nil {
        panic(err)
    }
    num := rand.Intn(1000000000)
    s.N = num
    fmt.Println(s)

    b, err := json.Marshal(s)
    if err != nil {
        panic(err)
    }
    fmt.Println(string(b))
    ctx := context.Background()

    dsn := "postgres://username:password@localhost:15432/test?sslmode=disable"
    sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
    db := bun.NewDB(sqldb, pgdialect.New())

    _, err = db.Exec("DROP TABLE test")
    if err != nil {
        panic(err)
    }

    _, err = db.NewCreateTable().Model((*S)(nil)).Exec(ctx)
    if err != nil {
        panic(err)
    }

    // Insert a value with non-null date
    _, err = db.NewInsert().Model(&s).Exec(ctx)
    if err != nil {
        panic(err)
    }

    // Query it
    s = S{}
    err = db.NewSelect().Model(&s).Where("n = ?", num).Scan(ctx)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            fmt.Println("No rows")
        } else {
            panic(err)
        }
    }

    b, err = json.Marshal(s)
    if err != nil {
        panic(err)
    }
    fmt.Println(string(b))

    // Insert a value with null date
    _, err = db.Exec("INSERT INTO test(n) VALUES (42)")
    if err != nil {
        panic(err)
    }
    // Query it
    s = S{}
    err = db.NewSelect().Model(&s).Where("n = ?", 42).Scan(ctx)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            fmt.Println("No rows")
        } else {
            panic(err)
        }
    }

    b, err = json.Marshal(s)
    if err != nil {
        panic(err)
    }
    fmt.Println(string(b))
}

The error produced is:

panic: sql: Scan error on column index 1, name "d": unsupported type <nil>

I'm not sure if it's a problem in Bun or in the timex.Date implementation, but it looks like a bug, especially since I have the nullzero annotation on the Date field.

[bevzzz]

Hi @ivoras !

nullzero in the bun tag only controls how the value is appended to the query, so this should work:

withNil := S{D: nil}
db.NewInsert().Model(&withNil).Exec(ctx)

Scanning, on the other hand, is controlled by how (and if) the given type implements sql.Scanner interface. If we check the timex.Date implementation:

func (d *Date) Scan(value interface{}) (err error) {
    switch v := value.(type) {
    case []byte:
        *d, err = ParseDate(RFC3339, string(v))
    case string:
        *d, err = ParseDate(RFC3339, v)
    case time.Time:
        *d = DateFromTime(v)
    default:
        err = fmt.Errorf("unsupported type %T", value)
    }
    return err
}

the last part of the wrapped error message seems to come directly from here -- timex.Date does not support scanning NULL values.

unsupported type nil

What do you do about it?

You can wrap timex.Date and add the missing functionality:

package ivorastime

type Date struct {
  timex.Date
}

func (d *Date) Scan(value interface{}) (err error) {
  if value != nil {
      return d.Date.Scan(value)
  }
  // Optionally, since ivorastime.Date will initialize with zero timex.Date anyways:
  // d.Date = timex.Date{}
  return nil
}

bun.NullTime does the same for the standard time.Time and you can use it as a reference:

https://github.com/uptrace/bun/blob/8a4383505d7e954897b23811a412b9cdafaf41eb/schema/sqltype.go#L81-L83

---

P.S.: specifying the language for your code snippets will add syntax highlighting:

"```go"

instead of

"```"