Error reading NULL date in PostgreSQL

[ivoras]

I'm using the Bun ORM (https://bun.uptrace.dev/) so I'm not sure if it's their problem or of the timex package. I'd like to use a Date field without the time part in my database and JSON structures. The timex package is the only one so far that seems to work with my stack (Bun + PostgreSQL), but it fails on null values.

Here's a test 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>

[invzhi]

I think timex.NullDate will solve your error for NULL values.

[ivoras]

Thank you!

[ivoras]

This is an ok solution, and probably not a common problem, but timex could handle nil values, to be closer to interoperability with time.Time:

uptrace/bun#934 (comment)