Column receives "" when updating with zero values using pgtype.Text

[Nikola-Milovic]

When using pgtype.Text with the UPDATE SQL statement, if no value is provided for a specific field, it is updated with an empty string "". I expected that if no value is provided and IsValid is false, the field would be ignored and not updated, rather than being overwritten with an empty string.

const updateProject = `-- name: UpdateProject :exec
UPDATE projects
SET build_bucket = $2
WHERE id = $1
`

type UpdateProjectParams struct {
    ID          pgtype.UUID
    BuildBucket pgtype.Text
}

func (q *Queries) UpdateProject(ctx context.Context, arg UpdateProjectParams) error {
    _, err := q.db.Exec(ctx, updateProject, arg.ID, arg.BuildBucket)
    return err
}

err = UpdateProject(ctx, UpdateProjectParams{
    ID: someUUID,
})

I expected that if no value is provided for BuildBucket, the corresponding build_bucket field in the database would be left untouched. But this is not the case, it goes ahead and updates the field to an empty value. I am using SQLC, maybe I'll repost it there as well.

Any thoughts on this, seems like an expected behaviour, but not what I imagined would happen. Maybe the issue lies somewhere else in my code but I am 90% sure its because of this behaviour that I described here

[jackc]

When using pgtype.Text with the UPDATE SQL statement, if no value is provided for a specific field, it is updated with an empty string "". I expected that if no value is provided and IsValid is false, the field would be ignored and not updated, rather than being overwritten with an empty string.

Valid == false means the field is the SQL NULL. It does not control whether the field is updated or not.