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.

Any thoughts on this, seems like an expected behaviour, but not what I imagined would happen

Cross-posted from jackc/pgx#1727, any ideas how to handle this? How do I handle an update statement that doesnt always take all of the arguments that won't override the value with "" for the things not specified. Or I am I being mistaken and the issue lies somewhere else in my code?

[andrewmbenton]

How do I handle an update statement that doesnt always take all of the arguments that won't override the value with "" for the things not specified.

I think given your query there's no way to achieve what you want. Your query is explicitly saying that you want build_bucket to take on a new value. The only way to not overwrite the value would be to not call the query.

That said, I think there's probably a way you can alter the query to get what you want using a conditional expression: https://www.postgresql.org/docs/current/functions-conditional.html

In the end it may just be faster to handle the conditional logic in your own code before deciding to call the query though.

[Nikola-Milovic]

Yeah, I was misunderstanding the types completely. I thought that Valid == false means that it won't be included in the query, eg it will leave the field alone, but I was mistaken