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:

bun/schema/sqltype.go

Lines 81 to 83 in 8a43835

	type NullTime struct {
	time.Time
	}

---

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

"```go"

instead of

"```"