Pg: Insert with .Set()

[yiiTT]

I'm trying to insert a new row while using .Set() like in OnConflictDoUpdate and I'm not sure if this would work (I already tried).

The user struct is already filled with the plain text password, and when I create a new user I want to run crypt on the password column. In my case I'm trying to do db.Model(u).Set("password = crypt(?, gen_salt('bf'))", u.Password).OnConflict("DO NOTHING").Insert() _(This works when I do .Update())_ is it possible to do this or would I have to set each value when inserting?

type User struct {
    tableName struct{}  `sql:"users"`
    Id        uuid.UUID `sql:"user_id,type:uuid,pk,notnull"`
    Password  string    `sql:"password"`
}

[vmihailenco]

If I understand your question right:

type User struct {
    tableName struct{}  `sql:"users"`
    Id        uuid.UUID `sql:"user_id,type:uuid,pk,notnull"`
    Password  string    `sql:"-"`
    PasswordExpr  types.ValueAppender `sql:"password"`
}

user.PasswordExpr = pg.Q("crypt(?, gen_salt('bf')", user.Password).
db.Model(u).OnConflict("DO NOTHING").Insert()

[yiiTT]

Perfect, thanks!

@vmihailenco Looks like you forgot a closing ")" for crypt (for those who look at this issue)

[yiiTT]

@vmihailenco Hmm looks like this brought an error with it when selecting, but still returns the correct data, I suspect something with the types.ValueAppender.

"invalid character '$' looking for beginning of value" the hashed password in DB is (similar to) $2a$06$2aKmUoXUDrzw8s.FHrORyuavJOq4sTQqD.FwMhgW74EcjX7qfRddi

.Model(&user).Where("username = ?", Username).Where("password = crypt(?, password)", Password).Limit(1).Select()

[vmihailenco]

For now the easiest solution is to to have 2 structs:

type User struct {
    tableName struct{}  `sql:"users"`
    Id        uuid.UUID `sql:"user_id,type:uuid,pk,notnull"`
    Password  string 
}

type UserForInsert struct {
    tableName struct{}  `sql:"users"`
    Id        uuid.UUID `sql:"user_id,type:uuid,pk,notnull"`
    PasswordExpr  types.ValueAppender `sql:"password"`
}

It is not ideal (you can improve it by creating 3rd base model), but it works and so far I've needed it only once. I will try to come with something better, but I don't see obviously good solution to this problem now. Suggestions are welcome.

[yiiTT]

What do you thing about something like sql:"ignore_va:[select|insert|update]" or/and sql:"on_va:[select|insert|update]".

The main thing that should be supported would be the ability to support more than one [select|insert|update] so if you want to ignore select and insert there should be a separation character like | for example. IE: "ignore_va:select|insert"

type User struct {
    tableName struct{}  `sql:"users"`
    Id        uuid.UUID `sql:"user_id,type:uuid,pk,notnull"`
    Password  string    `sql:"-"`    // doesn't have to be ignored
    PasswordExpr  types.ValueAppender `sql:"password,ignore_va:select"`
}

[vmihailenco]

Try v6.9.3 and

db.Model(&user).Value("password", "crypt(?password, 'bf')").Insert()

insert there should be a separation character like | for example. IE: "ignore_va:select|insert"

That is a possibility, but it is cumbersome so let's postpone such measures until later...

[yiiTT]

Looks like it works great thanks!