Update query with type boolean

[luandnh]

Hi,
I'm having a problem when updating a row with column type of boolean, a query that casts a value to NULL when that value is false.

Active bool `json:"active" bun:"active,type:bool,default:'1',nullzero"`

UPDATE "campaign"
SET "campaign_name" = 'Autodialer 01', "active" = NULL
WHERE 
(campaign_id = 'vaseg')

Bun version 1.1.7

[vmihailenco]

,nullzero is what causes that behavior. Just remove it.

[luandnh]

It works!

But I need to remove default too. If I set default tag, active will set to NULL like nullzero.

I need to set a default value of this column, campaign should be true on creation.

[vmihailenco]

Yes, for default to work you need to have nullzero. There is no way around it.

[luandnh]

Okay, I can fix it by add new struct with same structure and remove some tags.
Thanks you!

[sykesm]

We hit this with a string value and it's a bit weird because of its inconsistency. For example, the insert is successful but the update is not.

type Object struct {
	ID           int64  `bun:",pk,autoincrement"`
	SomeValue    string `bun:",notnull,default:''"`
	AnotherValue string `bun:",notnull,default:''"`
}
// ...
db.ResetModel(context.Background(), (*Object)(nil))
obj := &Object{SomeValue: "some value"}
_, err = db.NewInsert().Model(obj).Returning("*").Exec(ctx)
if err != nil {
	t.Fatalf("failed to insert object: %v", err)
}
obj.SomeValue = "updated value"
_, err = db.NewUpdate().Model(obj).WherePK().Returning("*").Exec(ctx)
if err != nil {
	t.Fatalf("failed to update object: %v", err)
}

// [bun]  10:10:00.665   DROP TABLE           21.238ms  DROP TABLE IF EXISTS "objects" CASCADE
// [bun]  10:10:00.675   CREATE TABLE          9.931ms  CREATE TABLE "objects" ("id" BIGSERIAL NOT NULL, "some_value" VARCHAR NOT NULL DEFAULT '', "another_value" VARCHAR NOT NULL DEFAULT '', PRIMARY KEY ("id"))
// [bun]  10:10:00.677   INSERT                1.436ms  INSERT INTO "objects" ("id", "some_value", "another_value") VALUES (DEFAULT, 'some value', DEFAULT) RETURNING *
// [bun]  10:10:00.679   UPDATE                2.424ms  UPDATE "objects" AS "object" SET "some_value" = 'updated value', "another_value" = NULL WHERE ("object"."id" = 1) RETURNING *        pgdriver.Error: ERROR: null value in column "another_value" of relation "objects" violates not-null constraint (SQLSTATE=23502)
// --- FAIL: TestUpdateZero (0.04s)
//     db_test.go:116: failed to update object: ERROR: null value in column "another_value" of relation "objects" violates not-null constraint (SQLSTATE=23502)
// FAIL

I realize we can remove the annotations but the downside is that we can't use some of the helpers like NewCreateTable and ResetModel.