pgx
pgx copied to clipboard
jackc
cannot scan NULL into *string
I have table with nullable values (email and avatar_url in my case)
CREATE TABLE IF NOT EXISTS users ( id int NOT NULL PRIMARY KEY, email varchar(127), avatar_url varchar(255), created_at timestamp default now(), updated_at timestamp default now() );
I am trying to scan SELECT * from users into object like
type PostSearchResult struct { Id int json:"id" CreatedAt pgtype.Timestamp json:"created_at" UpdatedAt pgtype.Timestamp json:"updated_at" AvatarUrl string json:"avatar_url" Email string json:"email" }
When email or avatarUrl is null, then i got error
cannot scan NULL into *string
Can I avoid this somehow? Or this is known bug?
@jackc why ScanType only return reflect.TypeOf("") instead of reflect.TypeOf(pgtype.Text)?
// ColumnTypeScanType returns the value type that can be used to scan types into.
func (r *Rows) ColumnTypeScanType(index int) reflect.Type {
fd := r.rows.FieldDescriptions()[index]
switch fd.DataTypeOID {
case pgtype.Float8OID:
return reflect.TypeOf(float64(0))
case pgtype.Float4OID:
return reflect.TypeOf(float32(0))
case pgtype.Int8OID:
return reflect.TypeOf(int64(0))
case pgtype.Int4OID:
return reflect.TypeOf(int32(0))
case pgtype.Int2OID:
return reflect.TypeOf(int16(0))
case pgtype.BoolOID:
return reflect.TypeOf(false)
case pgtype.NumericOID:
return reflect.TypeOf(float64(0))
case pgtype.DateOID, pgtype.TimestampOID, pgtype.TimestamptzOID:
return reflect.TypeOf(time.Time{})
case pgtype.ByteaOID:
return reflect.TypeOf([]byte(nil))
default:
return reflect.TypeOf("")
}
}
@quanqqqqq The example in the docs seems to show that the base type is used rather than a nullable wrapper:
https://pkg.go.dev/database/sql/driver#RowsColumnTypeScanType. Notice that it says bigint should be reflect.TypeOf(int64(0)) and not reflect.TypeOf(sql.NullInt64).
Not really sure what the implications of changing that would be either so it seems safer to follow precedent.
If you do not want to use a pointer type in your struct, you can use the coalesce function in your SQL select to set a default value if the column returns NULL
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
IE:
select COALESCE(a_nullable_string_col, '') from table
@jackc While you have pointed out the correct resolution for the error, the error message being produced is moderately misleading:
cannot scan NULL into *string
In this case, the destination field on the struct has type string, but the error is describing it as *string, which makes it seem like the code would already be doing the correct thing.
As a test, I rigged up a small case to show that we're getting pointers to the destination type, rather than the type itself:
type testStruct struct {
Test *int `db:"test"`
}
func getInvalid(ctx context.Context) (testStruct, error) {
conn, err := pgx.Connect(ctx, connectionString)
if err != nil {
return testStruct{}, err
}
rows, err := conn.Query(ctx, "SELECT 'not an int' AS test")
if err != nil {
return testStruct{}, err
}
return pgx.CollectOneRow(rows, pgx.RowToStructByName[testStruct])
}
func Test_getInvalid(t *testing.T) {
_, err := getInvalid(context.Background())
assert.Equal(t,
"can't scan into dest[0]: cannot scan text (OID 25) in text format into *int",
err.Error())
})
// Expected: can't scan into dest[0]: cannot scan text (OID 25) in text format into *int
// Actual: can't scan into dest[0]: cannot scan text (OID 25) in text format into **int
I just ran into this myself with the same kind of NULL issue: can't scan into dest[0]: cannot scan NULL into *time.Time
I'm so familiar with this particular scenario that I initially missed the discrepancy, and only noticed when a less-familiar coworker was asking about how to resolve it
@benjamincudi
The error makes more sense when it comes directly from a Scan call. It's very clear that a pointer is being passed in. But the Collect* and RowToStruct* functions hide the Scan.
I can see how that error can be confusing, but Scan can only return use the type it was given to build the error.
Understanding that Scan can only know the type it was given, presumably something up the chain within the boundary of the pgx package is responsible for taking a pointer to the caller-provided field types, since it seems like setupStructScanTargets is the one taking the address: https://github.com/jackc/pgx/blob/a68e14fe5ad7caed8657816b9883ed418f3324ec/rows.go#L853
Given this, it seems like it may be possible to update the two internal call sites at least (positional and named scanners) with something along the lines of:
func (rs *positionalStructRowScanner) ScanRow(rows CollectableRow) error {
typ := reflect.TypeOf(rs.ptrToStruct).Elem()
// snipped remainder of method for brevity
err := rows.Scan(scanTargets...)
var sar ScanArgError
if errors.As(err, &sar) {
// This may not be the correct index, but my initial reading makes it seem like it is
// *baseRows.Scan can likely provide the missing index or name for the error-causing field otherwise
return fmt.Errorf("source field: %v, type %v: %w",
typ.Field(sar.ColumnIndex).Name, typ.Field(sar.ColumnIndex).Type, err)
}
return err
}
Does this sound correct?