Add support sql.Null

[kyleconroy]

A new generic Null type is landing in Go 1.22. I wanted to confirm that pgx will work with this type without any updates. Thanks!

[jackc]

I don't see why not. It should just be another sql.Scanner and driver.Valuer to pgx.

[lefinal]

I don't see why not. It should just be another sql.Scanner and driver.Valuer to pgx.

@jackc Well, I was just about to open a similar issue . The problem, why this does not work, is that these types probably implement the sql.Scanner and driver.Valuer interfaces. I'm doing the same with my own nulls-library. However, in the case of a non-null value, we need to provide manual scanning logic. pgx usually has its own advanced and awesome logic for scanning correctly into various types. But this is only possible when supplying the value directly to the Scan method. Maybe you could expose the logic so that in case of non-null values, we could manually use it. This would also apply for the Value() logic.

Example usage would then look like this:

// Nullable holds a nullable value.
type Nullable[T any] struct {
	// V is the actual value when Valid.
	V T
	// Valid describes whether the Nullable does not hold a NULL value.
	Valid bool
}

// ...

// Scan to value or not valid if nil.
func (n *Nullable[T]) Scan(src any) error {
	if src == nil {
		n.Valid = false
		return nil
	}
	n.Valid = true
	dst := &(n.V)
	return pgx.Scan(src, dst) // <-- SEE HERE
}

// Value returns the value for satisfying the driver.Valuer interface.
func (n Nullable[T]) Value() (driver.Value, error) {
	if !n.Valid {
		return nil, nil
	}
	return pgx.Value(n.V.) // <-- SEE HERE
}

Feel free to correct me, if I'm missing something :)

[jackc]

@lefinal Can you provide an example usage that wouldn't work? I'm having trouble picturing it.

[lefinal]

@lefinal Can you provide an example usage that wouldn't work? I'm having trouble picturing it.

@jackc For example *Map.planScan in pgtype.go. There is much useful stuff here. When we implement sql.Scanner for wrapper-types, we have to implement this manually. And for wrapper for generic values like the example from above, this gets even more difficult as we have to literally copy the code to get the same behavior.

[jackc]

@lefinal I'm not sure get the entire picture, but my guess is it is similar to the issues with supporting pgtype.Array[T] in database/sql (see #1458 and #1662). Ideally, there would be some approach that resolves all these issues, but I'm not sure what that would be.