Insert boolean type field with false value.

[rive-n]

I have struct:

type ms_backend_customuser struct {
	tableName struct{} 		`pg:"ms_backend_customuser"`
	Password	string
	LastLog		time.Time	`pg:"last_login"`
	CreatedAt	time.Time	`pg:"created_at"`
	ModifiedAt	time.Time	`pg:"modified_at"`
	Email		string		`pg:"email"`
	FirstName	string		`pg:"first_name"`
	LastName	string		`pg:"last_name"`
	SuRights	bool		`pg:"su_rights" sql:",notnull,default:false"`
	Admin		bool		`pg:"admin_rights" sql:",notnull,default:false"`
	Active		bool		`pg:"active" sql:",notnull,default:true"`
}

Here i want to insert default values, like:
CreatedAt, ModifiedAt and etc.

pg default and sql default are not working for me. So that's how i am doing default in code:

func (user *ms_backend_customuser) BeforeInsert (ctx context.Context) (context.Context, error) {
	log.Println("Inserting current time.")
	user.CreatedAt = time.Now().UTC()
	user.ModifiedAt = time.Now().UTC()
	user.LastLog = time.Now().UTC()
	return ctx, nil
}

Now - new problem. There is no information about insertion of boolean false.
I tryed a lot, but ORM always inserts "DEFAULT", not "false":
INSERT INTO "ms_backend_customuser" ("password", "last_login", "created_at", "modified_at", "email", "first_name", "last_name", "su_rights", "admin_rights", "active") VALUES ('pbkdf2_sha256$216000$5TS34IT+6RxtH/MP$893T++yKDNf4KEIvoEy60t7Z/vYWbWz6fOYsjjySBM8=', '2021-04-04 15:20:41.044193+00:00:00', '2021-04-04 15:20:41.044193+00:00:00', '2021-04-04 15:20:41.044193+00:00:00', 'riven4@riven.com', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) RETURNING "first_name", "last_name", "su_rights", "admin_rights", "active"

As you can see i am trying sql in annotations. I tryed pg too. Nothing works for me.
Is that a BUG?

[rive-n]

Yeah, btw:

Active bool pg:"active,default:true"` is not working too.

[elliotcourant]

Have you tried using usezero I think go-pg might treat false as a zero value.

[rive-n]

Have you tried using usezero I think go-pg might treat false as a zero value.

The problem in that go-pg inserting DEFAULT value if you want to insert false. That's really bad thing i guess... And I don't really want to make DEFAULT value on my DB.

So, yeah. use_zero make things, but i want to understand how annotations with DEFAULT works. If they works...

[vmihailenco]

@Pragma-Edragon default in pg:"default:now()" is only used when creating a table, e.g. it will create a table with column having a default value:

CREATE TABLE (
    created_at timestamptz DEFAULT now()
)

Then go-pg will use the DEFAULT placeholder when inserting data and PostgreSQL will replace DEFAULT with now().

Does that help?

[rive-n]

@Pragma-Edragon default in pg:"default:now()" is only used when creating a table, e.g. it will create a table with column having a default value:

CREATE TABLE (
    created_at timestamptz DEFAULT now()
)

Then go-pg will use the DEFAULT placeholder when inserting data and PostgreSQL will replace DEFAULT with now().

Does that help?

There's another problem. When I try to insert a negative value (false) - go-pg inserts Default directly instead of false.

How can I fix this directly at application level and not at database level?

And how do I specify default values for other fields so that I don't have to write methods to do this before and after sending the request?

[vmihailenco]

You should check https://pg.uptrace.dev/sql-null-go-zero-values/

How can I fix this directly at application level and not at database level?

go-pg does not offer any way to set the default value for you. PostgreSQL does.

[rive-n]

There's another problem. When I try to insert a negative value (false) - go-pg inserts Default directly instead of false.

How can I fix this directly at application level and not at database level?

You should check https://pg.uptrace.dev/sql-null-go-zero-values/

How can I fix this directly at application level and not at database level?

go-pg does not offer any way to set the default value for you. PostgreSQL does.

?

[hkolvenbach]

I just ran into this issue myself today and it is exactly as @rive-n described: Boolean values are ignored if a default is set on the table, even if the boolean value differs from the default.

If i have the following in my SQL table:

CREATE TABLE tablewithbool (boolvalue boolean DEFAULT true);

and create the following struct:

type tableWithDefaultBoolValue struct {
   boolvalue bool `sql:"boolvalue"`
}

newval := tableWithDefaultBoolValue { boolvalue: false }
gopg.Insert(newval)

Then go PG will always create the following insert statement:

INSERT INTO tablewithbool ('boolvalue') VALUES (DEFAULT)

Instead of

INSERT INTO tablewithbool ('boolvalue') VALUES (false)

Even though the value set (false) is clearly not the DEFAULT value (true)

[hkolvenbach]

using use_zero also does not work, because in that case it's not setting it to DEFAULT (true) when you don't specify anything - it always ends up false

[hkolvenbach]

for anyone else revisiting this problem, using sql.NullBool instead of bool seems to help