Upsert ignores columns set to false

[iktakahiro]

What version of SQLBoiler are you using (sqlboiler --version)?

v4.11.0

What is your database and version (eg. Postgresql 10)

PostgreSQL 13

If this happened at runtime what code produced the issue? (if not applicable leave blank)

When isEnabled is true, upsert will be as expected

dbTag := &db.Tag{
	ID:        "id_0001"
	Label:     "my_label",
	IsEnabled: true, // <----- HERE
        DeletedAt: nil,
}
if err := dbTag.Upsert(
	ctx,
	tx,
	true,
	[]string{db.TagColumns.ID},
	boil.Whitelist(db.TagColumns.Label, db.TagColumns.IsEnabled),
        boil.Infer(),
)

INSERT INTO "tag" ("id", "label", "is_enabled") VALUES ($1,$2,$3) ON CONFLICT ("id") DO UPDATE SET "label" = EXCLUDED."label","is_enabled" = EXCLUDED."is_enabled" RETURNING "deleted_at"

But when isEnabled is false, upsert ignores is_enabled column.

dbTag := &db.Tag{
	ID:        "id_0001"
	Label:     "my_label",
	IsEnabled: false, // <----- HERE
        DeletedAt: nil,
}
if err := dbTag.Upsert(
	ctx,
	tx,
	true,
	[]string{db.TagColumns.ID},
	boil.Whitelist(db.TagColumns.Label, db.TagColumns.IsEnabled),
        boil.Infer(),
)

INSERT INTO "tag" ("id", "label") VALUES ($1,$2) ON CONFLICT ("id") DO UPDATE SET "label" = EXCLUDED."label","is_enabled" = EXCLUDED."is_enabled" RETURNING "deleted_at"

Therefore, I cannot use Upsert to set the value to false.

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE IF NOT EXISTS tag
(
    id               VARCHAR(191) PRIMARY KEY NOT NULL,
    label            VARCHAR(32)              NOT NULL,
    is_enabled       BOOLEAN                  NOT NULL DEFAULT TRUE
);

This problem did not occur when the DEFAULT TRUE was not specified.

CREATE TABLE IF NOT EXISTS tag
(
    id               VARCHAR(191) PRIMARY KEY NOT NULL,
    label            VARCHAR(32)              NOT NULL,
    is_enabled       BOOLEAN                  NOT NULL
);

[stephenafamo]

If this was generated using the postgrest driver, your upsert method call should take one other argument. This is the method signature:

func (o *Model) Upsert(ctx context.Context, exec boil.ContextExecutor, updateOnConflict bool, conflictColumns []string, updateColumns, insertColumns boil.Columns) error

Without seeing what is being passed as the insert columns, I can't get the full picture.

[iktakahiro]

@stephenafamo thanks for your reply.

I apologize for that my code was incorrect. The value of the last argument is boil.Infer().

if err := dbTag.Upsert(
	ctx,
	tx,
	true,
	[]string{db.TagColumns.ID},
	boil.Whitelist(db.TagColumns.Label, db.TagColumns.IsEnabled),
        boil.Infer(),
)

[stephenafamo]

The generated query seems correct, what were the arguments?

[iktakahiro]

command:

sqlboiler psql

sqlboiler.toml:

pkgname = "db"
output = "db/sqlboiler"

[psql]
dbname = "hoge"
host = "db"
port = 5432
user = "hoge"
pass = "hoge"
sslmode = "disable"
schema = "public"
testdbname = "testdb"
blacklist = ["migrations", "goose_db_version"]

[auto-columns]
created = "created_at"
updated = "updated_at"

At least, there seems to be a difference between the two queries in the debug log.

-- expected
INSERT INTO "tag" ("id", "label", "is_enabled") VALUES ($1,$2,$3) ON CONFLICT ("id") DO UPDATE SET "label" = EXCLUDED."label","is_enabled" = EXCLUDED."is_enabled" RETURNING "deleted_at"

-- actual
INSERT INTO "tag" ("id", "label") VALUES ($1,$2) ON CONFLICT ("id") DO UPDATE SET "label" = EXCLUDED."label","is_enabled" = EXCLUDED."is_enabled" RETURNING "deleted_at"

Even if IsEnable is false, it seems the target columns should contain is_enabled.

I will check this behavior once again.

[stephenafamo]

Oh!!!! This is definitely an issue then. I'll look into it later.

[himapopo]

I ran into the same thing. I found out about boil.Infer behavior here: https://github.com/volatiletech/sqlboiler#diagnosing-problems

A field not being inserted (usually a default true boolean), boil.Infer looks at the zero value of your Go type (it doesn't care what the default value in the database is) to determine if it should insert your field or not. In the case of a default true boolean value, when you want to set it to false; you set that in the struct but that's the zero value for the bool field in Go so sqlboiler assumes you do not want to insert that field and you want the default value from the database. Use a whitelist/greylist to add that field to the list of fields to insert.

If there is a bool type that defaults to true, you must use a whitelist or a greylist.