Can persist an entity, but get am error, when reading using sqlx

[hanskarlneuland]

Describe the bug
I am using sqlx and pgx.
I can persist an entity , but when I try to read it, I get an error
sql: Scan error on column index 2, name "db_timeofday": unsupported Scan, storing driver.Value type string into type *time.Time

To Reproduce
Execute the attached main. It will create the db table, insert an entity and receive an error when reading.

package main

import (
	"context"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
)

type Entity struct {
	Uuid      uuid.UUID `db:"db_uuid"`
	Number    int       `db:"db_number"`
	TimeOfDay time.Time `db:"db_timeofday"`
}

func main() {
	dsn := os.Getenv("DATABASE_URL")
	conn, err := sqlx.Connect("pgx", dsn)

	log.SetFlags(log.Lshortfile)
	if err != nil {
		log.Fatal(err)
	}

	dropAndCreateTable(conn)
	persistEntity(conn)
	readEntity(conn)
}

func dropAndCreateTable(db *sqlx.DB) {
	_, err := db.Exec(dropTable)
	_, err = db.Exec(createTable)
	if err != nil {
		log.Fatal(err)
	}
}
func persistEntity(db *sqlx.DB) {
	entities := []Entity{Entity{Uuid: uuid.UUID{}, Number: 4711, TimeOfDay: time.Now()}}

	query := `INSERT INTO csv_table (db_uuid, db_number, db_timeofday) VALUES (:db_uuid, :db_number, :db_timeofday)`
	named, args, err := sqlx.Named(query, entities)
	if err != nil {
		log.Fatal(err)
	}
	rebind := db.Rebind(named)
	_, err = db.Exec(rebind, args...)
	if err != nil {
		log.Fatal(err)
	}
}

func readEntity(db *sqlx.DB) {
	var loaded Entity
	err := db.Get(&loaded, `SELECT db_uuid, db_number,db_timeofday FROM csv_table WHERE db_number = $1 `, 4711)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Loaded:", loaded)
}

Please run the main`.

Expected behavior
I expect to read the persisted entity without an error.

Actual behavior
The db.Get in readEntity returns an error
main.go:72: sql: Scan error on column index 2, name "db_timeofday": unsupported Scan, storing driver.Value type string into type *time.Time

Version

• go version go1.26.0 linux/amd64
• PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
• pgx: github.com/jackc/pgx/v5 v5.8.0

Additional context
My go.mod:
module pgxexample

go 1.26
require (
github.com/google/uuid v1.6.0
github.com/jackc/pgx/v5 v5.8.0
github.com/jmoiron/sqlx v1.4.0
)
require (
github.com/jackc/pgpassfile v1.0.0 // indirect
github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
github.com/jackc/puddle/v2 v2.2.2 // indirect
golang.org/x/sync v0.17.0 // indirect
golang.org/x/text v0.29.0 // indirect
)

[jackc]

Can you duplicate this without sqlx? Perhaps the problem is there?

[hanskarlneuland]

I don't think, I can duplicate ist without sqlx.
Even with sqlx a direct insert, with args set directly (not in the entity) is working.
The problem is the following line

named, args, err := sqlx.Named(query, entities)

which scans the entities and extracts the args for the insert statement.

Anyhow the code works when I use "github.com/lib/pq", and it also works also, if I use sqlserver and "github.com/microsoft/go-mssqldb". (with dropAndCreateTable adapted :-) )

I run into this problem, when I tried to upgrade to pgx, so for me it seems to be an issue in pgx.

[hanskarlneuland]

I have done a little debugging.
The error is returned in the package sql, file convert.go function convertAssignRows,
a golang package.

func convertAssignRows(dest, src any, rows *Rows) error {

When using pgl, this function is invoked with argument interface{]|time.Time
When using pgx, this function is invoked with argument interface{}| *time.Time

As pgx returns no error, when the args are set directly, I would guess, that pgx sometimes invokes it also as the function with interface{]|time.Time, but does not in this case.

It is definitely true, that a small change in the golang libs would solve the problem, anyhow, even if pgx passed the compatibility tests, the code from the golang editors define the interface, and if pgx confirm to the interface, it will also work.